| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional sum
I have 3 columns of data, the first designates the currency of sales, the
second identifies the type of value input and the third is the value. I would like add the total of only the "sales" in column (C) that have a cell 2 columns to the left (A - Currency) that contains a specific value (USD). A B C 1 Currency Type Value 2 USD Sales $374.40 3 Freight $0.00 4 Freight % 0.00% 5 6 USD Sales $0.00 7 Freight $0.00 8 Freight % 0.00% 9 10 CAD Sales $2,635.14 11 Freight $63.13 12 Freight % 2.40% 13 14 USD Sales $0.00 15 Freight $0.00 16 Freight % 0.00% 17 18 CAD Sales $31,084.11 19 Freight $2,366.53 20 Freight % 7.61% The total in this case would be $374.40 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT
Try this:
=SUMPRODUCT(--(A3:A21="USD"), --(B3:B21="Sales"),--(C3:C21)) To learn more about SUMPRODUCT, check out this page: http://www.contextures.com/xlFunctio...tml#SumProduct -- If my posting was helpful, please click the "Yes" button. ROCK ON!, Scott |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT
That worked fine. Thanks. My problem is now i need to total the freight from
the USD accounts only as well. I'm thinking some sort of offset? My problem now is that i want to total the feight based on the same criteria "porter444" wrote: Try this: =SUMPRODUCT(--(A3:A21="USD"), --(B3:B21="Sales"),--(C3:C21)) To learn more about SUMPRODUCT, check out this page: http://www.contextures.com/xlFunctio...tml#SumProduct -- If my posting was helpful, please click the "Yes" button. ROCK ON!, Scott |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT
Sorry i got distracted and stated the same thing twice in the prievious post.
"Picman" wrote: That worked fine. Thanks. My problem is now i need to total the freight from the USD accounts only as well. I'm thinking some sort of offset? My problem now is that i want to total the feight based on the same criteria "porter444" wrote: Try this: =SUMPRODUCT(--(A3:A21="USD"), --(B3:B21="Sales"),--(C3:C21)) To learn more about SUMPRODUCT, check out this page: http://www.contextures.com/xlFunctio...tml#SumProduct -- If my posting was helpful, please click the "Yes" button. ROCK ON!, Scott |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT
i need to total the freight from the USD accounts only as well
All USD accounts or just those that are type "sales" ? As long as the location of "freight" has a consistent offset, 1 row below, 2 columns to the right. Array entered** : =SUM(IF(A2:A16="USD",OFFSET(A2:A16,1,2))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Picman" wrote in message ... That worked fine. Thanks. My problem is now i need to total the freight from the USD accounts only as well. I'm thinking some sort of offset? My problem now is that i want to total the feight based on the same criteria "porter444" wrote: Try this: =SUMPRODUCT(--(A3:A21="USD"), --(B3:B21="Sales"),--(C3:C21)) To learn more about SUMPRODUCT, check out this page: http://www.contextures.com/xlFunctio...tml#SumProduct -- If my posting was helpful, please click the "Yes" button. ROCK ON!, Scott |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional sum
Hi
Try this formula: =SUMPRODUCT(--(A2:A100="USD"),--(B2:B100="Sales"),C2:C100) Regards, Per "Picman" skrev i meddelelsen ... I have 3 columns of data, the first designates the currency of sales, the second identifies the type of value input and the third is the value. I would like add the total of only the "sales" in column (C) that have a cell 2 columns to the left (A - Currency) that contains a specific value (USD). A B C 1 Currency Type Value 2 USD Sales $374.40 3 Freight $0.00 4 Freight % 0.00% 5 6 USD Sales $0.00 7 Freight $0.00 8 Freight % 0.00% 9 10 CAD Sales $2,635.14 11 Freight $63.13 12 Freight % 2.40% 13 14 USD Sales $0.00 15 Freight $0.00 16 Freight % 0.00% 17 18 CAD Sales $31,084.11 19 Freight $2,366.53 20 Freight % 7.61% The total in this case would be $374.40 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional sum
Hi,
In 2007 you could use the following: =SUMIFS(C2:C20,A2:A20,"USD",B2:B20,"Sales") as well as the SUMPRODUCT formula from the previous post If this helps, please click the Yes button Cheers, Shane Devenshire "Picman" wrote: I have 3 columns of data, the first designates the currency of sales, the second identifies the type of value input and the third is the value. I would like add the total of only the "sales" in column (C) that have a cell 2 columns to the left (A - Currency) that contains a specific value (USD). A B C 1 Currency Type Value 2 USD Sales $374.40 3 Freight $0.00 4 Freight % 0.00% 5 6 USD Sales $0.00 7 Freight $0.00 8 Freight % 0.00% 9 10 CAD Sales $2,635.14 11 Freight $63.13 12 Freight % 2.40% 13 14 USD Sales $0.00 15 Freight $0.00 16 Freight % 0.00% 17 18 CAD Sales $31,084.11 19 Freight $2,366.53 20 Freight % 7.61% The total in this case would be $374.40 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional sum
This didn't work for me.
"Shane Devenshire" wrote: Hi, In 2007 you could use the following: =SUMIFS(C2:C20,A2:A20,"USD",B2:B20,"Sales") as well as the SUMPRODUCT formula from the previous post If this helps, please click the Yes button Cheers, Shane Devenshire "Picman" wrote: I have 3 columns of data, the first designates the currency of sales, the second identifies the type of value input and the third is the value. I would like add the total of only the "sales" in column (C) that have a cell 2 columns to the left (A - Currency) that contains a specific value (USD). A B C 1 Currency Type Value 2 USD Sales $374.40 3 Freight $0.00 4 Freight % 0.00% 5 6 USD Sales $0.00 7 Freight $0.00 8 Freight % 0.00% 9 10 CAD Sales $2,635.14 11 Freight $63.13 12 Freight % 2.40% 13 14 USD Sales $0.00 15 Freight $0.00 16 Freight % 0.00% 17 18 CAD Sales $31,084.11 19 Freight $2,366.53 20 Freight % 7.61% The total in this case would be $374.40 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional sum
Hi,
I copied your data from the post and it worked fine for me. But you must be using Excel 2007, this won't work in 2003. Cheers, Shane "Picman" wrote: This didn't work for me. "Shane Devenshire" wrote: Hi, In 2007 you could use the following: =SUMIFS(C2:C20,A2:A20,"USD",B2:B20,"Sales") as well as the SUMPRODUCT formula from the previous post If this helps, please click the Yes button Cheers, Shane Devenshire "Picman" wrote: I have 3 columns of data, the first designates the currency of sales, the second identifies the type of value input and the third is the value. I would like add the total of only the "sales" in column (C) that have a cell 2 columns to the left (A - Currency) that contains a specific value (USD). A B C 1 Currency Type Value 2 USD Sales $374.40 3 Freight $0.00 4 Freight % 0.00% 5 6 USD Sales $0.00 7 Freight $0.00 8 Freight % 0.00% 9 10 CAD Sales $2,635.14 11 Freight $63.13 12 Freight % 2.40% 13 14 USD Sales $0.00 15 Freight $0.00 16 Freight % 0.00% 17 18 CAD Sales $31,084.11 19 Freight $2,366.53 20 Freight % 7.61% The total in this case would be $374.40 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional sum
i'm using 2003, any ideas for me.
"Shane Devenshire" wrote: Hi, I copied your data from the post and it worked fine for me. But you must be using Excel 2007, this won't work in 2003. Cheers, Shane "Picman" wrote: This didn't work for me. "Shane Devenshire" wrote: Hi, In 2007 you could use the following: =SUMIFS(C2:C20,A2:A20,"USD",B2:B20,"Sales") as well as the SUMPRODUCT formula from the previous post If this helps, please click the Yes button Cheers, Shane Devenshire "Picman" wrote: I have 3 columns of data, the first designates the currency of sales, the second identifies the type of value input and the third is the value. I would like add the total of only the "sales" in column (C) that have a cell 2 columns to the left (A - Currency) that contains a specific value (USD). A B C 1 Currency Type Value 2 USD Sales $374.40 3 Freight $0.00 4 Freight % 0.00% 5 6 USD Sales $0.00 7 Freight $0.00 8 Freight % 0.00% 9 10 CAD Sales $2,635.14 11 Freight $63.13 12 Freight % 2.40% 13 14 USD Sales $0.00 15 Freight $0.00 16 Freight % 0.00% 17 18 CAD Sales $31,084.11 19 Freight $2,366.53 20 Freight % 7.61% The total in this case would be $374.40 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional sum
SUMIFS is a new function add to Excel 2007 version, you don't find it in
Excel 2003 In your case, use the =SUM(IF(A2:A16="USD",OFFSET(A2:A16,1,2))) provided pls click yes if this help. "Picman" wrote: i'm using 2003, any ideas for me. "Shane Devenshire" wrote: Hi, I copied your data from the post and it worked fine for me. But you must be using Excel 2007, this won't work in 2003. Cheers, Shane "Picman" wrote: This didn't work for me. "Shane Devenshire" wrote: Hi, In 2007 you could use the following: =SUMIFS(C2:C20,A2:A20,"USD",B2:B20,"Sales") as well as the SUMPRODUCT formula from the previous post If this helps, please click the Yes button Cheers, Shane Devenshire "Picman" wrote: I have 3 columns of data, the first designates the currency of sales, the second identifies the type of value input and the third is the value. I would like add the total of only the "sales" in column (C) that have a cell 2 columns to the left (A - Currency) that contains a specific value (USD). A B C 1 Currency Type Value 2 USD Sales $374.40 3 Freight $0.00 4 Freight % 0.00% 5 6 USD Sales $0.00 7 Freight $0.00 8 Freight % 0.00% 9 10 CAD Sales $2,635.14 11 Freight $63.13 12 Freight % 2.40% 13 14 USD Sales $0.00 15 Freight $0.00 16 Freight % 0.00% 17 18 CAD Sales $31,084.11 19 Freight $2,366.53 20 Freight % 7.61% The total in this case would be $374.40 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| New Conditional Format Overriding Previous Conditional Format | Excel Discussion (Misc queries) | |||
| Conditional Rank (or rather, Conditional Range) | Excel Worksheet Functions | |||
| Conditional Formatting that will display conditional data | Excel Worksheet Functions | |||
Hybrid Mode
