Excel 2007 : Looking for Array Formula Help

**Clint5823** · 11-30-2009, 12:23 PM

Hello,
I'm building a spreadsheet to analyze how coupons are being redeemed within a range of postal codes and how revenue is affected between new and repeat customers. One of the formulas I'm using is:
{=SUM(IF('ENTER INFO HERE'!$F$4:$F$15000="T9E",IF('ENTER INFO HERE'!$G$4:$G$15000="F125",'ENTER INFO HERE'!$C$4:$C$15000,0),0))}
Column A= Store #
Column C= New Customers
Column D = Repeat Customers
Column E= Revenue Generated
Column F= Postal Code
Column G= Coupon Code
Column I= True or false value dependant on the value of Column C where if column C=0 it shows a value of "N" and "Y" when column C=1.
According to the information entered into worksheet 'ENTER INFO HERE', I should be getting a value of 5, but I'm getting 0. I'm having the same problem with the other formulas in this area as well. For some reason, the combination of "T9E" and "F125" will not calculate for Column C,D or Column E in this area of the spreadsheet. There is no 'ERROR' OR '#VALUE' message so I'm not sure what is causing the problem. What stumps me is that I'm using similar formulas throughout this worksheet, and they are calculating properly. For instance, I'm also using the following formula: {=SUM(IF('ENTER INFO HERE'!I$4:I$15000="Y",IF('ENTER INFO HERE'!A$4:A$15000=5,IF('ENTER INFO HERE'!G$4:G$15000="F125",'ENTER INFO HERE'!C$4:C$15000,0),0)))}, and this formula is calculating correctly. I'm hoping someone out there will be able to give me a reason why this formula will not calculate any results. HELP!!!!

**DonkeyOte** · 11-30-2009, 12:31 PM

If as implied in your profile you're using XL2007 what does

=SUMIFS('ENTER INFO HERE'!$C$4:$C$15000,'ENTER INFO HERE'!$F$4:$F$15000="T9E",'ENTER INFO HERE'!$G$4:$G$15000="F125")

return ?

The above should be entered as normal (no array required)

If you get 0 then given your other formula which utilises F125 is returning expected output then the finger of blame would point towards column F and T9E... analyse this field in more detail (trailing spaces etc...)

**Clint5823** · 11-30-2009, 01:08 PM

When I copy this formula, it's telling me "The formula you typed contains an error."

**DonkeyOte** · 11-30-2009, 01:10 PM

Might help if I actually provide the correct formula mightn't it...

=SUMIFS('ENTER INFO HERE'!$C$4:$C$15000,'ENTER INFO HERE'!$F$4:$F$15000,"T9E",'ENTER INFO HERE'!$G$4:$G$15000,"F125")

**Clint5823** · 11-30-2009, 01:11 PM

I tried looking in Column F for the error, and that's where it is. Apparently, there was a space entered at the end of the code and this caused the problem. Thanks for your help!!! I was about to loose it!!!

**DonkeyOte** · 11-30-2009, 01:13 PM

OK that's good... if you're using XL2007 you should be using SUMIFS/COUNTIFS in preference to Arrays wherever possible as they are significantly more efficient ... your present arrays are referencing close to 45000 cells each and that I'm afraid is some serious calculation overhead which is liable to have a significant adverse impact on the performance of your model.

**Clint5823** · 11-30-2009, 01:29 PM

The corrected formula works as well. Will using this formula help to accelerate upload time on this spreadsheet?

**Clint5823** · 11-30-2009, 01:32 PM

I see you posted the answer to my question before I read your reply. Thank you so much for the help. I began building this spreadsheet using XL2003 and was told at the time to use the array formula. I didn't realize the sumifs function works the same way.