Help Counting Conditional Text Fields

[Rodman]

I am trying to count the number of rows where column A is equal to “High†and
column B has the year “2007†in it. I want to store the results in column C.
(FYI, the format for both column A and B is general.)

A B C
1 High 30 Nov 2007
2 Medium 31 Jan 2006
3 Low 17 Jan 2007
4 Low 17 Jan 2007
5 Low 17 Jan 2007
6 High 28 Oct 2006
7 Medium 28 Oct 2006
8 High 15 Dec 2006
9 High 18 Nov 2006
10 High 15 Dec 2007

I used the array formula: {=SUM(A1:A10="High",COUNTIF(B1:B10,"*"&"2007"&"*"))}

The results I get from this formula, summing up all of the rows with “Highâ€
in column A and the year “2007†in column B is 5. 5 is the total of “2007â€
entries in column B.

(Note: even without the array formula, calculations between the braces {},
the results are the same)

The correct answer is 2.

Can anyone help?

Thanks in Advance,

 

[bj]

try sumproduct()
=sumproduct(--(rangeA = "High"),--(year(rangeB)=2007))
note with sumproduct you cant use the A:A as shorthand for column A
the "--(" changes the logical true/false to a 1/0
Depending on whether your date is text or a date value, you may have to
change the second section

 

[Rodman]

Thanks for the quick response and information. I will try your suggestion.

Thanks Again!