COUNTIF function with multiple criteria???

[adnauseum]

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!

[Timbo]

Try this -

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

[adnauseum]

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?

[h1h]

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

ammend the ranges to fit your needs

[adnauseum]

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

[adnauseum]

Can that be extended to a third criteria? ie

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

[bryce]

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

[MAC-SOLUTIONS]

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:

{=SUM(If( '2006 data'!$B:$B="homebuilder a",IF('2006 data'!$E:$E="1",1,0),0))}

Help?

[h1h]

=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.

[MAC-SOLUTIONS]

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?