count unique values in 3 columns, but countif function not working?

Hello, please help me:

I am attempting to count unique values across 3 columns, Title, Date, Time, in order to count how many distinct sessions have been offered. The title and time columns both have text values, and there are a few blank entries in the data.

I have tried a number of variations (SUM, SUMPRODUCT, COUNTIFS) but with no luck. When I enter the following formula, I get the wrong result:


Worse, when I try to troubleshoot by entering a very simple formula, that is =COUNTIF(A:A, A:A), the answer always comes up wrong (for some reason, it is only counting a small portion of the column. Any idea why?

I've tried to force the issue by reducing the number of rows and only entering =Countif(A2:A11, A2:A11), but the answer is still wrong. This occurs with both the title and time columns.


Would be very grateful for any advice. Thanks!

Hi,

Can you post an example with the data and what your results should be?

Thanks,
Steve

Thanks, yes. Here is a sample of the data that I'm working with.

When I try to use =countif(C2:C14), for example, I get 5, which would be correct if the date were also included, but it isn't. When I try to include the date, that is =COUNTIFS(B2:B14, B2:B14, C2:C14, C2:C14), I get 4 as a result, but the answer should be 5.

And when I try to include the whole data set, I invariably get implausible results.

OK, I see now what my problem is and have solved it with the following formula:

=SUM(IF("CHMA11H3"=$A$2:$A$10000, 1/(COUNTIFS($A$2:$A$10000, "CHMA11H3", $B$2:$B$10000, $B$2:$B$10000, $C$2:$C$10000, $C$2:$C$10000)), 0))