Mar 04 2021 06:25 AM
Mar 04 2021 06:25 AM
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?
Mar 04 2021 07:05 AM
On which version of Excel you are, does it support FILTER(), UNIQUE() and like?
Mar 04 2021 07:48 AM - edited Mar 04 2021 07:50 AM
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:
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:
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
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:
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:
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.
Mar 04 2021 08:54 AM
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…
Mar 04 2021 09:29 AM