Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: COUNTIF function with multiple criteria???

1. I agreed to these rules
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. ## 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. I agreed to these rules
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. ## Re: COUNTIF function with multiple criteria???

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

ammend the ranges to fit your needs

5. I agreed to these rules
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. I agreed to these rules
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. Established Member
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. Join Date
16th 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"))
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. ## 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.

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

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

#### 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