Microsoft Office Forums Frequency of Count Ifs
 User Name Remember Me? Password
 Register FAQ Search Today's Posts Mark Forums Read

 Thread Tools Display Modes
#1
01-30-2017, 07:05 AM
 keeble Windows 10 Office 2010 32bit Novice Join Date: Jan 2017 Posts: 2
Frequency of Count Ifs

I have the below formula which works correctly however I have duplicates in rows which I need it to deduct, any ideas what I can add to get it to do this without?

=SUM(COUNTIFS(\$A\$28:\$A\$224,"Accepted",\$C\$28:\$C\$224 ,"Tier 1"))

So for example I have the below, the number I need to get to is 4 accepted tier 1 companies:

Accepted Mr Smith Tier 1 Company A
Accepted Mr Reynolds Tier 1 Company A
Accepted Miss Cole Tier 1 Company B

Accepted Mr x Tier 1 Company C
Accepted Mrs Y Tier 1 Company C
Accepted Mr Z Tier 1 Company D

Note I cannot just use the frequency count as I need it to look for specific things across a range of data.

Ive been racking my brains so anyone that can help with this would appreciate it!
Thanks :0)
#2
01-30-2017, 10:34 AM
 jeffreybrown Windows Vista Office 2007 Expert Join Date: Apr 2016 Posts: 650

How about use a helper column, so in E1 copied down...

=COUNTIFS(\$A\$1:A1,A1,\$C\$1:C1,C1,\$D\$1 : D1,D1)

Now if F1 count the 1's for the unique...

=COUNTIF(E:E,1)
#3
01-31-2017, 08:43 AM
 Pecoflyer Windows 7 64bit Office 2010 64bit Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,577

Moved to non-programming forum
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
#4
02-01-2017, 09:03 AM
 keeble Windows 10 Office 2010 32bit Novice Join Date: Jan 2017 Posts: 2

This works thank you so much!
#5
02-01-2017, 10:33 AM
 jeffreybrown Windows Vista Office 2007 Expert Join Date: Apr 2016 Posts: 650

Happy to hear it helped you out and you're welcome.

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post laucn Excel 2 06-08-2015 07:50 AM lwls Excel 1 03-30-2015 05:40 AM borninscorpio Excel 4 07-31-2013 12:44 AM bryant03 Excel 1 06-27-2012 10:21 AM KIM SOLIS Excel 5 09-07-2011 09:01 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:44 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top