| Home |
| Search |
| Today's Posts |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
THANKS SCOTT!!!
Thanks again, good info!!!
"Scott" wrote in message ups.com... For the explanation of how I used sumproduct, this link does a much better job than I can: (you can read through it... then i provided a few extra details about the actual function used) http://www.xldynamic.com/source/xld.SUMPRODUCT.html Basically, sumproduct can be used for multiple condition testing. (Which that site goes on to describe in some detail). For the formula I gave you, it was slightly modified. The first sumproduct just checks to see if one of the previous lines has had this box on it. This is done by freezing the first reference, and allowing the other to change. If the count equals 1, this is the first occurance, so the calculation needs to be done. If it's greater than 1, the calculation was done on a previous row. For the second sumproduct, we know this is the first occurance of this box. So the calculation only needs to look at the rows from the current row to the end (there are none before it). The first parameter is the condition, the second is the data we're summing. For both of these, SUMIF could have been used instead. I tend to use SUMPRODUCT more frequently because it is more robust. HTH, Scott Brian wrote: Thanks a million Scott! Worked great, I only changed "Delete" to "" so nothing would be in those cells. If you have time, could you walk me though the logic of the arguments? Thanks, Brian "Scott" wrote in message oups.com... Try this: In D1, put and drag down: =IF(SUMPRODUCT(--($A$1:A1=A1))1,"Delete",SUMPRODUCT(--(A1:$A$9=A1),B1:$B$9)) In E1, put and drag down: =IF(SUMPRODUCT(--($A$1:A1=A1))1,"Delete",SUMPRODUCT(--(A1:$A$9=A1),C1:$C$9)) Scott Brian wrote: Howdy All, I have an inventory spreadsheet that contains 3 piece of pertinent info. Column A contains Box Number (i.e. 1, 2,3, etc) and there are repeat numbers Column B contains Number of Pages for given charts contained within the boxes. Column C contains Hours. Example: A1 = 1, B1 = 745, C1 = 2.5 A2 = 1, B2 = 555, C1 = 1.75 A3 = 2, B3 = 1007, C3 = 3.25 A4 = 2, B4 = 345, C4 = 0.75 I want to get the Total Number of Pages, and Total Hours, per box. So, in the example I want to know that for Box 1, I had 1300 total pages and 4.25 hours. For Box 2 I had 1352 total pages and 4.00 hours. Any ideas? Thanks, Brian |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Automatic totalling of spend on repeated items | Excel Discussion (Misc queries) | |||
| Searching columns, summarizing, and totalling? | Excel Discussion (Misc queries) | |||
| How do I sum hours and minutes totalling over 24 hours? | Excel Worksheet Functions | |||
| word appearance and totalling | Excel Discussion (Misc queries) | |||
| Totalling Sum of Parts in a 2 column array. | Excel Discussion (Misc queries) | |||
Threaded Mode
