Hi Everyone,
I am trying to find way to figure out total styles count per each category and each season. The data is in different tab. Somehow I couldn't attach the file here.
I am using office 2013 and know that there is distinct count in pivot table in office 2013 that can help my issue. However my data base will keep changing and this will require me to re-pivot the data.
Therefore I prefer to use formula for my report so that any changes can be automatically captured since the file will cover large data.
For example below is data that is saved in “data tab” meantime I want to create summary on “answer” tab.
Style Season Category
FM100 Fall 16 adult
FM200 Fall 16 adult
FM300 Fall 16 adult
FM400 Fall 15 adult
FM500 Fall 16 kids
FM600 Fall 15 adult
FM700 Fall 15 kids
FM200 Fall 16 adult
Expectedly the summary would go like this in “answer” tab.
Category Fall 15 F16
adult 2 3
kids 1 1
I am not familiar with if frequency or sum product and I tried to create the formula :
=SUMPRODUCT(--(FREQUENCY(MATCH(data!C2:C11,data!C2:C11,0),ROW(data!C2:C11)-ROW(data!B2)+1)>0))
The above formula only can count total styles and I have no idea on how to customize the formula so that I can get the figure as in my summary table. Pls let me know if this is really workable with formula or there is no way except using pivot table with distinct count function. If excel can help, pls share your expertise .
Thanks in advance
Bookmarks