SanFelippo
Board Regular
- Joined
- Apr 4, 2017
- Messages
- 124
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.
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.
Last edited: