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. 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!!!!

"SA5823" wrote:
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))}
[....]
I should be getting a value of 5, but I'm getting 0

First, double-check that you entered the formula as an array formula. I
suspect you did, since you included the curly braces in your posting. But
it doesn't hurt to check. It if is not an array formula, press F2 followed
by ctrl+shift+Enter instead of just Enter.

Second, be sure that you have Automatic calculation selected under Tools
Options Calculation (in Excel 2003). If you purposely have Manual
calculation set, you will need to press F9 or ctrl+alt+F9 each time you want
to recalculate.

Finally, try using the Evaluate Formula feature under Tools Formula
Auditing (in Excel 2003) to see how the formula is evaluated step-by-step.
That might reveal the source of the unexpected resulted -- perhaps an error
in your assumptions.


----- original message -----

"SA5823" wrote in message
...
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. 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!!!!

Hi,
try

=sumproduct(('ENTER INFO HERE'!I$4:I$15000="Y"),('ENTER INFO
HERE'!A$4:A$15000=5),('ENTER INFO HERE'!G$4:G$15000=F125),'ENTER INFO
HERE'!C$4:C$15000)

"SA5823" wrote:

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. 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!!!!