(Array) Formula not returning expected results

Question

I have the following two formulas, which were expected to produce identical results. The one marked "Original" is verified to produce the expected results. It actually calls source data 4 different times, so I decided to consolidate the formula into the one marked "New" to speed up the workbook. Unfortunately, it isn't doing what I thought it would.

The purpose of this formula is to scan a very large sheet of raw data, and sum monthly values for rows that meet specific criteria.

Original: just here for reference, I don't expect anyone to decipher it

{=(ROUND(SUM(IF('FPA Warehouse'!$A$1:$A$5000=AllLocData!I$3,IF('FPA Warehouse'!$D$1:$D$5000="72_LABOR_TOT",IF(RIGHT('FPA Warehouse'!$C$1:$C$5000,3)="D04",'FPA Warehouse'!$F$1:$Q$5000,0),0),0)),2)+ROUND(SUM(IF('FPA Warehouse'!$A$1:$A$5000=AllLocData!I$3,IF('FPA Warehouse'!$D$1:$D$5000="72_LABOR_TOT",IF(RIGHT('FPA Warehouse'!$C$1:$C$5000,3)="D07",'FPA Warehouse'!$F$1:$Q$5000,0),0),0)),2))-(ROUND(SUM(IF('FPA Warehouse'!$A$1:$A$5000=AllLocData!I$3,IF('FPA Warehouse'!$D$1:$D$5000="729545",IF(RIGHT('FPA Warehouse'!$C$1:$C$5000,3)="D04",'FPA Warehouse'!$F$1:$Q$5000,0),0),0)),2)+ROUND(SUM(IF('FPA Warehouse'!$A$1:$A$5000=AllLocData!I$3,IF('FPA Warehouse'!$D$1:$D$5000="729545",IF(RIGHT('FPA Warehouse'!$C$1:$C$5000,3)="D07",'FPA Warehouse'!$F$1:$Q$5000,0),0),0)),2))}

New:

{=(ROUND(SUM(IF('FPA Warehouse'!$A$1:$A$5000=AllLocData!I$3,IF(OR(RIGHT('FPA Warehouse'!$C$1:$C$5000,3)="D04",RIGHT('FPA Warehouse'!$C$1:$C$5000,3)="D07"),IF('FPA Warehouse'!$D$1:$D$5000="72_LABOR_TOT",'FPA Warehouse'!$F$1:$Q$5000,IF('FPA Warehouse'!$D$1:$D$5000="729545",-('FPA Warehouse'!$F$1:$Q$5000),0)),0),0)),2))}

When I'm investigating non-working formulas, I often paste them into Word so I can reformat and indent, to more easily trace the logic (in this case, the pairing of the True and False parameters of each embedded IF statement. Vertically aligned rows are the True/False statements for the IF above them.

