Excel Array formula help - an "OR" situation. 1

[creeker69]

I hesitate to ask this question, because I am almost embarassed that I am having trouble with this array formula. I searched through the previous postings, but cannot find a parallel situation for mine. Most all of my array formulas seek results of all the true conditions and are rather easy.

I have pasted the formula below. First, let me explain the data. I am very limited on data fields that our mainframe will export, but it is accurate and consistent. On this spreadsheet, I only have data from 2007 thru 2009 (3 years almost) of units sold.

DATA:

$C12 = 501.00, $D12 = 1,000.00 (to get a range of Gross Profit, and there are about 14 ranges.

$F$1 is a Validation Cell with a list. The list is 2007, 2008, 2009 and the word "ALL".


When the User selects ANY of the YEARS from the Validation Cell dropdown, It works perfect. But what I need is a way for it to work on "ALL". To correctly select "ALL" the data and give the correct answers, all I need to do is eliminate is " IF(YEARSOLD=$F$1,GROSS) " from the formula and then the formula works on ALL the data (from the "RED" store in this case).

I know (or I think I know) I need an IF, THEN, ELSE type of formula but just can't seem to get the syntax of it correct. I have tried many variations and can get close but when I select "ALL" from the dropdown I get a 0 (numeric zero).

The original formula, which work when any of the YEARS are selected from the dropdown validation list is:

=SUM(IF(STORE="RED",IF(GROSS>=$C12,IF(GROSS<=$D12,IF(SOURCE="AUC",IF(SALETYPE="retail",IF(YEARSOLD=$F$1,GROSS)))))))

This is the closest I have come to an answer:

=IF((YEARSOLD="ALL"),(SUM(IF(STORE="RED",IF(GROSS>=$C9,IF(GROSS<=$D9,IF(SOURCE="AUC",IF(SALETYPE="retail",1))))))),((SUM(IF(STORE="RED",IF(GROSS>=$C9,IF(GROSS<=$D9,IF(SOURCE="AUC",IF(SALETYPE="retail",IF(YEARSOLD=$F$1,1)))))))))) but it will still not work on "ALL". It does still grab the YEARS properly, however.

What am I missing?

I truly appreciate your help (and your patience) with this and thank you all in advance.


Creeker69

 

[SkipVought]

Hi,

If it were me, I'd be using the SUMPRODUCT function...
[tt]
If(YEARSOLD="ALL",
SUMPRODUCT((STORE="RED")*(GROSS<$D9)*(SOURCE="AUC")*(SALETYPE="retail")),
SUMPRODUCT((STORE="RED")*(GROSS<$D9)*(SOURCE="AUC")*(SALETYPE="retail")*(YEARSOLD=$F$1))
)
[/tt]
Don't need to enter as an array. It will COUNT the occurrences according to your criteria.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[creeker69]

Skip,

Thanks for the speedy reply. As usual, the minute after I posted, I got it. I need to not only count the occurences but also add up the total gross profit and that is why I am using an array formula. But, I am always trying to learn from you guys and, frankly, though I love the power of the array formulas, I would like to see if there is another way. This formula now works.

I have a cell with this formula and another where I substitute the word "GROSS" with the number "1" to count the occurences as well.

I should not have used "YEARSOLD" at the beginning of my IF but instead referred to the Validation cell ($F$1).

This now works:

{=IF(($F$1="ALL"),(SUM(IF(STORE="RED",IF(GROSS>=$C9,IF(GROSS<=$D9,IF(SOURCE="AUC",IF(SALETYPE="retail",1))))))),((SUM(IF(STORE="RED",IF(GROSS>=$C9,IF(GROSS<=$D9,IF(SOURCE="AUC",IF(SALETYPE="retail",IF(YEARSOLD=$F$1,1))))))))))}

Thanks again,

Creeker69

 

[SkipVought]

and that is why I am using an array formula.

This Sums GROSS
[tt]
If($F$1="ALL",
SUMPRODUCT((STORE="RED")*(GROSS<$D9)*(SOURCE="AUC")*(SALETYPE="retail")*(GROSS)),
SUMPRODUCT((STORE="RED")*(GROSS<$D9)*(SOURCE="AUC")*(SALETYPE="retail")*(GROSS)*(YEARSOLD=$F$1))
)
[/tt]
Either way, but I prefer the SIMPLICITY. Nested IF's get dicy.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[creeker69]

I totally agree with your logic! And, Thank you for the definitely easier to read and understand formula. I am changing this worksheet to use your formula.

Just one typo in yours and that is *(GROSS) should be ,(GROSS) . But, I knew it was a typo.

Thank you for being so kind and patient.


Creeker69

 

[SkipVought]

No typo!!!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

No typo: let me clarify.

The original design of SUMPRODUCT is based in array math. We all learned to do matrix multiplication in school.

This technique is an adaption, using a sightly different approch.

Example

[tt]
STORE SOURCE GROSS
RED AUC 2000
DER AUC 1000
RED AUC 4000
RED CUA 3000
RED AUC 500
[/tt]

[tt]
=SUMPRODUCT((STORE="RED")*(SOURCE="AUC")*(GROSS<4000)*(GROSS))
[/tt]
returns 2500

What Happens as it SUMS the PRODUCTS
[tt]
STORE SOURCE GROSS GROSS
TRUE *TRUE *TRUE * 2000 = 2000
FALSE*TRUE *TRUE * 1000 = 0
TRUE *TRUE *FALSE* 5000 = 0
TRUE *FALSE *TRUE * 3000 = 0
TRUE *TRUE *TRUE * 500 = 500
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bluedragon2]

Might want to read faq68-4725

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...