Sum of different quantities by product code

**GLewis14** · 07-19-2019, 04:45 AM

Hi all,
I am looking for a formula to sum quantities of different codes, that fall within different time brackets.
For example, in the attachment, in cells L3, L6 and L9, I need to sum the quantities of the different product codes, which are in column F (so cell L3 should read 25, cell L6 should read 45 and cell L9 should be blank (not 0) ).
I then need to replicate this for all the “plan” rows, in columns K,L and M.

Many thanks for any help, appreciated.

**AliGW** · 07-19-2019, 04:50 AM

Ah! A nightmare in merged cells ...

And something tells me it's not even as straightforward as this. Would I be right?

**jason.b75** · 07-19-2019, 04:53 AM

They're not merged, Ali!

GLewis14, others need to learn from your approach.

If you change K2 to '02 MONDAY so that it matches column B, then change K2:M2 to Shift 1, Shift 2 and Shift 3, then this formula in K3 and copied to the other cells will do what you need.

Formula:

Please Login or Register  to view this content.

**AliGW** · 07-19-2019, 04:55 AM

You're right, Jason - should have looked closer. More caffeine, methinks ...

I agree - this is a good approach - I have used it myself and set conditional formatting to show or hide the text as appropriate.

**jason.b75** · 07-19-2019, 05:01 AM

I do similar, but tend to go with a subtle difference between font and fill colour so that I can still see what is there.

I'm AFK for a few hours, but one quick observation, the times in column D are actually 'text' numbers. If my earlier suggestion is not suitable for what is needed, then these would need to be proper times before they can be used for formulas.

**GLewis14** · 07-19-2019, 05:01 AM

Thanks Jason

**AliGW** · 07-19-2019, 05:03 AM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

**Olly** · 07-19-2019, 05:06 AM

As usual, I'm going to make it so much more complicated (at first glance), and suggest using PowerPivot.

First of all, give your data (B:F) headings, then format as a table.

Now we can use Power Query to reshape the data, a bit:

Product Data:

Please Login or Register  to view this content.

Close and Load To the Data Model.

Now in PowerPivot, create a few measures:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

This didn't take much longer than the single formula solution, but now we can produce really flexible, scalable pivot reports. See attachment for worked example.

Sum of different quantities by product code

LinkBack

Thread Tools

Rate This Thread

Display

Sum of different quantities by product code

Re: Sum of different quantities by product code

Re: Sum of different quantities by product code

Re: Sum of different quantities by product code

Re: Sum of different quantities by product code

Re: Sum of different quantities by product code

Re: Sum of different quantities by product code

Re: Sum of different quantities by product code

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Lowest price against each product code ( grading of price against each product code )

[SOLVED] Use vlookup to change old product code to new product code

vlookup to find a product based on code, but still able to find product when code changes

[SOLVED] Sum quantities of product by year

Link product image to product code

[SOLVED] 4 digit code for similar product, new product unique code

[SOLVED] Need Function that will find ordered product, and display the product code in a 2nd workbk

Bookmarks

Bookmarks

Posting Permissions