COUNTIF with muliple criteria

[jdangle]

My original worksheet only counted 1 criteria (how many issues were closed each month (column defined as Year_Month_Closed)).

my equation before was simply COUNTIF(Year_Month_Closed, "2003 Jan") which i wrote in every cell all the way up to "2005 Dec".

Instead of just having one total per month, i have 20 people (defined as NAMES) down the left hand column and want to count how many each person closed each month. I am pretty sure you cant use an AND statement within a COUNTIF. Anybody have any suggestions to help me out?

 

[acw]

Hi

Try the SUMPRODUCT

=sumproduct(--(year_month_closed = "2003 Jan"),--(names = "your name here"))


Tony

 

[Zack Barresse]

For a good write-up (whitepaper) check out xld's site: http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[jdangle]

The sumproduct didnt work very well. It says "a value used in the formula is of the wrong data type". after clicking on show calculation steps it shows that the first criteria (month closed) returns a 1 or 0 and the second criteria (names) returns a true or false. Why is this? Any other suggestions?

 

[Zack Barresse]

Your ranges must be of equal size. I'm not sure that they are here.

 

[jdangle]

That was it!!! My range for names was 2 cells longer than my range for year_month_closed.

Thanks for the help!!