Solved: If statement in an Array Formula

[JimS]

In the attachment is a file (with most of the data removed).

I have 2 Array Formulas that I need to add an IF Statement to (see the Summary worksheet for the details).

I have tried to add the IF Statement to both of them but cannot come up with the correct syntax to make them work.

Any ideas?

Thanks...

JimS

[Bob Phillips]

=SUM(--(FREQUENCY(IF((SUMIF(OppN,OppN,Split)>249999)*(District={"Arizona","Irvine","San Diego"}),MATCH(OppN,OppN,0)),ROW(INDIRECT("1:"&ROWS(OppN))))>0))

and

=SUM(IF((SUMIF(OppN,OppN,Split)>249999)*(District={"Arizona","El Segundo","Irvine","San Diego"}),Split))

[JimS]

You are the man...

Can these be changed so that they will only sum >249999 but <500000

[Bob Phillips]

=SUM(--(FREQUENCY(IF((SUMIF(OppN,OppN,Split)>=250000)*(SUMIF(OppN,OppN,Split)<500000)*(District={"Arizona","Irvine" ,"San Diego"}),MATCH(OppN,OppN,0)),ROW(INDIRECT("1:"&ROWS(OppN))))>0))

and

=SUM(IF((SUMIF(OppN,OppN,Split)>=250000)*(SUMIF(OppN,OppN,Split)<500000)*(District={"Arizona","El Segundo","Irvine","San Diego"}),Split))

[JimS]

XLD,

You are amazing... Thank you so much.

Do you see anything wrong with this one?

=IF(Cat={"HARDWARE","SOFTWARE"},SUMPRODUCT((District="Chicago")*(Split)))

[Bob Phillips]

Yeah, you can't use an array constant in IF like that, you would either have to embed a MATCH, or use OR. If Cat is multiple cells, definitely MATCH is the way to go.

[JimS]

I think this is my last formula that I need to resolve (I hope)...

It works if with Cat="Hardware" but doesn't like Cat={"Hardware","Software")

=SUM(--(FREQUENCY(IF((SUMIF(OppN,OppN,Split)>=50)*(SUMIF(OppN,OppN,Split)<500000)*(District={"Chicago","Chicago 2","Chicago 3","Minnesota"})*(Cat={"Hardware","Software"}),MATCH(OppN,OppN,0)),ROW(INDIRECT("1:"&ROWS(OppN))))>0))

Can you help?

[Bob Phillips]

Can you post this workbook?

[JimS]

Attached.

All the formulas worked until I added the (Cat={"HARDWARE","SOFTWARE"}) to try and limit them.

Thanks again for all your help.

[Bob Phillips]

Very odd situation here which I had forgotten all about.

The problem is that you are comparing against differently sized array, and the extra items throw out the #N/A.

You can get over it by using ,(Cat={"HARDWARE","SOFTWARE","HARDWARE","SOFTWARE"}) instead of (Cat={"HARDWARE","SOFTWARE"})

[Bob Phillips]

Actually, that doesn't work, because it pairs off the array constants, so it only matches

District={"Chicago", and Cat={"HARDWARE"
and
District={"Chicago 2", Cat={"SOFTWARE"

and misses say District={"Chicago", Cat={"SOFTWARE"

[JimS]

That explains why it doesn'twork.

Do you know of a different way to do this?

[Bob Phillips]

I need to think about this Jim.

[Bob Phillips]

Jim,

I haven't tested it properly but this should work

=SUM(--(FREQUENCY(IF((SUMIF(OppN,OppN,Split)>=50)*(SUMIF(OppN,OppN,Split)<500000)
*((District="Chicago")+(District="Chicago 2")+(District="Chicago 3")+(District="Minnesota"))
*((Cat="Hardware")+(Cat="Software")),
MATCH(OppN,OppN,0)),ROW(INDI RECT("1:"&ROWS(OppN))))>0))