SUMIF Formula Help

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

I want to sum a column from a different sheet using the date as the condition
for the formula. I need a formula that will sum all the amounts that are
equal to or less than a specific cell E1 (which would be the date). Store 1
is the first sheet and I have 99 sheets for 99 stores. I tried the following
formula but got back -3.0559E-10 as the result. The result should have been
$18,810.90. I am not sure what I am doing wrong. Any help will be greatly
appreciated.

=SUM(IF(('Store #1'!B1:B73<=E1),'Store #1'!E1:E73))


Store # Period Step Rent
1 04/01/2008 962.70
1 05/01/2008 962.70
1 06/01/2008 962.70
1 07/01/2008 962.70
1 08/01/2008 962.70
1 09/01/2008 962.70
1 10/01/2008 962.70
1 11/01/2008 962.70
1 12/01/2008 962.70
1 01/01/2009 962.70
1 02/01/2009 962.70
1 03/01/2009 962.70
1 04/01/2009 587.77
1 05/01/2009 587.77
1 06/01/2009 587.77
1 07/01/2009 587.77
1 08/01/2009 587.77
1 09/01/2009 587.77
1 10/01/2009 587.77
1 11/01/2009 587.77
1 12/01/2009 587.77
1 01/01/2010 587.77
1 02/01/2010 587.77
1 03/01/2010 587.77
1 04/01/2010 205.34
1 05/01/2010 205.34
1 06/01/2010 205.34
1 07/01/2010 205.34
1 08/01/2010 205.34
1 09/01/2010 205.34
1 10/01/2010 205.34
1 11/01/2010 205.34
1 12/01/2010 205.34
 
Your formula should work but needs to be entered as an array formula. An
array formula must be entered by pressing CTRL+Shift+Enter and not just
Enter. If you do it correctly then Excel will put curly brackets around the
formula { }. You can't type these yourself. If you edit the formula you must
enter it again with CTRL+Shift+Enter.

Here is another formula, using SUMPRODUCT, which gives the same results but
does not have to array-entered:
=SUMPRODUCT(--('Store #1'!B1:B73<=E1),'Store #1'!E1:E73)

In versions before Excel 2007, you can't use whole columns with SUMPRODUCT.

Hope this helps,

Hutch
 
Is it possible to have it look at the entire column and not just the specific
range? I tried changing your formula to B:B instead of B:B73 but i get an
error. Any ideas?
 
You can't use whole columns with SUMPRODUCT unless you are using XL2007. You
can use whole columns with your original formula:
=SUM(IF(('Store #1'!B:B<=E1),'Store #1'!E:E))
which must be array-entered.

Or, you could use this SUMIF version:
=SUMIF('Store #1'!B:B,"<="&E1,'Store #1'!E:E)

Hutch
 
Thanks again your suggestion worked great.

Rick

Tom Hutchins said:
You can't use whole columns with SUMPRODUCT unless you are using XL2007. You
can use whole columns with your original formula:
=SUM(IF(('Store #1'!B:B<=E1),'Store #1'!E:E))
which must be array-entered.

Or, you could use this SUMIF version:
=SUMIF('Store #1'!B:B,"<="&E1,'Store #1'!E:E)

Hutch
Click to expand...
 
Back
Top