Sumif with n conditions

[Guest]

Suppose a have a definition table with the following conditions

StatusToSum - The number of conditions to sum is variable, having up to N
(< 10)
Won
Lost

In the main table a have two colunms with more than 4000 rows
Status Price
Won 456
Cancelled 798
Lost 547
Lost 847
Negotiating 748

I want to sum the Price of all rows that match any of the conditions.

I tried something like the following array formula but the ranges don`t have
the same size so i returns a error

{=SUM((Status=StatusToSum)*Price))}

The following sums everything
=SUM(IF(OR(TRANSPOSE(StatusToSum)=Status);Price))

Don't want to name the several conditions like
{=SUM(((Status=StatusToSum1)+.........+(Status=StatusToSumN))*Price))}

Any clever ideia?

 

[Sandy Mann]

With the Status in A2:A108 and the Price in B2:B108, (not necessarily with
data in all cells), and the Status to Sum in G11 try:

=SUMPRODUCT((A2:A108<>"")*(A2:A108=G11)*B2:B108)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk