#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 334
Default SUMIF Formula Help

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

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 74
Default SUMIF Formula Help

Rick,

Try this.

=SUMPRODUCT(--(B2:B33E1),C2:C33)

"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

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,069
Default SUMIF Formula Help

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

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default SUMIF Formula Help

Try this...

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


--
Biff
Microsoft Excel MVP


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



  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 334
Default SUMIF Formula Help

Your formula worked great. Thanks for your help.

Rick

"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



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 334
Default SUMIF Formula Help

Thanks for you help.

Rick

"Huber57" wrote:

Rick,

Try this.

=SUMPRODUCT(--(B2:B33E1),C2:C33)

"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

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 334
Default SUMIF Formula Help

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

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default SUMIF Formula Help

=SUMIF('Store #1'!B:B,"<="&E1,'Store #1'!E:E)


--
Biff
Microsoft Excel MVP


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



  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,069
Default 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

  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 334
Default SUMIF Formula Help

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
SumIf formula BarbS via OfficeKB.com Excel Worksheet Functions 1 November 16th 08 08: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 09:07 PM


All times are GMT +1. The time now is 11:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"