Nov 24 2021 08:48 AM - edited Nov 24 2021 09:00 AM
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 |
Nov 24 2021 09:02 AM
=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.
Nov 24 2021 09:18 AM
Nov 24 2021 11:50 AM
Nov 24 2021 12:58 PM
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.
Nov 24 2021 01:17 PM
Nov 24 2021 03:04 PM
SolutionI 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).
Nov 24 2021 03:04 PM
SolutionI 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).