Results 1 to 6 of 6
Thread: Count If Array (Excel 2003)

20080613, 18:28 #1
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
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"))

20080613, 18:32 #2
 Join Date
 Mar 2004
 Location
 Mechanicsville, Virginia, USA
 Posts
 130
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20080613, 18:39 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
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).

20080613, 18:41 #4
 Join Date
 Mar 2004
 Location
 Mechanicsville, Virginia, USA
 Posts
 130
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?

20080613, 18:43 #5
 Join Date
 Mar 2004
 Location
 Mechanicsville, Virginia, USA
 Posts
 130
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Count If Array (Excel 2003)
Thanks Hans!

20080613, 18:46 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Count If Array (Excel 2003)
The difference between your formula and mine is that I omitted the 0 in the iffalse 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).