Cannot get SUMPRODUCT to work with calculating inventory

[lorne17]

Hello there,

I have a spreadsheet that has all my orders on the first tab. Then on the second tab I'm trying to calculate my Shirt sizes, color, *** so I can easily know what to order.

What's the best way to do this? Currently I am manually entering ***, size, color of each shirt type, then I tried SUMPRODUCT for my formulas. It's calculating the, but it's not correct. For instance, I have 1 Men, XXL, Royal shirt and it won't calculate it. It says 0 when I put my SUMPRODUCT formula in. but then Men, M, Royal calculates the total shirts correctly.

I would hope there's also a better way to do it so I don't have to type out my product out and it can pull the different varieties off the first tab to sort quantity by ***, size, color. Perhaps using INDEX somehow.

Can anyone help me with this formula and setting up my sheet? I'd be eternally in your debt! My spreadsheet is attached.

THANKS!
Lorne

[Spencer101]

If you get rid of the trailing spaces in column D on the Orders worksheet, it all adds up nicely.

For example, cell D3 = "Men" but cell D5 = "Men " with a trailing space.

EDIT: I know that only answers half of your question, but it's a start

[Kevin UK]

Have you tried checking through them, you can use COUNTIFS as you uploaded a .xlsx file.
In D3 on the QTYs sheet, this returns 54 as well.

Please Login or Register  to view this content.

[lorne17]

Thanks for the replies.

Spencer, I removed the spaces and it worked great! Thanks!

Do you guys have suggestions on how I can automatically fill out the QTYs table? I have a few notes on that tab? Should I post a new post for this?

Thanks,
Lorne

[Spencer101]

Hi Lorne,

Are there always going to be S, M, L, XL, XXL for each set of Men/Women & Colour?

If so, you would probably be better off adjusting your formulas to match the cell contents under Style, Size & Colour rather than actually typing the values into the formulas.

i.e. change the formula in D3 to:

=SUMPRODUCT((ORDERS!$D$2:$D$63=A3)*(ORDERS!$F$2:$F$63=C3)*(ORDERS!$E$2:$E$63=B3))

and copy down. That way you don't have to keep changing it for each row.

Is that enough to solve your problem or do you need a way of populating the lists automatically from the data sheet?

[Kevin UK]

What's the criteria to start with, for the second table!

Thanks for the replies.

Spencer, I removed the spaces and it worked great! Thanks!

Do you guys have suggestions on how I can automatically fill out the QTYs table? I have a few notes on that tab? Should I post a new post for this?

Thanks,
Lorne

[lorne17]

Thanks for the more automated formula for SUMPRODUCT.

However, is it possible to get the first three rows to automatically populate? So it's all automatic? So let's say I add a Purple color in the orders tab. It'll automatically populate for quantity takeoffs.

make sense?

[Spencer101]

Thanks for the more automated formula for SUMPRODUCT.

However, is it possible to get the first three rows to automatically populate? So it's all automatic? So let's say I add a Purple color in the orders tab. It'll automatically populate for quantity takeoffs.

make sense?

It is possible. I have a way of doing it using a "helper column", but I'm struggling to get them in S, M, L, XL, XXL order without changing the values to 1 - S, 2 - M, 3 - L, 4 - XXL, 5 - XXL.

[lorne17]

Well maybe you can use them that way and I can go from there. But why are the numbers needed?

[Spencer101]

My method uses an array formula to create a unique sorted list from a helper column. Because L comes before S in the alphabet, Excel sorts the sizes in alphabetical order and not in the actual ascending size order.

Adding the numbers to the beginning of the sizes would solve that.

[lorne17]

Ah makes sense. Is the L labeled? if so, then I don't mind if L comes first.

How did you array it? Would you mind sharing the template file? I appreciate the help!

[Spencer101]

This is the best I can think of at the moment.

It uses a helper column on each worksheet that you can hide for the sake of aesthetics.

There is a dynamic named range called "ConcatList" on the Orders worksheet that will adjust itself as more orders are added.

There is an array formula in column A on the QTYs worksheet that you will need to drag down to the appropriate number of rows.

As I mentioned, I'm struggling to make it sort the sizes in ascending order without adding numbers to them.

I'm sure there are better ways of doing this, but I'm stumped.
At least it's some way toward what you're trying to do.

[lorne17]

Awesome! Just what I needed, THANK YOU!

Lorne

[Spencer101]

Glad I could help

[icestationzbra]

deleted...

it seems that i had zipped through the thread and missed one of the key issues...

[Spencer101]

You've slightly missed the point of the helper columns and the trailing spaces issue was corrected early in the thread.

[Kevin UK]

Hi lorne17

Then use a pivot table, no need for formulas and helper columns.
See the attached file.

[peterrc]

Hi Lorne17,

Although Spencer101 has provided an excellent solution, a less complicated solution would be to use the "Countif" function.
Please see sheet QTYs (2) on the attached spreadsheet.

Regards

peterrc

[Spencer101]

Hi Lorne17,

Although Spencer101 has provided an excellent solution, a less complicated solution would be to use the "Countif" function.
Please see sheet QTYs (2) on the attached spreadsheet.

Regards

peterrc

Hi peterrc

Less complicated indeed, but the purpose of mine being so 'complicated' was to automatically generate the Style, Size & Colour lists on the QTY worksheet as well as add the count of each that occurs on the ORDERS worksheet.

Personally I'd have gone for either a simple macro for this or the pivot table approach, as mentioned by Kevin.
However, wherever possible I try to stick with the OPs request and fit a solution around their current workbook design if at all possible. From a sample workbook you never know how much more there is branching off of that one worksheet and using a pivot to solve one issue could result in a whole lot of redesigning elsewhere.

Adding the data validation was a nice touch and certainly something I would have employed had the OP mentioned it. I would have done this with dynamic ranges to allow for easy adding of new colours etc.

S.