# Thread: Count If Array (Excel 2003)

1. ## Re: Count If Array (Excel 2003)

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"))

2. ## Count If Array (Excel 2003)

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.

3. ## Re: Count If Array (Excel 2003)

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).

4. ## Re: Count If Array (Excel 2003)

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?

Thanks Hans!

6. ## Re: Count If Array (Excel 2003)

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).

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•