Results 1 to 6 of 6
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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"))

  2. #2
    2 Star Lounger
    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.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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).

  4. #4
    2 Star Lounger
    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?

  5. #5
    2 Star Lounger
    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!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •