Summing multiple rows with the same item number - How do you interpret this code (inside)

Hello,

Thanks for any insight!

I'm trying to interpret what this function represents in plain english:

Please Login or Register  to view this content.

They way I read it is that: if the item has B in b10 sum these columns.

What does the "...C10<>C9)" and ("C:C", C10,P:P)" mean/represent?

The reason I ask, is that if I highlight a row and copy/insert copied cells, the total that comes up in the column sometimes behaves oddly (as in doesn't report the proper total)

Thanks!

There are two conditions being tested, and only if both of them are TRUE will the sum appear. The two conditions are that B10 is equal to the letter "B" AND C10 is not equal to C9. If that is the case then the second part of the formula will kick in, and this is a conditional sum, meaning that you want the sum of the cells from column P but only where the corresponding cell in column C is equal to C10, otherwise a blank will appear as the result.

I suspect that you have items in column C that have been sorted, and that you want a sum total of the cells from column P for the particular item in column C, but for this to appear only on the first occurrence of the item in C (and at the same time for the cell in column B to equal "B").

Hope this helps.

Pete

That does help, and i think I know part of my problem.
If I'm understanding what you just explained, me copying and inserting one row directly above each other, thus both columns having the same #. So since the inserted row above the original is equal to the one below the SUMIF section of the formula isn't happening.

It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Pete

Whoops. Replied to an outdated page

Originally Posted by Pete_UK

It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Pete

Is there a way to ADD a condition to which the sumif kicks out a total? Right now its looking at the item number in C. Can I have it look at item number in C and a manufacturer name from another column (and if they are different a separate sum is calculated)? So they can share the same item number(which is why its summing things weird)

Originally Posted by popipipo

Try the SUMIFS function
SYNTAX: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

This function is available from version excel 2007

I looked at it a little, and not quite sure how I would go about doing it.

In the attached example at the bottom you can see there are two sets of item 1003. Each set starts with "B" as a base item and then has "O" or optional items that are part of the base item. These two sets of 1003 need to be summed separately in column U (LIP). So C16:C19 and C20:C23 would sum column P individually.
The "make" column F would have different values in the two sets of item 1003 which I think could be used as a criteria to sum them individually. I'm not sure how you would go about this though.

Thanks for you help!

Originally Posted by JeteMc

Try modifying the formula in column U as follows and see if it does what you want:

Formula:

Please Login or Register  to view this content.

Let me know if you have any questions.

Well it sort of accomplishes a part of the problem, but it seems the formula isn't looking at the item numbers? I could have 50 lines, all with unique item numbers and have the same value in the F column, but the formula should only total each occurrence of the item number individually (the B and O part of the item number in column A). The formula you wrote (thanks by the way), totals ALL the cells with the same value in column F (even though they have different item numbers).
Does that make sense?

Originally Posted by JeteMc

I may have misunderstood post #8 however the formula is summing item #'s 1001 and 1002 separately ($1680 and $880) and then sums the "two sets of 1003" which "need to be summed separately in column U (LIP)" (both $610, however if you change D22 to 1 then only U20 changes, to $595).
My suggestion is to manually put some numbers in column U so that we can see what you expect the formula to yield.

Ok, try this attachment.

Thanks!

Originally Posted by JeteMc

Different approach. Since every item has a base price plus prices for options, lets just start with each base and add any options that occur after that. To do this I have added three helper columns, which may be hidden for aesthetic purposes. The helper columns find the addresses of the base and last option using formulas like:

Formula:

Please Login or Register  to view this content.

The formula in column U sums that range using:

Formula:

Please Login or Register  to view this content.

Let me know if you have any comments/questions.

Well I tried integrating it, but I'm having a problem (I shifted your helper cells over to columns Y,Z, and AA. In column AA I'm getting "#N/A" and if I go into the cell and hit enter to try and 'refresh' the formula I receive a "Run-time error '13': Type Mismatch".

Any idea what would be causing this?

It looks like it works awesome in the sample you uploaded though, thanks!

Originally Posted by JeteMc

Manually change the column references in the formula from W to Z as in:

Formula:

Please Login or Register  to view this content.

Let me know if you have any questions.

I had already manually updated the columns, but its still creating the error