Counting unique posts in a table with multiple conditions

[Klas_Britse]

Hi
Im trying to calculate number of rows in a table that fulfil multiple conditions using the COUNTIFS function. My problem is that I only want to count rows holding unique values in one of the columns. I've read about the FREQUENCY function that can be used for counting unique values, but I don't understand if/how I can combine it with COUNTIFS.
Is that possible, or does anyone know a different method to do this?

/Klas

[#2]

Does this help? https://support.microsoft.com/en-us/office/count-unique-values-among-duplicates-8d9a69b3-b867-490e-8...

[#3]

@Klas_Britse 

On which version of Excel you are, does it support FILTER(), UNIQUE() and like?

[#4]

It can be done with frequency, but it can also be done with countifs. Also, I think the latest version of excel has a unique function that may be more efficient in accomplishing this (I don't have it, so can't test it). But, I can show an example using countifs that may be useful if you don't have the newer functions.

Normally, to get a unique count of a single range, say A1:A10, I use:

=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10&""))

Note: the &"" at the end is to prevent DIV/0 errors if the range can have blank cells.

To add some conditional criteria, I replace the "1" with my criteria, enclosed in parentheses. So, to exclude blank cells in A1:A10:

=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))

To add additional criteria for other columns, I add those criteria in the numerator's place (using multiplication for an AND condition), change countif to countifs, and add those ranges to the countifs function (in the same format as the existing arguments for A1:A10). So, if I wanted to add the condition B1:B10=1, then

=SUMPRODUCT((A1:A10<>"")*(B1:B10=1)/COUNTIFS(A1:A10,A1:A10&"",B1:B10,B1:B10&""))

To add OR conditions, then you need to add those conditions in the numerator. But, you need an extra set of parentheses due to order of operations:

=SUMPRODUCT((A1:A10<>"")*((B1:B10=1)+(B1:B10=2))/COUNTIFS(A1:A10,A1:A10&"",B1:B10,B1:B10&""))

If it's possible your OR condition could have more than one condition be TRUE, say you wanted to include rows where column B=1 or column C=2, then you need to test your OR condition for >0 and add C1:C10 to the countifs function. So, it would look like:

=SUMPRODUCT((A1:A10<>"")*((B1:B10=1)+(C1:C10=2)>0)/COUNTIFS(A1:A10,A1:A10&"",B1:B10,B1:B10&"",C1:C10,C1:C10&""))

Without an example of your actual data (excluding any sensitive information) and your excel version (newer functions should make this easier and more efficient), you won't get a response that is tailored to what you're trying to do. If the examples aren't helping, then it would be helpful if you are able to upload an example of your data.

[#5]

Coung - Not really, I found that part myself and it seems a bit too simplified to really help me address my issue.

Sergei – Im using Excel365. FILTER and UNIQUE are available functions (even if they are called FILTER and UNIK in my swedish version). Must try those, haven’t used those formula fun

JMB17 – Thanks for the shared formulas, need to study them closer to understand wh

In general, I think I’ll try first with the FILTER/UNIQUE functions, it’s always nice when things can be solved as easy as possible.

Thank you all very much!
For now, that is. We’ll see if I need more support later… :smiling_face_with_smiling_eyes:

[#6]

This may be helpful. I imagine you would wrap it with count or counta, but I'm not sure what filter returns by default when there are no matches (error, blank, zero)? So, you may have to play with that (filter does have an optional argument to specify what to return if no matches).

https://exceljet.net/formula/unique-values-with-multiple-criteria