|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
conditional sum
I'm still getting an error
"franciz" wrote: 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 |
conditional sum
After some testing the problem seems to be in the range. if i use only a
single cell reference (A2) i get the desired result, and when i input the range again (A2:A16) i get an error. "Picman" wrote: I'm still getting an error "franciz" wrote: 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 |
conditional sum
Did you enter the formula as an array?
Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you *must* use a combination of keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key. When done properly Excel will enclose the formulas in squiggly brackets { }. You can't just type these brackets in, you *must* use the key combo. Also, anytime you edit an array formula it *must* be re-entered as an array using the key combo. -- Biff Microsoft Excel MVP "Picman" wrote in message ... After some testing the problem seems to be in the range. if i use only a single cell reference (A2) i get the desired result, and when i input the range again (A2:A16) i get an error. "Picman" wrote: I'm still getting an error "franciz" wrote: 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 |
conditional sum
This is array formula, press Ctl,Shift and Enter on your keyboard all at the
same time. Post back if you don't get it Hope this help. Pls click Yes if this help cheers "Picman" wrote: After some testing the problem seems to be in the range. if i use only a single cell reference (A2) i get the desired result, and when i input the range again (A2:A16) i get an error. "Picman" wrote: I'm still getting an error "franciz" wrote: 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 |
conditional sum
That did it. Thank you all very much.
"T. Valko" wrote: Did you enter the formula as an array? Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you *must* use a combination of keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key. When done properly Excel will enclose the formulas in squiggly brackets { }. You can't just type these brackets in, you *must* use the key combo. Also, anytime you edit an array formula it *must* be re-entered as an array using the key combo. -- Biff Microsoft Excel MVP "Picman" wrote in message ... After some testing the problem seems to be in the range. if i use only a single cell reference (A2) i get the desired result, and when i input the range again (A2:A16) i get an error. "Picman" wrote: I'm still getting an error "franciz" wrote: 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 |
conditional sum
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Picman" wrote in message ... That did it. Thank you all very much. "T. Valko" wrote: Did you enter the formula as an array? Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you *must* use a combination of keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key. When done properly Excel will enclose the formulas in squiggly brackets { }. You can't just type these brackets in, you *must* use the key combo. Also, anytime you edit an array formula it *must* be re-entered as an array using the key combo. -- Biff Microsoft Excel MVP "Picman" wrote in message ... After some testing the problem seems to be in the range. if i use only a single cell reference (A2) i get the desired result, and when i input the range again (A2:A16) i get an error. "Picman" wrote: I'm still getting an error "franciz" wrote: 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 |
| All times are GMT +1. The time now is 04:52 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com