Sumif with n conditions

C

Caio Milani

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?
 
S

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 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

[email protected]
[email protected] with @tiscali.co.uk
 
Top