Forum Discussion

wmjames1's avatar
wmjames1
Copper Contributor
Nov 24, 2021
Solved

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 "The...
Charting
excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Nov 24, 2021

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

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Nov 24, 2021

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.

wmjames1's avatar
wmjames1
Copper Contributor
Nov 24, 2021
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.
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Nov 24, 2021

    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.

     

    • wmjames1's avatar
      wmjames1
      Copper Contributor
      Nov 24, 2021
      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.
      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        Nov 24, 2021

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

Resources