Excel 2007 : Using countifs to count unique values

Hi. Wondering if someone can assist me in writing a formula (I'm hoping to avoid using macros).

As shown below, I have a table with data. I need to calculate unique values (all digits but amount varies daily) in column C for specific "sectors" based on the values in columns A and B. In other words, using data example below, this formula would calculate how many unique values in column C are there for Sun and Day (columns A and B). The result would be 2.

I've tried combining countifs with sumproduct but can't seem to get it to work. Any help is appreciated. Thanks.


A B C
1 sun day 123
2 sun day 344
3 sun day 344
4 moon day 555
5 moon night 666
6 star night 333
7 star night 222

If your data is in say A2:C8, and your input criteria are in E2 (Sun) and F2 (Day), then try:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER