Sumif with n conditions
 

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=Sta tusToSumN))*Price))}

Any clever ideia?

Sumif with n conditions
 

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 G1:P1 try:

=SUMPRODUCT((A2:A108<"")*(A2:A108=G1:P1)*B2:B108)

--
HTH

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


with @tiscali.co.uk


"Caio Milani" wrote in message
...
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=Sta tusToSumN))*Price))}

Any clever ideia?