Hi Paul,
To include additional criteria in the
FREQUENCY function, you can use the
SUMPRODUCT function along with the
FREQUENCY function. Here's how you can modify your formula to include the additional criteria:
- =SUM(IF(FREQUENCY(IF((C:C=A5)*(E:E=5)*(F:F=4),D:D) ,D:D),1))
Let me explain how this formula works:
- The IF function checks for the rows where cells in column C equal A5 (the first criteria), and cells in column E equal 5 and cells in column F equal 4 (the additional criteria).
- The IF function returns an array of values from column D that meet the criteria.
- The FREQUENCY function then calculates the unique values in the array returned by the IF function.
- The IF function then returns an array of 1s and 0s, where 1 represents a unique value and 0 represents a duplicate value.
- Finally, the SUM function adds up the 1s in the array to give you the count of unique values that meet all the criteria.
Note that this is an array formula, so you need to press
Ctrl+Shift+Enter instead of just Enter to enter the formula.