Single formula with Multiple Countifs and where data table contains duplicate values in more than on-CodePudding

The excel file contains a structure table. (which I find difficult to work with but I can't alter the file structure, I am just adding a summary tab with various counts from the data table.)

Item	Month
1	2
2	2
2	2
2	3
3	3
3	4

The item number itself can repeat and be repeated within the same month.

I was able to get a count of total number of unique items using this formula.
=SUMPRODUCT((Item_tab[Item]<>"")/COUNTIF(Item_tab[Item],Item_tab[Item]&""))

Looking for how to count the number of unique items within a specific month.?

Example of Desired output.
Edit/Set formula for Month 2 , output should be 2
Edit/Set formula for Month 3 , output should be 2
Edit/Set formula for Month 4 , output should be 1

I cannot figure out how to apply countifs to something that needs to avoid duplicates in multiple columns.

Tried this but returns #VALUE!
=SUMPRODUCT((Item_tab[Item]<>"")/COUNTIFS(Item_tab[Item],Item_tab[Item]&"",'Item'!M:M,6))

The month column was added later and I don't know how why or how to get that column to have the structured table column Header like everything else.
The sheet name is Item
The Table Name is Item_tab
I can find where to rename the table but not the columns. Not sure the variance is causing an issue. The month column is absolutely part of the table in every way that I can see.

CodePudding user response：

You can try the following in cell D2:

=LET(A, A2:A7, B, B2:B7, uxB, UNIQUE(B), cnts, BYROW(uxB, LAMBDA(u, 
 ROWS(UNIQUE(FILTER(A, B=u))))), HSTACK(uxB, cnts))

Here is the output:

If you want to get the result repeated instead of consolidating the result by unique months, you can try the following:

=LET(A, A2:A7, B, B2:B7, uxB, UNIQUE(B), BYROW(B, LAMBDA(u, 
 ROWS(UNIQUE(FILTER(A, B=u))))))

If you don't have such function available, you can try the following formula in D2, it produces the same result as the first formula:

=CHOOSE({1,2}, UNIQUE(B2:B7),
 MMULT(N(MMULT(TRANSPOSE(N(B2:B7=TRANSPOSE(UNIQUE(B2:B7)))),
 N(A2:A7=TRANSPOSE(UNIQUE(A2:A7))))>0), SEQUENCE(ROWS(UNIQUE(A2:A7)),,1,0)))

or using LET for a better reading and maintenance:

=LET(A, A2:A7, B, B2:B7, CHOOSE({1,2}, UNIQUE(B),
 MMULT(N(MMULT(TRANSPOSE(N(B=TRANSPOSE(UNIQUE(B)))),
 N(A=TRANSPOSE(UNIQUE(A))))>0), SEQUENCE(ROWS(UNIQUE(A)),,1,0))))