Modify a Formula to Include an OR statement

[SanFelippo]

Hey All,
I thought this was an easy tweak, but apparently not so, atleast for me. Must have something to do with how excel is dealing with thearray formula, so hopefully someone else has a better understanding.
This is my current formula:
{=SUM(IF(FREQUENCY(IF(Jan!$N:$N="ValidError",Jan!$D:$D),Jan!$D:$D),1))}

I am trying to make this tweak:
{=SUM(IF(FREQUENCY(IF(OR(Jan!$N:$N="ValidError", Jan!$N:$N=“CBB Identified Error”),Jan!$D:$D),Jan!$D:$D),1))}.

So essentially I want it to count if column N containseither of this two phrases. I am getting a #NAME? Error.
Any ideas?

 

[Fluff]

Try
=SUM(IF(FREQUENCY((IF(Jan!$N:$N="ValidError",Jan!$D:$D))+(IF(Jan!$N:$N="CBB Identified Error",Jan!$D:$D)),Jan!$D:$D),1))

You are getting the #Name ? error as the quotes are wrong, they should be "

 

[SanFelippo]

Sorry for the late reply. This doesn't appear to be working. The original formula, which was {=SUM(IF(FREQUENCY(IF(Jan!$N:$N="Valid Error",Jan!$D:$D),Jan!$D:$D),1))}, gives me a count of 35. The new formula you gave me is only giving me a count of 1.

Since I want to be in a sense adding an OR statement to look for either "Valid Error" or "CBB Identified Error", if anything the count should go up.

 

[Fluff]

Can you post some sample data?
Also with array formulae you should avoid using whole columns.

 

[FormR]

Hi, here is an alternative you could try.

=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH(Jan!$N1:$N100,{"Valid Error","CBB Identified Error"},0)),Jan!$D1:$D100),Jan!$D1:$D100),1))

Extend the end rows to sensible maximums or consider using dynamic named ranges or tables along with structured references.

 

[SanFelippo]

Sample Data is below. To give you an idea of how it works. This is original formula I had in there:



{=SUM(IF(FREQUENCY(IF(Jan!$N:$N="Valid Error",Jan!$D:$D),Jan!$D:$D),1))}


this formula for the sample data below gives me a count of 9, as there are 9 unique Loans that had a "Valid Error." I set up the formula this way because there are duplicate Loan Numbers. It also has to reference the whole column because I never know how many rows of data will be dropped in.


The new formula I am trying to come up with would give me a final count of 12, as there are 12 unique applications that had either a "Valid Error" or a "CBB Identified Error."


[TABLE="width: 217"]
<colgroup><col width="159" style="width: 119pt; mso-width-source: userset; mso-width-alt: 5814;"><colgroup><col width="131" style="width: 98pt; mso-width-source: userset; mso-width-alt: 4790;"><tbody>[TR]
[TD="width: 159, bgcolor: #D9D9D9"]Loan Number[/TD]
[TD="width: 131, bgcolor: #D9D9D9"]CCS Response[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]Valid Error[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]Valid Error[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]CBB Identified Error
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]CBB Identified Error[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]8[/TD]
[TD="bgcolor: transparent"]OK[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]9[/TD]
[TD="bgcolor: transparent"]OK[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10[/TD]
[TD="bgcolor: transparent"]OK[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]15[/TD]
[TD="bgcolor: transparent"]Observation[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]16[/TD]
[TD="bgcolor: transparent"]CBB Identified Error[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]17[/TD]
[TD="bgcolor: transparent"]OK[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]18[/TD]
[TD="bgcolor: transparent"]Valid Error[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]18[/TD]
[TD="bgcolor: transparent"]Valid Error[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]28[/TD]
[TD="bgcolor: transparent"]OK[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]28[/TD]
[TD="bgcolor: transparent"]Valid Error[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]34[/TD]
[TD="bgcolor: transparent"]Valid Error[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]35[/TD]
[TD="bgcolor: transparent"]OK[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]38[/TD]
[TD="bgcolor: transparent"]Observation[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]39[/TD]
[TD="bgcolor: transparent"]Valid Error[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]40[/TD]
[TD="bgcolor: transparent"]Valid Error[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]40[/TD]
[TD="bgcolor: transparent"]Valid Error[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]40[/TD]
[TD="bgcolor: transparent"]Valid Error[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]44[/TD]
[TD="bgcolor: transparent"]OK[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]47[/TD]
[TD="bgcolor: transparent"]Valid Error[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]48[/TD]
[TD="bgcolor: transparent"]OK[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]52[/TD]
[TD="bgcolor: transparent"]CBB Identified Error[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]56[/TD]
[TD="bgcolor: transparent"]OK[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]57[/TD]
[TD="bgcolor: transparent"]OK[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]58[/TD]
[TD="bgcolor: transparent"]OK[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]25[/TD]
[TD="bgcolor: transparent"]OK[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]Valid Error[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]CBB Identified Error[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]CBB Identified Error[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]CBB Identified Error[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]Valid Error[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]Valid Error[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]Valid Error[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]Valid Error[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]Valid Error[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Have you tried FormR's suggestion in post#5, it works for me.

 

[SanFelippo]

The code in Post#5 works just fine. I can definitely work in dynamic named ranges to account for the changing number of rows

Thank you both!