Yes, it sums, Andy. However, since you said there is only one entry that
matches your two criteria, the formula will sum a single cell. Hence, you
should get nothing more nor nothing less than the value you need.

If you aren't correct in your assertion that there will always be only one
matching row, then...that's another story.

"Andy" wrote:

Duke,
I'm keeping you busy this morning. This formula works well for summing, but
I don't want to sum, just return the value in that cell. So the value I'm
looking for in this case is B. The value of the cell in the Total range.
Not the sum, but the actual value in the matching cell.

Sorry if the explanation is confusing.
Andy



"Duke Carey" wrote:

Try:

=SUMPRODUCT(--(PRODUCT="Sink"),--(Location="New York"),Sold)


"Andy" wrote:

Okay I'm really stumped here but I know I shouldn't be.

I'm trying to return a value from a table. The table is sortof multi-use.
I've had a similiar formula that did a SumIf from the same table, but now I
just want to return the value of a certain cell given two conditions.

How do I return the value in column Sold for the following conditions:
Product = Sink
Location = New York

A vLookup wouldn't work because of multiple conditions. There should only
be one entry with those matching criterias.

Named Ranges are the columns based on their headers.

Product Location Total Sold InStock Percentage
Toaster New York 4 2 2 50%
Oven Philly 17 0 17 100%
Toaster New York 10 2 8 80%
Micro Pittsburgh 10 5 5 50%
Micro Toledo 5 0 5 100%
Sink New York B
Toaster Philly 4 1 3 75%
Toaster Philly 87 15 72 83%
Oven Pittsburgh 9 8 1 11%
Sink Toledo 10 5 5 50%
Toaster Philly A
Toaster Toledo A

BTW This newsgroup is awesome - it's helped me so much in the past few
weeks! (And the boss thinks it's all me!!!)