An alternative to COUNTIF function?

[Austinma]

Good Morning Excelees and Excelettes,


I have a column (C) that contains the outcome of a simple COUNTIF function:


=COUNTIF($A$1:$A$6000,B1)


Where A1:A6000 is my range and B1 is my criteria. The output will either be a 0 (criteria not in range) or 1 (criteria present in my range; criteria will only ever be present the once in my range). 100 different criteria B1:B100


Having completed this I next sum the total of column C and take this sum value away from a =SUBTOTAL(3,A1:A6000) value (the subtotal function gives me the number of cells in column A containing values). This gives me the total number of cells that DON’T contain any of the criteria listed in column B


Is there a better, more streamlined way of doing this? Perhaps with a VLOOKUP function?


If I’ve not explained this very well and people need more clarification then please do ask.


Thanks in advance for your help and advice,


Regards,


Mark

 

[Faseeh]

Hi Austinma,


Assuming this is your data between A1:C10...

[pre]

Col-A  Col-B  Col-C
C1	C1	97
C2		68
C3		32
C4		35
C5		68
C6		12
C7		71
C8		85
C9		79
C10		75[/pre]

....You can count for Col-A excluding Criteria (C1) with : [code]=COUNTIF(A1:A10,"<>"&B1)

where B1 contains the criteria.


Similarly you can sum the values with: =SUMIF(A1:A10,"<>"&B1,C1:C10)[/code]


Regards,

Faseeh

 

[Austinma]

Hi Faseeh,


Thanks for taking the time to help me out.


I'll give it a go and get back to you,


Best wishes,


Mark