Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif with multiple variables
My data is arranged in three columns: Col a is my inventory
count date, Col b is a code letter (a,b,c or blank), col c is my sales. col a col b col c s04 a 10 f04 b 12 s05 c 13 f04 a 10 s04 a 12 f04 (blank) 9 I need to sumif all numbers in column c where column a and column b match (for example: col a has 2 s04 where col b has an A so I need the total of 10+12. I suppose I could concatenate A+B, and then sumif on that, but is there a better way? Thanks for any help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif with multiple variables
Here's a link to a recent discussion of that very same topic:
http://groups.google.com/group/microsoft.public.excel.misc/browse_thread/thread/e0d563106f3d908f/98aed4413d44708e?q=%3DSUMPRODUCT(--(A1%3AA10%3D%22x%22)%2C--(B1%3AB10%3D%22y%22)%2CC1%3AC10)+&rnum=1#98aed4413 d44708e |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif with multiple variables
try
=sumproduct((a2:a200="s04")*(b2:b200="a")*c2:c200) -- Don Guillett SalesAid Software "les8" wrote in message ... My data is arranged in three columns: Col a is my inventory count date, Col b is a code letter (a,b,c or blank), col c is my sales. col a col b col c s04 a 10 f04 b 12 s05 c 13 f04 a 10 s04 a 12 f04 (blank) 9 I need to sumif all numbers in column c where column a and column b match (for example: col a has 2 s04 where col b has an A so I need the total of 10+12. I suppose I could concatenate A+B, and then sumif on that, but is there a better way? Thanks for any help. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif with multiple variables
Try something like this:
D1: =SUMPRODUCT((A1:A100="s04")*(B1:B100="a")*C1:C100) Change range references to suit your situation. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "les8" wrote: My data is arranged in three columns: Col a is my inventory count date, Col b is a code letter (a,b,c or blank), col c is my sales. col a col b col c s04 a 10 f04 b 12 s05 c 13 f04 a 10 s04 a 12 f04 (blank) 9 I need to sumif all numbers in column c where column a and column b match (for example: col a has 2 s04 where col b has an A so I need the total of 10+12. I suppose I could concatenate A+B, and then sumif on that, but is there a better way? Thanks for any help. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif with multiple variables
Ron and Don,
Thank you both very much. It works! "Don Guillett" wrote: try =sumproduct((a2:a200="s04")*(b2:b200="a")*c2:c200) -- Don Guillett SalesAid Software "les8" wrote in message ... My data is arranged in three columns: Col a is my inventory count date, Col b is a code letter (a,b,c or blank), col c is my sales. col a col b col c s04 a 10 f04 b 12 s05 c 13 f04 a 10 s04 a 12 f04 (blank) 9 I need to sumif all numbers in column c where column a and column b match (for example: col a has 2 s04 where col b has an A so I need the total of 10+12. I suppose I could concatenate A+B, and then sumif on that, but is there a better way? Thanks for any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use the sumif if I have multiple criteria (i.e. greater . | Excel Worksheet Functions | |||
SUMIF with multiple criteria | Excel Discussion (Misc queries) | |||
SUMIF on multiple sheets | Excel Worksheet Functions | |||
Can I use multiple criteria in SUMIF funqtion? | Excel Worksheet Functions | |||
How do I ask for multiple criteria when creating a "sumif" formul. | Excel Worksheet Functions |