View Full Version : Solved: If statement in an Array Formula
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
11-05-2010, 06:20 AM
=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))
You are the man...
Can these be changed so that they will only sum >249999 but <500000
Bob Phillips
11-05-2010, 06:53 AM
=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))
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
11-05-2010, 07:35 AM
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.
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
11-05-2010, 10:58 AM
Can you post this workbook?
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
11-06-2010, 04:06 AM
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
11-06-2010, 04:10 AM
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"
That explains why it doesn'twork.
Do you know of a different way to do this?
Bob Phillips
11-06-2010, 11:10 AM
I need to think about this Jim.
Bob Phillips
11-06-2010, 02:23 PM
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))
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.