Forum Discussion
Help with summing totals using multiple texts
Good evening.
I have attached an image of my spreadsheet below which hopefully will help my blurb make a bit more sense!
I am producing some uniform for a local club. I have made a spreadsheet to allow me to track the number of garments and sizes ordered (purple table). However from this information in the purple table I need to know how many logos of each size to order (orange table).
I know what size logo is required for all the different garments and sizes of garment, as can be seen in the blue table, but this would require me to go through 1 order at a time tallying up what size logos I require. Ideally I would like excel to be able to look at the purple table and tell me how many items there are that require a given size logo.
For example, I know that a 'Youth S T-shirt' and a 'S Legging' and 'Youth M Hoodie' all require a 6cm logo. and also that the 'Youth M Hoodie' also requires an 18cm logo.
I would therefore like excel to look at the purple table and see that there are 3 x 'Youth S T-shirt', 2 x S Legging and 2 x Youth M Hoodie, therefore 7 items require a 6cm logo, so I would like excel to populate the orange table in the 'total needed' column for 6cm (cell B23) to show '7'.
The 'Youth M Hoodie' also requires an 18cm logo and therefore cell B28 should also populate with '1'.
I have tried COUNTIF, COUNTIFS, SUMIF, SUMIFS, but none of them have worked. I do not know if I am entered the information into the equation incorrectly or if I am using the wrong function/formula.
Can anyone please help!
Many thanks.
3 Replies
ok so this is NOT the best way to have the worksheet set up. For example what will happen if someone order 2x (or more) of something? or even if they want 2 different sizes of the same item.
next I don't understand the 'blue' section and how that defines the logo size. I see a number 6 on both rows and the second row has the number 18.
lastly i caution that those entries will all have to be exactly the same for the item & size in all lines (but this is true in most cases but in my proposed format it is a bit more 'simple') I highly recommend you use data validation if you aren't already.
so for you your set up, a formula that should work to sum up the number of cases you find any of the text in that list of items from the Table range (note I defined the data found on your image in A6:I18 as "Table1"):
=SUM(COUNTIF(C34,"*"&Table1&"*"))-COUNTIF(Table1,"")so although that may work for you I suggest re-doing the sheet something more like this:
so on the left is a data entry table where you have each line be 1 item (item type + size) with a quantity
in columns J:L is a table for all the items and sizes and corresponding Logo Sizes
I added column G to the data entry table for the logo size, but that is a lookup formula so you do not enter that cell, it is automatically calculated based on the table
Finally in O:P is a simple table to sum up the orders based on logo sizes
There are MANY ways to do this, and this is just one but I highly recommend you reconsider your original format.
I will try to attach this sample file with both your tables and my suggestion.
my sample file ... maybe
It would be a lot easier to help if you could provide us with not a set of images but the actual spreadsheet. This doesn't appear to be the kind of thing that would need to be kept secret for corporate proprietary reasons. But without that, you're in effect asking us to create anew worksheets you already have in order to show how to approach the problem.
Just attach a copy of the file to your reply. You should be able to drag and drop the file. If not here, then put a copy in Google Drive or the equivalent and paste a link that allows access.
