Share via

Adding a parameter to the SUM/IF/FREQUENCY formula to narrow down range?

Anonymous
2010-07-08T17:05:56+00:00

The following formula correctly calculates the number of UNIQUE entries in the range K4:K38.

=SUM(IF(FREQUENCY(K4:K38,K4:K38)>0,1))

I would like to add a parameter so that the range being evaluated is limited to rows where the data in column P is equal to a specific cell reference. The cell reference is a name of a region, so the formula would be evaluating the number of unique records in the sheet specific to that region.

I tried the formula below (entered as an array formula) and the result was not correct:

{=SUM(IF($P$4:$P$38=$B121,FREQUENCY($K$4:$K$38,$K$4:$K$38)>0,1))}

How can I modify the working formula so that I can limit the evaluated rows to rows where P matches my criteria?

Thanks,

David

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2010-07-08T19:53:58+00:00

The following formula correctly calculates the number of UNIQUE entries in the range K4:K38.

=SUM(IF(FREQUENCY(K4:K38,K4:K38)>0,1))

I would like to add a parameter so that the range being evaluated is limited to rows where the data in column P is equal to a specific cell reference. The cell reference is a name of a region, so the formula would be evaluating the number of unique records in the sheet specific to that region.

I tried the formula below (entered as an array formula) and the result was not correct:

{=SUM(IF($P$4:$P$38=$B121,FREQUENCY($K$4:$K$38,$K$4:$K$38)>0,1))}

How can I modify the working formula so that I can limit the evaluated rows to rows where P matches my criteria?

Thanks,

David

 

See if this helps.

A1:A20 = region

B1:B20 = numeric values

Count the unique numeric values by the region that's entered in cell D1.

North 2 North
South 2
South 1
South 5
North 2
South 2
South 3
North 5
North 4
North 1
South 1
North 3
North 4
South 5
North 3
North 5
South 3
North 5
North 2
South 1

Array entered** :

=SUM(IF(FREQUENCY(IF(A1:A20=D1,B1:B20),IF(A1:A20=D1,B1:B20)),1))

Result = 5

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Assumes no empty cells in column B.

--

Biff

Microsoft Excel MVP

Was this answer helpful?

2 people found this answer helpful.
0 comments

12 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2010-07-08T17:21:01+00:00

    Try something like this:

    =SUM(--(FREQUENCY(($P$4:$P$38=$B121)*$K$4:$K$38,($P$4:$P$38=$B121)*$K$4:$K$38)>1))

    If this answer solves your problem, please check, Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers Shane Devenshire

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2010-07-08T18:05:13+00:00

    Yes but even entered as an array, the formula evaluates to #N/A.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2010-07-08T18:01:28+00:00

    Sorry, this is an array formula - to enter it you must press Shift+Ctrl+Enter, not Enter

    If this answer solves your problem, please check, Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers Shane Devenshire

    Was this answer helpful?

    0 comments
  4. Anonymous
    2010-07-08T17:24:00+00:00

    Thanks but this formula evaluates as an error #N/A

    Was this answer helpful?

    0 comments