Count by sizes

[wmjames1]

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

[#2]

@wmjames1 

=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.

[#3]

I will definitely look at this after I get from my appointment, thank you for you help

[#4]

@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.

[#5]

@wmjames1 

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.

[#6]

@OliverScheurich because of all the sizes throughout the workbook I have. Yes I have small, medium and large, but will also have 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 for sizes for the uniforms for each person. Im about to test out the formula you provided. thank you again.

[#7 · Accepted solution]

@wmjames1 

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).

[#8]

thank you very much!