Forum Discussion
Counting unique posts in a table with multiple conditions
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.
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 funJMB17 – 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… 😊- 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
