ExcelBanter - SumIf

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - SumIf (https://www.excelbanter.com/excel-worksheet-functions/57778-sumif.html)

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.

Perhaps you want something like this?

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

--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=489110

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

Does that help?

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

"Steven" wrote:

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.

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.

"Steven" wrote:

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.

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

or if the vendor numbers are text

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

--

Regards,

Peo Sjoblom

"mike" wrote in message
...
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.

"Steven" wrote:

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.

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" wrote:

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

or if the vendor numbers are text

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

--

Regards,

Peo Sjoblom

"mike" wrote in message
...
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.

"Steven" wrote:

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.

If you could sort the vendors so they group together you could use
datasubtotal

You cannot use one formula for all vendors but you could easily extract all
vendors using datafilteradvanced 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

--

Regards,

Peo Sjoblom

"mike" wrote in message
...
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" wrote:

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

or if the vendor numbers are text

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

--

Regards,

Peo Sjoblom

"mike" wrote in message
...
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.

"Steven" wrote:

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.