Counting data to a certain criteria in another cell

**drgogo** · 04-22-2010, 04:20 AM

Good morning all

I have the code below

Please Login or Register  to view this content.

As most of you know, this formula counts the cells that have Non-compliant and Compliant, what im trying today is to count the cells which are displaying Non-compliant and Compliant but equal what number is in cell C20.

Thus all cells containing NC and C that are equal to C20 which contain 11

So in plain english, its only counts the rows which have equal what data is in C20

Hope this makes sense

G

**DonkeyOte** · 04-22-2010, 04:24 AM

You don't specify where the value in C20 will be located, however, in general terms you would use:

=IF(C20="","",SUMPRODUCT(('Frequent Service'!$H$7:$H$2100="Non-Compliant")+('Frequent Service'!$H$7:$H$2100="Compliant"),--(otherrange=C20)))

Note for your existing formula you could use a more efficient approach of:

=IF(C20="","",SUM(COUNTIF('Frequent Service'!$H$7:$H$2100,{"Non-Compliant","Compliant"})))

**drgogo** · 04-22-2010, 04:40 AM

Sorry, I'll try and explain a bit better

This formula needs to go into another sheet, which is a summary sheet.

So if i enter 11 into C20, ont he summary sheet then i need the formula to look at the sheet "frequent service" and count all the enteries that have NC and C that are equal to 11

I hope thats better explained

G

**DonkeyOte** · 04-22-2010, 04:48 AM

Originally Posted by drgogo

I hope thats better explained

Not really I'm afraid.

You've still not said where the value entered in C20 on the summary sheet is to be found on the frequent service sheet - this range should replace "otherrange" in the suggested formula.