MrExcel Publishing
Your One Stop for Excel Tips & Solutions

COUNTIF with multiple IF's


Posted by JAF on January 11, 2000 5:20 AM

This is another of those annoying and probably very simple things, but I just can't get my head round it at the moment.

What I need is a formula (or macro) that will count the number of records that match 2 specific criteria - where the text in column G is equal to JW AND where the value in column H is greater than zero.

I'm currently using a workaround where I have a hidden column with the formula =IF(AND(G2="JW",H2<>""),1,0) -copied down to all rows - and then calculate the total of the hidden column - it's not eaxctly "elegant" but it does work!

Any assistance greatly appreciated.

JAF


Posted by Chris on January 11, 2000 1:07 PM


An array formula will easily handle that. I think MrExcel actually has an example of that very thing on this site.

Have a look.

Chris

Posted by Ivan Moala on January 11, 2000 10:20 PM


JAF
Try this array formula
Enter using Ctrl + Shift + Enter
(Assuming your range to evaluate is Row1 - Row8)
=SUM((G1:G8="JW")*(H1:H8>0))


Ivan