SumIf

[Guest]

Is there a way to put multiple criteria in a single sumif() equation.

=SUMIF(C7:C200,C2,E7:E200)

But the criteria I want to sum is really in C2, C3 and C4 and I prefer it
all in one function. ie I dont want to say: =SUMIF(C7:C200,C2,E7:E200) +
SUMIF(C7:C200,C3,E7:E200) + SUMIF(C7:C200,C4,E7:E200)


Thanks.

 

[Vito]

Perhaps you want something like this?

=SUMPRODUCT((C7:C200=C2)+(C7:C200=C3)+(C7:C200=C4),(E7:E200))

 

[Guest]

Try this:
=SUMPRODUCT(+((C7:C200=C2)+(C7:C200=C3)+(C7:C200=C4))*E7:E200)

Does that help?

***********
Regards,
Ron

 

[Don Guillett]

or if you want to just use the numbers instead of cell ref
=SUMPRODUCT((C7:C200={1,2,3})*E7:E200)

 

[Guest]

I have an excel question. I have a large file with multiple columns. I'm only
concerned with two. column a has vendor # and column b has an amount. I want
to know if there is a way for excel to look at the vendor #s in column A then
add up the values in column c for all the vendor #s that match. In other
words I want it to look at duplicate vendor #s in a and return one value that
added up all the amounts in column b.

 

[Peo Sjoblom]

=SUMIF(A:A,vendornumber,C:C)

or if the vendor numbers are text

=SUMIF(A:A,"vendornumber",C:C)

--

Regards,

Peo Sjoblom

 

[Guest]

that helps but there are thousands of orders and hundreds of vendor #s. I
would have to use that formula for each vendor # right? Is it possible to
have excel look at all the vendor numbers and return each vendor number with
the total rather than using that formula for each vendor #? I can copy the
formula down but if I do it will return the total for each vendor # that
matches.

 

[Peo Sjoblom]

If you could sort the vendors so they group together you could use
data>subtotal

You cannot use one formula for all vendors but you could easily extract all
vendors using data>filter>advanced filter (after selecting the vendors only
plus the header), then use copy to another location and unique records only,
that will give you a list of all vendors, assume the first vendor number in
that list is in H2, in preferably I2 put

=SUMIF(A:A,H2,C:C)

then copy down the formula