| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
adding multiple sums w/ different criteria
I have been able to partialy add the sums for the following table using sum(if
1/9/07 g h i j k l desc. ser. code date rec. cost/day status cost to date date ret. xxx xxx 100 1/1/07 $100 here $200 xxx xxx 200 1/1/07 $100 here $200 xxx xxx 100 1/1/07 $100 return $200 1/5/07 xxx xxx 300 1/1/07 $100 here $200 xxx xxx 100 1/1/07 $100 enroute $200 What I am attempting to do is add all cells for a daily cost where the equipt. with the same cost code is either still here or enroute, and make it quit adding up items on a daily ticket for parts returned. the formula I am using is =SUM(IF((G6:G12="100")+(J6:J12="here"),I6:I12)) The problem is that it adds the items here even though it is the incorrect cost code. Any help is greatly appreciated as I take this info and add to a second worksheet w/ the indivdual cost / per day of each code broke down. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
adding multiple sums w/ different criteria
Doug
An array formula should do this. =sum(if((G6:G12=100)*(J6:J12="here"),I6:I12,false) ) Type in the formula and press CTRL+SHIFT+ENTER to enter it. Watch out for the relative/absoulte references if you intend to move this around elsewhere. More info on arrays at Chip Pearsons site http://www.cpearson.com/excel/array.htm Good luck Murray Doug wrote: I have been able to partialy add the sums for the following table using sum(if 1/9/07 g h i j k l desc. ser. code date rec. cost/day status cost to date date ret. xxx xxx 100 1/1/07 $100 here $200 xxx xxx 200 1/1/07 $100 here $200 xxx xxx 100 1/1/07 $100 return $200 1/5/07 xxx xxx 300 1/1/07 $100 here $200 xxx xxx 100 1/1/07 $100 enroute $200 What I am attempting to do is add all cells for a daily cost where the equipt. with the same cost code is either still here or enroute, and make it quit adding up items on a daily ticket for parts returned. the formula I am using is =SUM(IF((G6:G12="100")+(J6:J12="here"),I6:I12)) The problem is that it adds the items here even though it is the incorrect cost code. Any help is greatly appreciated as I take this info and add to a second worksheet w/ the indivdual cost / per day of each code broke down. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
adding multiple sums w/ different criteria
This only gets an answer of true for me. It still is not adding the cells and
giving me a total. What I need it to do is add all cells which are code 100 and are here or enroute. I do no want it to count cells which are coded 100 and are returned. Thanks for the help just not quite there. "Murray" wrote: Doug An array formula should do this. =sum(if((G6:G12=100)*(J6:J12="here"),I6:I12,false) ) Type in the formula and press CTRL+SHIFT+ENTER to enter it. Watch out for the relative/absoulte references if you intend to move this around elsewhere. More info on arrays at Chip Pearsons site http://www.cpearson.com/excel/array.htm Good luck Murray Doug wrote: I have been able to partialy add the sums for the following table using sum(if 1/9/07 g h i j k l desc. ser. code date rec. cost/day status cost to date date ret. xxx xxx 100 1/1/07 $100 here $200 xxx xxx 200 1/1/07 $100 here $200 xxx xxx 100 1/1/07 $100 return $200 1/5/07 xxx xxx 300 1/1/07 $100 here $200 xxx xxx 100 1/1/07 $100 enroute $200 What I am attempting to do is add all cells for a daily cost where the equipt. with the same cost code is either still here or enroute, and make it quit adding up items on a daily ticket for parts returned. the formula I am using is =SUM(IF((G6:G12="100")+(J6:J12="here"),I6:I12)) The problem is that it adds the items here even though it is the incorrect cost code. Any help is greatly appreciated as I take this info and add to a second worksheet w/ the indivdual cost / per day of each code broke down. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
adding multiple sums w/ different criteria
One way:
=SUMPRODUCT(--(G6:G12=100),(J6:J12="here")+(J6:J12="enroute"),I6 :I12) In article , Doug wrote: This only gets an answer of true for me. It still is not adding the cells and giving me a total. What I need it to do is add all cells which are code 100 and are here or enroute. I do no want it to count cells which are coded 100 and are returned. Thanks for the help just not quite there. "Murray" wrote: Doug An array formula should do this. =sum(if((G6:G12=100)*(J6:J12="here"),I6:I12,false) ) Type in the formula and press CTRL+SHIFT+ENTER to enter it. Watch out for the relative/absoulte references if you intend to move this around elsewhere. More info on arrays at Chip Pearsons site http://www.cpearson.com/excel/array.htm Good luck Murray Doug wrote: I have been able to partialy add the sums for the following table using sum(if 1/9/07 g h i j k l desc. ser. code date rec. cost/day status cost to date date ret. xxx xxx 100 1/1/07 $100 here $200 xxx xxx 200 1/1/07 $100 here $200 xxx xxx 100 1/1/07 $100 return $200 1/5/07 xxx xxx 300 1/1/07 $100 here $200 xxx xxx 100 1/1/07 $100 enroute $200 What I am attempting to do is add all cells for a daily cost where the equipt. with the same cost code is either still here or enroute, and make it quit adding up items on a daily ticket for parts returned. the formula I am using is =SUM(IF((G6:G12="100")+(J6:J12="here"),I6:I12)) The problem is that it adds the items here even though it is the incorrect cost code. Any help is greatly appreciated as I take this info and add to a second worksheet w/ the indivdual cost / per day of each code broke down. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
adding multiple sums w/ different criteria
With that I get the #NAME error.
"JE McGimpsey" wrote: One way: =SUMPRODUCT(--(G6:G12=100),(J6:J12="here")+(J6:J12="enroute"),I6 :I12) In article , Doug wrote: This only gets an answer of true for me. It still is not adding the cells and giving me a total. What I need it to do is add all cells which are code 100 and are here or enroute. I do no want it to count cells which are coded 100 and are returned. Thanks for the help just not quite there. "Murray" wrote: Doug An array formula should do this. =sum(if((G6:G12=100)*(J6:J12="here"),I6:I12,false) ) Type in the formula and press CTRL+SHIFT+ENTER to enter it. Watch out for the relative/absoulte references if you intend to move this around elsewhere. More info on arrays at Chip Pearsons site http://www.cpearson.com/excel/array.htm Good luck Murray Doug wrote: I have been able to partialy add the sums for the following table using sum(if 1/9/07 g h i j k l desc. ser. code date rec. cost/day status cost to date date ret. xxx xxx 100 1/1/07 $100 here $200 xxx xxx 200 1/1/07 $100 here $200 xxx xxx 100 1/1/07 $100 return $200 1/5/07 xxx xxx 300 1/1/07 $100 here $200 xxx xxx 100 1/1/07 $100 enroute $200 What I am attempting to do is add all cells for a daily cost where the equipt. with the same cost code is either still here or enroute, and make it quit adding up items on a daily ticket for parts returned. the formula I am using is =SUM(IF((G6:G12="100")+(J6:J12="here"),I6:I12)) The problem is that it adds the items here even though it is the incorrect cost code. Any help is greatly appreciated as I take this info and add to a second worksheet w/ the indivdual cost / per day of each code broke down. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
adding multiple sums w/ different criteria
Are you using a non-English version of XL? The SUMPRODUCT() function may
have a different local name. If you're using an English version, you made an error in typing or copying the formula. In article , Doug wrote: With that I get the #NAME error. "JE McGimpsey" wrote: One way: =SUMPRODUCT(--(G6:G12=100),(J6:J12="here")+(J6:J12="enroute"),I6 :I12) |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
adding multiple sums w/ different criteria
Doug
Your original formula only tried to count the "here"'s, not the "enroute"s. That can be overcome, but nevertheless using your data as posted and summing data where code=100 and status=here should give an answer of 100 from column I. Is that correct? The formula I posted works when I do it and should not give a value of true. Are you sure you entered it correctly. If you want to mail me the sheet (or part of it) I can have a look. Regards Murray Doug wrote: This only gets an answer of true for me. It still is not adding the cells and giving me a total. What I need it to do is add all cells which are code 100 and are here or enroute. I do no want it to count cells which are coded 100 and are returned. Thanks for the help just not quite there. "Murray" wrote: Doug An array formula should do this. =sum(if((G6:G12=100)*(J6:J12="here"),I6:I12,false) ) Type in the formula and press CTRL+SHIFT+ENTER to enter it. Watch out for the relative/absoulte references if you intend to move this around elsewhere. More info on arrays at Chip Pearsons site http://www.cpearson.com/excel/array.htm Good luck Murray Doug wrote: I have been able to partialy add the sums for the following table using sum(if 1/9/07 g h i j k l desc. ser. code date rec. cost/day status cost to date date ret. xxx xxx 100 1/1/07 $100 here $200 xxx xxx 200 1/1/07 $100 here $200 xxx xxx 100 1/1/07 $100 return $200 1/5/07 xxx xxx 300 1/1/07 $100 here $200 xxx xxx 100 1/1/07 $100 enroute $200 What I am attempting to do is add all cells for a daily cost where the equipt. with the same cost code is either still here or enroute, and make it quit adding up items on a daily ticket for parts returned. the formula I am using is =SUM(IF((G6:G12="100")+(J6:J12="here"),I6:I12)) The problem is that it adds the items here even though it is the incorrect cost code. Any help is greatly appreciated as I take this info and add to a second worksheet w/ the indivdual cost / per day of each code broke down. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Retrieving a Value from List that meets multiple Criteria | Excel Discussion (Misc queries) | |||
| Multiple criteria LOOKUP | Excel Worksheet Functions | |||
| Multiple Criteria | Excel Worksheet Functions | |||
| Sum Multiple Criteria or DcountA | Excel Worksheet Functions | |||
| Multiple Criteria IF Nesting | Excel Worksheet Functions | |||
Linear Mode
