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,
--
Rodman

--
Rodman Veney

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" wrote:

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,
--
Rodman

--
Rodman Veney

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

Thanks Again!
--
Rodman Veney


"bj" wrote:

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" wrote:

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,
--
Rodman

--
Rodman Veney