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
