Forum Discussion

Sazzy's avatar
Sazzy
Copper Contributor
Mar 28, 2026

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.





 

excel
Formulas and Functions

6 Replies

  • Sazzy's avatar
    Sazzy
    Copper Contributor
    Mar 30, 2026

    Thank you both for taking the time to respond.

    It won't let me attach a copy of the spreadsheet but here is a link to it

    https://1drv.ms/x/c/c35c074091694af2/IQD6efwQOYdiQKOITnxt4AyqAYWnf9GmFLCdyknPrHOBB4Y?e=sbX5Pv

     
    I am a novice with excel so I have developed the spreadsheet the best I can with my current knowledge.
    I didn't know how to set it up for if someone orders multiple of the same item so when that has happened I have just been entering it on the row below. If there is a better way then I am open to all suggestions.

    The blue section was just an extract of the spreadsheet I have for the logo sizes (it has its own tab in the worksheet) but essentially if someone was to order a 'YOUTH M (7/8yr) Hoodie' then that requires a 6cm AND an 18cm logo (one for the front of the hoodie and one for the back). However a 'YOUTH M (7/8yr) T-shirt' only requires a 6cm logo for the front as it is not having a logo on the back.

     

    The text for all the entries should be the same as I have created the spreadsheet with drop down lists so that I can just select the relevant items on each order. What is data validation?

    I will have a look at possibly re-doing the spreadsheet, I just wanted something that clearly displayed what each person had ordered so it could be referred to when packaging orders. 

    Thank you for your help

    • m_tarler's avatar
      m_tarler
      Silver Contributor
      Apr 04, 2026

      Hi Sazzy,

         Thank you for sharing the workbook.  on the MTA ('26) tab I created a table with the logo sizes.  I found a number of issues in the process.  First off the table you had didn't have the right logo size options for MTA.  Next i found in the tables on the 'logo sizes' tab you had entries like:
      YOUTH XS (3/4yr) Hoodie & Zoodie
      which means the  "YOUTH XS (3/4yr) Zoodie" would not match that entry (as I mentioned the text for all entries need to be identical). I also found 2 other differences including Tee vs T-shirt and Strap Top vs Sprag Strap Top.

      I made corrections on the MTA table but didn't touch the S.S table.

      So the updated formula I use actually builds the logo sizes table with all the possible sizes based on the logo sizes tab and then does the counting.  This is great but I found 1 problem.  The number of orders was 20 but I found 21 logo sizes for the smaller sizes (i.e. not counting the larger back logos).  I believe this is because 

      "L Spag strap top" will match with  "XL Spag strap top" and both of those match with "XXL Spag strap top" and so on.

      This is a problem with the configuration you have.  Even if you have a more consistent comma deliminated list or always had each item on its own line and added an extra line on each cell we make the search explicitely look for a line feed immediately before the item text.  but IMHO these are just 'hacks' to make it work. (btw I put everything on its own line and added the extra line return and added the preceeding line return to the search to make it work)

      As for what I feel it a better way to set it all up:

      I attached a file in a prior reply with a suggested different layout.  It has the main data table and then a item table that can include the logo sizes there and finally a table to sum up the logo sizes.  By adding the logo size lookup to the data table the sum becomes really easy to find.

      That item table showing all the different items and sizes and logo sizes could be on a separate tab and have multiple columns for different customers and front and back. then the lookups would just pull from the corresponding columns.

       

  • m_tarler's avatar
    m_tarler
    Silver Contributor
    Mar 30, 2026

    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.

  • mathetes's avatar
    mathetes
    Gold Contributor
    Mar 30, 2026

    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.