Announcement

Collapse
No announcement yet.

COUNTIF function with multiple criteria???

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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!

  • #2
    Re: COUNTIF function with multiple criteria???

    Try this -

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

    Comment


    • #3
      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, 23:29.

      Comment


      • #4
        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.

        Comment


        • #5
          Re: COUNTIF function with multiple criteria???

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

          Comment


          • #6
            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"))

            Comment


            • #7
              Re: COUNTIF function with multiple criteria???

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

              Comment


              • #8
                Re: COUNTIF function with multiple criteria???

                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:
                Code:
                {=SUM(IF('2006 data'!$B:$B="homebuilder a",IF('2006 data'!$E:$E="1",1,0),0))}
                Help?

                Comment


                • #9
                  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, 15:58.
                  In formulae, depending on your locale, you might have to replace ; with , or vice versa.

                  Comment


                  • #10
                    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?

                    Comment


                    • #11
                      Re: COUNTIF function with multiple criteria???

                      =SUMPRODUCT((MONTH(A1:A8886)=MONTH(E1)*(B1:B8886=F1)*(C1:C8886=G1)))

                      where E1 a date, e.g. the first of the month in question, F1 a builder, G1 a region
                      In formulae, depending on your locale, you might have to replace ; with , or vice versa.

                      Comment


                      • #12
                        Re: COUNTIF function with multiple criteria???

                        I tried it but it gave me a circular reference error. Here's the code I used.


                        Code:
                        =SUMPRODUCT((MONTH('2006 data'!A$2:A$2000)=MONTH(JAN)*('2006 data'!B$2:B$2000=A2)*('2006 data'!E$2:E$2000=1)))
                        "A2" is the current worksheet where as "2006 data" is the other worksheet.

                        Comment


                        • #13
                          Re: COUNTIF function with multiple criteria???

                          the first part ....=month(jan) is wrong. instead of jan you must state a date that contains the month you are looking at e.g. jan 21, 2006. or you replace month(jan) with just the number of the month e.g. 1 for jan, 2 for feb etc.
                          In formulae, depending on your locale, you might have to replace ; with , or vice versa.

                          Comment


                          • #14
                            Re: COUNTIF function with multiple criteria???

                            Nope, same thing with the following:

                            Code:
                            =SUMPRODUCT((MONTH('2006 data'!A$2:A$2000)=MONTH(1)*('2006 data'!B$2:B$2000=A2)*('2006 data'!E$2:E$2000=1)))
                            and

                            Code:
                            =SUMPRODUCT((MONTH('2006 data'!A$2:A$2000)=MONTH(Jan-06)*('2006 data'!B$2:B$2000=A2)*('2006 data'!E$2:E$2000=1)))

                            Comment


                            • #15
                              Re: COUNTIF function with multiple criteria???

                              I'm trying to forumlate a total for the amount of homes built in a particular region for the month of January.

                              Comment

                              Working...
                              X