Count If Array (Excel 2003)

Topic

New Reply

Instead of COUNT and IF, use SUMPRODUCT in a ‘normal’ (not array) formula:

=SUMPRODUCT((WEEKDAY(‘CD042 Summary’!$A$3:$A$10)=2)*(‘CD042 Summary’!$H$3:$H$10>0)*(‘CD042 Summary’!$E$3:$E$10=”Statements”))

Reply | Quote

Replies

I am trying to create a countif array and it doesn’t appear to be looking at the criteria that I have added. It is counting every row. Can someone take a look at this and tell me where I went wrong. Thanks

=COUNT(IF((WEEKDAY(‘CD042 Summary’!$A$3:$A$10)=2)*(‘CD042 Summary’!$H$3:$H$10>0)*(‘CD042 Summary’!$E$3:$E$10=”Statements”),’CD042 Summary’!$H$3:$H$10,0))

What I really need is an Average IF but it still appears to not recognize the criteria that I have entered.

Reply | Quote

I see that you changed the question after I posted a reply. If you want an average, try

=AVERAGE(IF((WEEKDAY(‘CD042 Summary’!$A$3:$A$10)=2)*(‘CD042 Summary’!$H$3:$H$10>0)*(‘CD042 Summary’!$E$3:$E$10=”Statements”),’CD042 Summary’!$H$3:$H$10))

as an array formula (confirm with Ctrl+Shift+Enter).

Reply | Quote

Thanks Hans!

Reply | Quote

The difference between your formula and mine is that I omitted the 0 in the if-false part of the IF function, so that cells that do not meet the criteria are ignored instead of being included as 0 (and hence included in the count).

Reply | Quote

Thanks Han. I made a modification to my post as you were replying. What I am trying to do is average based upon multiple criteria but it seems to be counting everything.

{=AVERAGE(IF((WEEKDAY(‘CD042 Summary’!$A$2:$A$308)=2)*(‘CD042 Summary’!$E$2:$E$308=”Statements”)*(‘CD042 Summary’!$H$2:$H$308>0),’CD042 Summary’!$H$2:$H$308,0))}

This is counting all 308 rows. It does pull by the criteria specified but then divides by all of the rows within that range. How can I set it up to divide by only the cells that have data in them?

Reply | Quote