Forum Discussion
Count by sizes
Hi,
So what I am trying to do is have a pivot table or anything look at this information and tell me how many X is counted for each header. Example, I want to look at a table that shows for "Thermal Shirt" and a breakdown of Small - 1, Medium - 3 and Large - 2 and so on for each column I select. Is this possible or would I need to rearrange how I have my table below? FYI the full workbook has about 30-35 headers so it is a lot of information for about 200+ people and I am creating this so my leadership can have a overall view of how many of each size has been issued out so we know how much we need to purchase at a later date. Also, MD/R is Medium Regular, MD/L is Medium Long and MD/S is Medium Short as far as sizes for the length.
I want it to be where excel looks at each columns and automatically count the values within the whole column and adds up how many times it see the same values for each value. For Instance, for "Uniform Top" all the sizes available are MS/S, MS/R, MS/L, MD/S, MD/R, MD/L, LR/S, LR/R, LR/L, XL/S, XL/R, XL/L, 2X/S, 2X/R, 2X/L, 3X/S, 3X/R and 3X/L. I don't want to have pre columns for each and every size IF ,at the time I look at the table, we didn't issue out a XL/L top... I don't want excel to count XL/L and put 0 for the how many times it saw it. I just want it to count what is there at that time and how many times it see the values. Hopefully this makes sense.
| Name | Gas Mask # | Gas Mask Condition | Thermal Shirt | Thermal Bottom | Gloves | Uniform Top | Uniform Bottom |
| mark | 153789R010 | A | MEDIUM | MEDIUM | MEDIUM | MD/R | MD/R |
| susan | 789456R010 | A | SMALL | SMALL | SMALL | SM/R | SM/R |
| tim | 159753R009 | F | MEDIUM | LARGE | LARGE | MD/L | MD/L |
| woddy | 321656R011 | A | LARGE | MEDIUM | LARGE | LG/R | LG/R |
| bobby | 664553R009 | A | LARGE | LARGE | MEDIUM | LR/L | LG/L |
| luke | 719382R009 | F | MEDIUM | MEDIUM | SMALL | MD/S | MS/S |
I was focused on the sample table and forgot about the size of the actual table. Hopefully the dynamic table will help to solve your task. In the meantime i noticed that i entered a criteria twice, column T and V are both for LR/L which is wrong.
Attached please find the corrected file with a control column (Y).
7 Replies
=COUNTIF(OFFSET(A2:A7,0,MATCH(A11,A1:H1,0)-1),A10)
Is this what you are looking for? Enter small, medium, large... in A10 and thermal shirt, thermal bott, gloves... in A11.
- OliverScheurich yes and no. Lets say column D "Thermal Shirts" were all size small. So the formula recognizes 6 size smalls. If I was to input a new name and this person wears a size medium, I want it to update on its own where it see a new value "medium" and automatically records that new value and places a 1 signifying that there is only 1 entry for size medium. There is a lot of information on my original document and going through and writing a formula for each individual seems a bit tasking and I wanted to see if there was a more efficient way of tracking all this data. Now if I had to write 1 formula to do this for each column, I can see that and wouldn't mind but to have 3-12 formulas for each columns seem a bit much.
Why would you have 3 - 12 formulas for each column? The originally suggested formula works for the column and condition (small, large, A etc.) that is chosen. The range can easily be adjusted from e.g. $A$2:$A$7 to $A$2:$A$70 in order to allow for additonal names in column A.
=COUNTIF(OFFSET($A$2:$A$70,0,MATCH($J3,$A$1:$H$1,0)-1),K$2)
With above formula you can more dynamically count data as shown in attached file. Maybe this fits better what you want to do.
- I will definitely look at this after I get from my appointment, thank you for you help