Help with a Frequency array formula

[SanFelippo]

Hello,

So right now I have this formula which is working correctly:

{=SUM(IF(FREQUENCY(IF(Feb!$N:$N="ValidError",Feb!$D:$D),Feb!$D:$D),1))}

This array formula is essentially going and looking at a tab named “Feb” thathas a bunch of ID numbers (there are duplicates of the same ID number inmultiple rows sometimes because one ID number can have more than one errorassociated with it, and each error gets its own row). The formula checks to seeif the value in the N column for each row is “Valid Error” If that condition ismet, it is included in the array. It then returns a unique count of ID numbersthat have an error associated with them.


For example, say that the following was the list of IDnumbers on the Feb tab:

1111 ValidError
1111 ValidError
1111 ValidError
2222 None
3333 ValidError
3333 ValidError
4444 ValidError

The formula above would return the number 3, as there are3 application numbers that have a “Valid Error” associated with them.
What I am trying to do is add an additional conditioninside the inner most IF. In order to be counted, I want both Column N to have “ValidError” and then also I want Column F to either be “Retail”, “Wholesale”, or “Correspondent”in order to be counted.

I tried using the following formula but I got a #Value ! Error:

{=SUM(IF(FREQUENCY(IF(AND(Jan!$N:$N="ValidError",OR(Jan!$F:$F="Retail",Jan!$F:$F="Wholesale",Jan!$F:$F="Correspondent")),Jan!$D:$D),Jan!$D:$D),1))}
Any help would be appreciated.

 

[Eric W]

Using ANDs and ORs in array formulas usually doesn't work, since they tend to evaluate when you don't want them to. Try:

=SUM(IF(FREQUENCY(IF(Feb!$N:$N="ValidError",IF(ISNUMBER(MATCH(Feb!$F:$F,{"Retail","Wholesale","Correspondent"},0)),Feb!$D:$D)),Feb!$D:$D),1))

Even better:

=SUM(IF(FREQUENCY(IF(Feb!$N1:$N100="ValidError",IF(ISNUMBER(MATCH(Feb!$F1:$F100,{"Retail","Wholesale","Correspondent"},0)),Feb!$D1:$D100)),Feb!$D1:$D100),1))

Try not to use whole column references with this type of formula, it can really slow down your sheet. Instead, figure out the maximum row you'll get to and use that.

Hope this helps!

 

[Marcelo Branco]

Thoughts
1. To use an OR condition in an array formula you should add the conditions
IF((Jan!F:F="Retail")+(Jan!F:F="Wholesale")+(Jan!F:F="Correspondent"),...

2. As sometimes the list of values can contain many values I always prefer to create a named range, say List, which contains the values and use
IF(ISNUMBER(MATCH(Jan!F:F,List,0)),....

3. For the sake of performance use definite ranges in array formulas rather than references to entire columns.

That said, i suggest
1. Create a named range, List, that contains
Retail
Wholesale
Correspondent

2. Use this formula
=SUM(IF(FREQUENCY(IF(Jan!N2:N100="ValidError",IF(ISNUMBER(MATCH(Jan!F2:F100,List,0)),Jan!D2:D100)),Jan!D2:D100),1))

3. Adjust the ranges in the above formula to the maximum likely number of rows

M.

 

[SanFelippo]

Both of your solutions work perfectly, and now I am smarter for having seem them and used them. Amazing the things excel can do. I will see if I can at least set the range to something I know will always be bigger than the dataset that gets dropped in, as the number of IDs that come through will always be different.

Thanks a ton for your help!

 

[Eric W]

Glad we could help!