excel sumproduct formula
 

I have a spreadsheet where I collect data in three columns as follows:
A B C
Name Score Month
Blue 10 Jan
Blue 10 Jan
Red 9 Jan

I use the sumproduct formula to pull data from the data base into a report.
Into one cell I can compare data and pull information in ranges such as
the number of cells where there is a score for Blue in January. In the
above example the answer is 2. The formula is as follows:
=sumproduct(--(A1:A3="blue")--(C1:C3="Jan"))
My problem is when there is no score and the cell under column B is blank.
When this happens I do not want that counted. I need the formula to ignore
and not count that entry. See example:

A B C
Name Score Month
Blue 10 Jan
Blue Jan
Red 9 Jan

In the above example the answer would be 1 since there is no score listed
for one of Blue's entries. Is there any way to modify the formula so that
correct response of 1 is returned. I need to draw from column A since that
is the name of the person I am measuring and I need C as that determines
the range based on the month.

--
joec

excel sumproduct formula
 

Hi Joec
Try it like this =SUMPRODUCT(--(A1:A3="blue"),--(C1:C3="jan"),--(B1:B30))
Regards
John
"joec" wrote in message
...
I have a spreadsheet where I collect data in three columns as follows:
A B C
Name Score Month
Blue 10 Jan
Blue 10 Jan
Red 9 Jan

I use the sumproduct formula to pull data from the data base into a
report.
Into one cell I can compare data and pull information in ranges such as
the number of cells where there is a score for Blue in January. In the
above example the answer is 2. The formula is as follows:
=sumproduct(--(A1:A3="blue")--(C1:C3="Jan"))
My problem is when there is no score and the cell under column B is blank.
When this happens I do not want that counted. I need the formula to ignore
and not count that entry. See example:

A B C
Name Score Month
Blue 10 Jan
Blue Jan
Red 9 Jan

In the above example the answer would be 1 since there is no score listed
for one of Blue's entries. Is there any way to modify the formula so that
correct response of 1 is returned. I need to draw from column A since that
is the name of the person I am measuring and I need C as that determines
the range based on the month.

--
joec

excel sumproduct formula
 

=SUMPRODUCT(--(A1:A3="blue")--(C1:C3="Jan"),--(ISNUMBER(B1:B3)))

--
__________________________________
HTH

Bob

"joec" wrote in message
...
I have a spreadsheet where I collect data in three columns as follows:
A B C
Name Score Month
Blue 10 Jan
Blue 10 Jan
Red 9 Jan

I use the sumproduct formula to pull data from the data base into a
report.
Into one cell I can compare data and pull information in ranges such as
the number of cells where there is a score for Blue in January. In the
above example the answer is 2. The formula is as follows:
=sumproduct(--(A1:A3="blue")--(C1:C3="Jan"))
My problem is when there is no score and the cell under column B is blank.
When this happens I do not want that counted. I need the formula to ignore
and not count that entry. See example:

A B C
Name Score Month
Blue 10 Jan
Blue Jan
Red 9 Jan

In the above example the answer would be 1 since there is no score listed
for one of Blue's entries. Is there any way to modify the formula so that
correct response of 1 is returned. I need to draw from column A since that
is the name of the person I am measuring and I need C as that determines
the range based on the month.

--
joec

excel sumproduct formula
 

A wee typing slip, one missed comma
=SUMPRODUCT(--(A1:A3="blue"), --(C1:C3="Jan"), --(ISNUMBER(B1:B3)))

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Bob Phillips" wrote in message
...
=SUMPRODUCT(--(A1:A3="blue")--(C1:C3="Jan"),--(ISNUMBER(B1:B3)))

--
__________________________________
HTH

Bob

"joec" wrote in message
...
I have a spreadsheet where I collect data in three columns as follows:
A B C
Name Score Month
Blue 10 Jan
Blue 10 Jan
Red 9 Jan

I use the sumproduct formula to pull data from the data base into a
report.
Into one cell I can compare data and pull information in ranges such as
the number of cells where there is a score for Blue in January. In the
above example the answer is 2. The formula is as follows:
=sumproduct(--(A1:A3="blue")--(C1:C3="Jan"))
My problem is when there is no score and the cell under column B is
blank.
When this happens I do not want that counted. I need the formula to
ignore
and not count that entry. See example:

A B C
Name Score Month
Blue 10 Jan
Blue Jan
Red 9 Jan

In the above example the answer would be 1 since there is no score listed
for one of Blue's entries. Is there any way to modify the formula so that
correct response of 1 is returned. I need to draw from column A since
that
is the name of the person I am measuring and I need C as that determines
the range based on the month.

--
joec