SUMIF Formula Help

[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

 

[Huber57]

Rick,

Try this.

=SUMPRODUCT(--(B2:B33>E1),C2:C33)

 

[Tom Hutchins]

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

 

[T. Valko]

Try this...

=SUMIF('Store #1'!B1:B73,"<="&E1,'Store #1'!E1:E73)

 

[Rick]

Your formula worked great. Thanks for your help.

Rick

 

[Rick]

Thanks for you help.

Rick

Rick,

Try this.

=SUMPRODUCT(--(B2:B33>E1),C2:C33)

 

[Rick]

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?

 

[Tom Hutchins]

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

 

[Rick]

Thanks again your suggestion worked great.

Rick

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