COUNTIFS with row/column ranges and duplicates

[zyangacp]

Hi, trying to write a formula using data in the first table to populate the second table, counting unique non-zero occurrences. Meaning, criteria for D3 in the second table would be:
- All columns in the first table matching B3 in the second table (so, columns D, E, F, G, H if I'm counting manually)
- All rows in the first table matching D2 in the second table (Row 6 and 15)
- All values >0 (D6, D15, E15, F15, G15, H15)

So, value of D3 should = 6. I'm trying to use a combination of COUNTIFS and INDEX/MATCH but can't seem to get there. Any help appreciated, thanks!!

 

[jasonb75]

Welcome to Mr Excel, @zyangacp

Could you just clarify the expected result please? You mention in the first line that you want to count unique non-zero occurrences, which looks (to me) to give a result of 5 for the example given (D6, D15, E15, F15, G15, H15) because F15 and G15 are identical.

Is that what is required, or am I simply overthinking that part of the question?

 

[jasonb75]

Following up on my reply with a couple of formulas because I'm not going to be online for much longer today.

For a result of 6 as per your example.

=SUMPRODUCT((Sheet1!$D$1:$H$1=$B3)*(Sheet1!$C$2:$C$18=C$2)*(Sheet1!$D$2:$H$18>0))

For a result of 5 as per my interpretation of the unique count requirement.

=SUMPRODUCT(--(FREQUENCY(IF((Sheet1!$D$1:$H$1=$B3)*(Sheet1!$C$2:$C$18=C$2)*(Sheet1!$D$2:$H$18>0),Sheet1!$D$2:$H$18),Sheet1!$D$2:$H$18)>0))

In both formulas, Sheet1 refers to the table in the top screen capture shown in your original post.

Hopefully that will give you what you need.