Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 18

Thread: COUNTIF function with multiple criteria???

  1. #1
    Join Date
    7th June 2006
    Posts
    11

    COUNTIF function with multiple criteria???

    Hi - i'm new to this forum so please forgive me if this has been covered elsewhere, but i couldn't find it...

    I need to count rows that meet 2 criteria.
    I have seen this help page
    http://www.ozgrid.com/Excel/count-if.htm
    but that counts rows with "criteria 1" OR "criteria 2"...
    I need to count rows that fulfill "criteria 1" AND "criteria 2"
    ie - count the rows that have todays date AND a cell that says "COMPLETE"

    ideally it would be as easy as "=countif(A:F,"today()","COMPLETE") but that doesn't work... any way around this???

    Thanks!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    9th June 2004
    Location
    England
    Posts
    1,047

    Re: COUNTIF function with multiple criteria???

    Try this -

    =COUNTIF($C$1:$C$20,"=today()")+COUNTIF($E$1:$E$20,"=complete")

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    7th June 2006
    Posts
    11

    Re: COUNTIF function with multiple criteria???

    I see how that could be what i'm asking for, maybe i didn't explain properly.
    That formula adds together the nmber of cells that have todays date plus the word "complete". I need it to count the number of rows that have todays date and the word "complete" in the SAME ROW.....
    ie
    7th June - INCOMPLETE
    7th June - COMPLETE
    5th June - COMPLETE
    7th June - INCOMPLETE
    7th June - COMPLETE
    5th June - INCOMPLETE
    5th June - COMPLETE

    Number of rows containing 7th June AND Complete = 2...
    Does that stil make sense?
    Last edited by adnauseum; June 7th, 2006 at 23:29.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    25th May 2004
    Location
    central Europe
    Posts
    773

    Re: COUNTIF function with multiple criteria???

    =SUMPRODUCT((C23:C25=TODAY())*(E23:E25="complete"))

    ammend the ranges to fit your needs
    In formulae, depending on your locale, you might have to replace ; with , or vice versa.

  5. #5
    Join Date
    7th June 2006
    Posts
    11

    Re: COUNTIF function with multiple criteria???

    Amazing! You truly are a scholar and a gent! Thanks!

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    7th June 2006
    Posts
    11

    Re: COUNTIF function with multiple criteria???

    Can that be extended to a third criteria? ie

    =SUMPRODUCT((C23:C25=TODAY())*(E23:E25="complete")*(F23:F25="David"))

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    25th April 2006
    Posts
    890

    Re: COUNTIF function with multiple criteria???

    yes - you can use up to 30 arrays in a sumproduct

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    16th June 2006
    Posts
    11

    Re: COUNTIF function with multiple criteria???

    Quote Originally Posted by adnauseum
    Can that be extended to a third criteria? ie

    =SUMPRODUCT((C23:C25=TODAY())*(E23:E25="complete")*(F23:F25="David"))
    What about for an entire column??

    Please excuse me, I'm terrible at trying to explain myself.

    I have a list of about 2000 rows containing a list of builders and the regions they build on. I need a formula that will tell me how many homes did "builder a" build in region "1"?

    Two columns, one with builder name and the other with region number. Please remember, the information is on a seperate worksheet, not a seperate file.

    I have something like this:
    VB:
    {=SUM(If( '2006 data'!$B:$B="homebuilder a",IF('2006 data'!$E:$E="1",1,0),0))}
    
    
    Help?

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    25th May 2004
    Location
    central Europe
    Posts
    773

    Re: COUNTIF function with multiple criteria???

    =SUMPRODUCT((Sheet1!A1:a65536="homebuilder")*(Sheet1!B1:B65536=1))

    you cannot use A:A for full collumn. If you really need full column then A1:S65536, which however I doubt you really need. Mind you are slowing things down with full column arrays.
    You could also use named ranges to refer to your data. Or even dynamic named ramges. To start, A1:A3000 will do.
    Last edited by h1h; June 16th, 2006 at 15:58.
    In formulae, depending on your locale, you might have to replace ; with , or vice versa.

  10. #10
    Join Date
    16th June 2006
    Posts
    11

    Re: COUNTIF function with multiple criteria???

    Thanks for the information and I was able to make one of the reports work with this formula. The problem now is I'm unable to figure out how to setup the following:

    I have a list of about 800 rows. Row "A" contains the "Date", Row "B" contains the "Builder" and row "C" contains the "Region".

    Region represents a permit, where the Builder built. So, what I'm trying to figure out is how many total permits did the builder have for a specific month? My "A" column is formated as "Jan-06".

    Is it a "CountIF" or "SUMPRODUCT" formula?

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Excel COUNTIF Function for Multiple Criteria
    By Dave Hawley in forum Excel Formulas (No Questions)
    Replies: 0
    Last Post: July 9th, 2008, 19:48
  2. Excel COUNTIF Function for Multiple Criteria
    By Dave Hawley in forum Excel Formulas (No Questions)
    Replies: 0
    Last Post: July 9th, 2008, 18:17
  3. Replies: 0
    Last Post: September 28th, 2007, 18:42
  4. Countif Function On Criteria
    By Andy B in forum EXCEL HELP
    Replies: 8
    Last Post: March 16th, 2007, 08:18
  5. CountIF Multiple Criteria
    By pdsasse in forum EXCEL HELP
    Replies: 2
    Last Post: March 2nd, 2006, 06:28

Bookmarks

Posting Permissions

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