Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SumIf formula | Excel Worksheet Functions | |||
Nesting a sumproduct formula within a sumif formula. | Excel Discussion (Misc queries) | |||
multiply formula where 1 cell has a (sumif) formula as a result | Excel Worksheet Functions | |||
Help with SUMIF formula Please | Excel Worksheet Functions | |||
Is there a MAXIF formula similar to the SUMIF formula? | Excel Discussion (Misc queries) |