Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf
or if you want to just use the numbers instead of cell ref
=SUMPRODUCT((C7:C200={1,2,3})*E7:E200) -- Don Guillett SalesAid Software "Ron Coderre" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf
=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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
SUMIF with Mutiple Ranges & Criteria | Excel Discussion (Misc queries) | |||
SUMIF Not | Excel Discussion (Misc queries) | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |