About Us

Rick

Thanks again your suggestion worked great.

Rick

"Tom Hutchins" wrote:

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
SumIf formula	BarbS via OfficeKB.com	Excel Worksheet Functions	1	November 16th 08 07:36 PM
Nesting a sumproduct formula within a sumif formula.	jerrymcm	Excel Discussion (Misc queries)	2	October 3rd 07 03:35 PM
multiply formula where 1 cell has a (sumif) formula as a result	kcip	Excel Worksheet Functions	1	May 3rd 07 07:41 AM
Help with SUMIF formula Please	parteegolfer	Excel Worksheet Functions	4	April 25th 06 11:47 PM
Is there a MAXIF formula similar to the SUMIF formula?	tlc	Excel Discussion (Misc queries)	2	March 13th 06 08:07 PM

Menu

About Us