MrExcel Publishing
Your One Stop for Excel Tips & Solutions

COUNTIF ARRAY FORMULA


Posted by Shazadi on March 01, 2001 12:56 PM

Hellllllppp! ...
I cannot figure out why this formula keeps giving me a value of 0:

=COUNTIF(I13:I52,(I13:I52>0)*(J13:J52>0))

I am trying to count the number of dealers that have reported numbers for both columns. I am hitting the CTRL and SHIFT ENTER for the array and it still isn't working.

Can anyone tell me where I have gone wrong?

Thank you again for your previous help, and I thought I had learned how to do these but apparently I am missing something here.


Posted by Aladin Akyurek on March 01, 2001 1:18 PM

Try to array-enter:

=SUM(IF((I13:I52)*(J13:J52)>0,1,0))

Aladin

Posted by Mark W. on March 01, 2001 2:13 PM

Aladin, you don't need the IF() function:

{=SUM((I13:I52*J13:J52>0)+0)}


...but, wouldn't it be even better to use:

{=SUM(ISNUMBER(I13:I52)*ISNUMBER(J13:J52))}

Posted by Aladin Akyurek on March 01, 2001 2:37 PM

Yep, all Boole is better...

That +0 part and that 'Simpler...' formula...
You're pinching from Mark's inner Web site again. :-)