How to count unique text & number to have same function with distinct count in office 2013

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

To attach a sample workbook.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).

With table as below (in F1:H3 in the formula)

Please Login or Register  to view this content.

in G2

=COUNTIFS($B$2:$B$9,G$1,$C$2:$C$9,$F2)

Copy across and down

I have assumed "Fall 16" (Season) refers to Fall in 2016 (??) so one column.

Correctm Fall 16 is season and below F16 should be Fall 16, they are under one column.

Originally Posted by Tony Valko

Is Fall 16 in a single cell or is Fall in its own cell and 16 in its own cell?

Fall 16 is in one cell and under Season column, thanks

Originally Posted by zbor

To attach a sample workbook.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).

Thanks zbor, it worked
Last time I clicked the paperclip icon but it didn't work. It only left me with white box and couldn't do anything with it.

Originally Posted by JohnTopley

With table as below (in F1:H3 in the formula)

Please Login or Register  to view this content.

in G2

=COUNTIFS($B$2:$B$9,G$1,$C$2:$C$9,$F2)

Copy across and down

Thank you John, I tried the formula. For Fall 16 it should be 3 styles in for adult. The formula should not count the duplicate text/number and blank cell.

In the answer sheet:

B2: =SUM(SIGN(FREQUENCY(IF(Season=B$1,IF(Category=$A2,MATCH(Style,Style,0))),MATCH(Style,Style,0))))


Array formula press Ctrl+Shift+Enter, not just Enter

copy down and across

Hello Teethless Mama....

Awesomeee! it worked...so happyyy....
but wondering when I add more styles in data sheet, the summary in answer does not change automatically...even after I pressed ctrl + shift + enter. is it possible to have the formula auto calculate once the data got changed?

also when I applied the formula on my other file which has same concept, it didn't show me anything...is there anything I should do differently?

bunch of thanks

Season, Category and Style are named ranges and probably fixed ranges:

Change "Refers to:" in Name Manager to =OFFSET(data!$A$2,,,COUNTA(data!$A:$A)-1) for Style

Change Season & Category likewise.

These ranges automatically expand/contract as data is added/deleted

Originally Posted by Tony Valko

Create these dynamic named ranges...

Name: Style
Refers to: =data!$A$2:INDEX(data!$A:$A,MATCH("zzzzz",data!$A:$A))

Name: Season
Refers to: =data!$B$2:INDEX(data!$B:$B,MATCH("zzzzz",data!$B:$B))

Name: Category
Refers to: =data!$C$2:INDEX(data!$C:$C,MATCH("zzzzz",data!$C:$C))

Then...

Data Range

	A	B	C
1	Category	Fall 15	Fall 16
2	adult	2	3
3	kids	1	1

This array formula** entered in B2:

=SUM(IF(FREQUENCY(IF(Category=$A2,IF(Season=B$1,MATCH(Style,Style,0))),ROW(Style)-MIN(ROW(Style))+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy across to C2 then down as needed.

Thanks Tony, I tried the formula with index and just added "if error" in case there is blank. Here is example for style : =IFERROR(data!$A$2:INDEX(data!$A:$A,MATCH("Style",data!$A:$A)),"")
I did all for the rest, but somehow for category after row 6 the cell showed blank only. The rest looks fine. Do you have any idea why this can happen?
For reference, I attached the file and I did your formula in "try formula" tab.
Also not sure why there is always error pop up after I created this formula and press ctrl+shift+enter =SUM(IF(FREQUENCY(IF(Category=$A2,IF(Season=B$1,MATCH(Style,Style,0))),ROW(Style)-MIN(ROW(Style))+1),1))

Gotta check with my IT team here

Thank you so much

Originally Posted by JohnTopley

Season, Category and Style are named ranges and probably fixed ranges:

Change "Refers to:" in Name Manager to =OFFSET(data!$A$2,,,COUNTA(data!$A:$A)-1) for Style

Change Season & Category likewise.

These ranges automatically expand/contract as data is added/deleted

Thank you John,
I can do with this formula and added if error too just to anticipate blank cells.

Sorry just come back Tony, let me double check ...
I am just deepening my excel lately and not so many people I can rely on in my new workplace