Sumproduct error

[dschnetzer]

In a Sumproduct formula, I am getting an error message when referring to an array involving multiple columns combined with an array involving a single column. See attached for example. The problem formula is located in cell C9. Alternatively, the layout is as follows:

A B C D
1 Program Year Program Host1 Program Host2 Program Host3
2 2008 Smith, Bill
3 2008 Doe, John Smith, Bill
4 2009 Smith, John Doe, John Smith, Bill
5 2010 Doe, Karen Doe, John

Problem formula: =Sumproduct(--(B2:D5="Smith, Bill"),--(A2:A5=2008))

Each portion of the formula works in isolation, but the formula returns a #VALUE! error when the portions are put together.

Thanks in advance for your help.

[NBVC]

Try this revision:

=SUMPRODUCT((C3:E6=B9)*(B3:B6=2008))

[ChemistB]

Replace your formula with

=SUMPRODUCT(--(C3:E6=B9)*(B3:B6=2008))

The traditional SUMPRODUCT (with commas separating arguments) requires that each argument have the same size ranges. Does that work for you?

[ChemistB]

Dang, too slow.

[dschnetzer]

Thank you all. This solved it.

David

[dschnetzer]

thank you all. This solved it.