SUMIF Formula Help
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" wrote:
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" wrote:
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
"Rick" wrote:
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
|