(I removed the ROUND statement, since it wasn't relevant here). And yes, I am subtracting the values associated with 729545.

SUM(

IF('FPA Warehouse'!$A$1:$A$5000=AllLocData!I$3,

     IF(OR(RIGHT('FPA Warehouse'!$C$1:$C$5000,3)="D04",RIGHT('FPA Warehouse'!$C$1:$C$5000,3)="D07"),

          IF('FPA Warehouse'!$D$1:$D$5000="72_LABOR_TOT",

               'FPA Warehouse'!$F$1:$Q$5000,

               IF('FPA Warehouse'!$D$1:$D$5000="729545",

                    -('FPA Warehouse'!$F$1:$Q$5000)

                    ,0))

          ,0)

     ,0)

)                                               

So according to my thinking, all of the FALSE parameters are zero, and I should only get the matching rows/values. What is actually happening is that it is also pulling in the rows where either line 4 or 6 are true (="72_LABOR_TOT", and ="729545") where row 3 is actually FALSE [e.g, RIGHT('FPA Warehouse'!$C$1:$C$5000,3) is not D04 or D07].

Here is some sample data; if you paste this into a blank sheet and name it "FPA Warehouse", then paste in the 'New' formula (Ctrl-Shft-Enter because it is an array formula) in a blank cell and change the "AllLocData!I$3" reference at the beginning of the "new" formula to a cell you can put the number 555 in. I think that should replicate my situation. The original formula adds up the total for the lines that have BOLD in three columns. The new formula is including the lines with ITALICS in column D.

The expected result is 620,469. Any idea why I'm getting 639,390 (the inclusion of the two italics rows) instead?

Thank you!!

edit: apparently the table is wrapping within cells, the third column contains "LocName D04", and color was not maintained even though it was visible while I was creating the post :( so edited to be bold and italics instead

555	(blank)	LocName  D04	72_LABOR_TOT	(blank)	61,565	61,239	71,286	75,653	66,737	81,122	67,042	68,882	61,457	-	-	-
555	(blank)	LocName  D04	723533	(blank)	5,217	5,090	5,090	5,182	5,182	5,182	8,182	1,943	4,917	-	-	-
555	(blank)	LocName  D04	729540	(blank)	-	-	-	(50)	-	-	-	-	-	-	-	-
555	(blank)	LocName  D04	729545	(blank)	(113)	-	-	-	-	-	(450)	(4,923)	-	-	-	-
555	(blank)	LocName  D04	72_OTH	(blank)	19,551	21,737	21,006	25,978	30,461	33,202	39,384	11,229	19,046	-	-	-
555	(blank)	LocName  D04	72_W	(blank)	81,117	82,976	92,292	101,631	97,198	114,324	106,426	80,112	80,504	-	-	-
555	(blank)	LocName  D06	72_OTH	(blank)	892	253	-	9	298	783	641	660	799	-	-	-
555	(blank)	LocName  D06	72_W	(blank)	892	253	-	9	298	783	641	660	799	-	-	-
555	(blank)	ABC LocName	72_LABOR_TOT	(blank)	1,891	1,854	2,250	2,102	2,294	2,112	2,119	2,153	2,148	-	-	-
555	(blank)	ABC LocName	729545	(blank)	-	-	-	-	-	-	-	-	-	-	-	-
555	(blank)	ABC LocName	72_OTH	(blank)	-	52	160	19	-	-	-	17	-	-	-	-
555	(blank)	ABC LocName	72_W	(blank)	1,891	1,905	2,410	2,120	2,294	2,112	2,119	2,170	2,148	-	-	-

Answer 1

As others have pointed out, the problem in your second formula is the inappropriate use of the OR function.  The OR function does not return an array of values; rather it returns a single TRUE or FALSE if ANY of the values tested evaluate to TRUE.

But in addition, your original formula does not seem to return a correct result.  If I SUM the lines represented by your bolded text, I get a result of 609,497.

Assuming you have made an error in both formulas, I suggest the following which does return 609,497

First of all, I used Defined Names as it makes it simpler for me to follow things.

If you have labels at the top of your table, you need to avoid including any rows that might contain text in the RangeToSum.  So you may need to change the NAME definitions from what I have below.

In any event, I defined the following NAMES:

ColA                   ='FPA Warehouse'!$A$1:$A$15    

ColC                  ='FPA Warehouse'!$C$1:$C$15    

ColD                  ='FPA Warehouse'!$D$1:$D$15    

RangeToSum  ='FPA Warehouse'!$F$1:$Q$15

I then used this formula, normally entered:

=SUMPRODUCT((ColA=555)*

(ISNUMBER(FIND("D04",ColC))+ISNUMBER(FIND("D07",ColC))) *

((ColD = "72_LABOR_TOT")+(ColD="729545")) *

RangeToSum)

Any of the "variables" can be replaced by cell references containing those variables.

Note that I used FIND for the LocName since I noted that in some cases the Name is at the end of the string, and in other cases at the start of the string; it'll make things simpler if you want to pick up, for example "ABC".

Answer 2

In the style of formula you are attempting to use an OR() statement within the progression of IF() statements to provide further consolidation of the formula but OR()s do not work in this style of array formula. You can try the following instead,

=ROUND(SUMPRODUCT((FPAWH!$A$1:$A$4988=$C$1)*((RIGHT(FPAWH!$C$1:$C$4988,3)="D04")+(RIGHT(FPAWH!$C$1:$C$4988,3)="D07"))*(((FPAWH!$D$1:$D$4988="72_LABOR_TOT")*(FPAWH!$F$1:$Q$4988))-((FPAWH!$D$1:$D$4988="729545")*(FPAWH!$F$1:$Q$4988)))),2)

The plus sign is the equivalent of an OR() statement when used against the same cell range within a SUMPRODUCT() formula. The minus reverses the sign of the secondary criteria. No need for CSE↵ on this formula.

Edit: BTW, I couldn't get that formula working with your sample data and your cell references due to cell content in rows 1:3. You can use the following to test,

=ROUND(SUMPRODUCT((FPAWH!$A$4:$A$15=$C$1)*((RIGHT(FPAWH!$C$4:$C$15,3)="D04")+(RIGHT(FPAWH!$C$4:$C$15,3)="D07"))*(((FPAWH!$D$4:$D$15="72_LABOR_TOT")*(FPAWH!$F$4:$Q$15))-((FPAWH!$D$4:$D$15="729545")*(FPAWH!$F$4:$Q$15)))),2)

Answer 3

Hopefully this link will work:

https://skydrive.live.com/view.aspx?cid=2004B197FD5A734A&resid=2004B197FD5A734A%21140

I did try your suggestion, but was unable to get it to return results; at least one cell calculated as an error, thereby throwing causing the overall formula to return an error.

Many thanks,

Keith

Answer 4

I'm not sure where you are getting either 620,469 or 639,390 for an answer. I suppose there is data that we cannot see to the right. The data that did survive your copy and paste was pretty wonky, with leading/trailing spaces, double spaces between words, and numbers unsure of whether to become a real number or a textual representation of a number. After attempting to discern the logic behind your formula, I came up with the following that seems to survive most distortions of the data that I threw at it.

=ROUND(SUMPRODUCT(('FPA Warehouse'!$A$1:$A$5000=AllLocData!$I$3)*(ISNUMBER(SEARCH("D04",'FPA Warehouse'!$C$1:$C$5000))+ISNUMBER(SEARCH("D07",'FPA Warehouse'!$C$1:$C$5000)))*(((TRIM('FPA Warehouse'!$D$1:$D$5000)="72_LABOR_TOT")*('FPA Warehouse'!$F$1:$Q$5000))+((('FPA Warehouse'!$D$1:$D$5000=729545)+(TRIM('FPA Warehouse'!$D$1:$D$5000)="729545"))*(0-'FPA Warehouse'!$F$1:$Q$5000)))),2)

That is not entered as an array formula.

Note that I've only used the ROUND() function once. I would suppose that this could lead to a 1¢ difference against using 3 ROUND()s on occasion.

Answer 5

Keith wrote:

SUM(

IF('FPA Warehouse'!$A$1:$A$5000=AllLocData!I$3,

     IF(OR(RIGHT('FPA Warehouse'!$C$1:$C$5000,3)="D04",RIGHT('FPA Warehouse'!$C$1:$C$5000,3)="D07"),

          IF('FPA Warehouse'!$D$1:$D$5000="72_LABOR_TOT",

               'FPA Warehouse'!$F$1:$Q$5000,

               IF('FPA Warehouse'!$D$1:$D$5000="729545",

                    -('FPA Warehouse'!$F$1:$Q$5000)

                    ,0))

          ,0)

     ,0)

)                                               

[....]

The expected result is 620,469. Any idea why I'm getting 639,390 (the inclusion of the two red rows) instead?

To be honest, I did not try to digest everything you wrote.  Some important information got lost in translation; for example, I see no red rows.

However, I suspect the OR() expression is not working as you intended.  If your intention is a row-by-row "or" of the two conditions, you need to write that as follows in an array formula:

SUM( IF('FPA Warehouse'!$A$1:$A$5000=AllLocData!I$3,

IF( (RIGHT('FPA Warehouse'!$C$1:$C$5000,3)="D04")

   +(RIGHT('FPA Warehouse'!$C$1:$C$5000,3)="D07")>0,

[...etc...]

Technically, ">0" is not needed for this particular "or" operation because the two conditions are mutually-exclusive.  But it's a good habit to get into in case you use the same paradigm in non-mutually-exclusive situation.

If that does not solve your problem, I suggest that you upload an example Excel file (devoid of proprietary and private data) to a file-sharing web site, and post the URL of the uploaded file here.  The following is a list of some free file-sharing web sites:

Box.Net: http://www.box.net/files

Windows Live Skydrive: http://skydrive.live.com

MediaFire: http://www.mediafire.com

FileFactory: http://www.filefactory.com

FileSavr: http://www.filesavr.com

FileDropper: http://www.filedropper.com

RapidShare: http://www.rapidshare.com