Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Criteria For SU<IF
Greetings,
I am trying to get the sum of a column of numbers (column E) if the date in column A is within the same month and year of the date in the row this formula is sitting in. This sheet covers several years. What I have so far is this formula sitting in E10 (the cell where I want the sum to be): =SUMIF($A:$A,$A10,E:E) Row 10 is used only as a monthly divider and monthly subtotals (For this example). Column A is formatted as date in the "mm dd, yy" format. With values in column E that should return a sum total, I am instead getting "$0.00". My criteria ($A10) is not working. How can I change the criteria to look at only for the "mm, yy" of column A's "mm dd, yy" entries and not the full "mm dd, yy" date? Any help will be greatly appreciated. -Minitman |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Criteria For SU<IF
=SUMPRODUCT(--(YEAR(A1:A100)=YEAR(A10)),--(MONTH(A1:A100)=MONTH(A10)),E1:E100)
-- __________________________________ HTH Bob "Minitman" wrote in message ... Greetings, I am trying to get the sum of a column of numbers (column E) if the date in column A is within the same month and year of the date in the row this formula is sitting in. This sheet covers several years. What I have so far is this formula sitting in E10 (the cell where I want the sum to be): =SUMIF($A:$A,$A10,E:E) Row 10 is used only as a monthly divider and monthly subtotals (For this example). Column A is formatted as date in the "mm dd, yy" format. With values in column E that should return a sum total, I am instead getting "$0.00". My criteria ($A10) is not working. How can I change the criteria to look at only for the "mm, yy" of column A's "mm dd, yy" entries and not the full "mm dd, yy" date? Any help will be greatly appreciated. -Minitman |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Criteria For SU<IF
Hey Bob,
Thanks for the reply. I copied and pasted your code: =SUMPRODUCT(--(YEAR(A1:A100)=YEAR(A10)),--(MONTH(A1:A100)=MONTH(A10)),E1:E100) And ran a test. It returned the #VALUE! error. I tried to modify it like so: =SUMPRODUCT(--(YEAR(A:A)=YEAR(A10)),--(MONTH(A:A)=MONTH(A10)),E:E) And it returned the #NUM! error. That column is formatted as currency, but that shouldn't make any difference, should it? What did I do wrong? -Minitman On Mon, 1 Dec 2008 21:29:20 -0000, "Bob Phillips" wrote: =SUMPRODUCT(--(YEAR(A1:A100)=YEAR(A10)),--(MONTH(A1:A100)=MONTH(A10)),E1:E100) __________________________________ HTH Bob "Minitman" wrote in message .. . Greetings, I am trying to get the sum of a column of numbers (column E) if the date in column A is within the same month and year of the date in the row this formula is sitting in. This sheet covers several years. What I have so far is this formula sitting in E10 (the cell where I want the sum to be): =SUMIF($A:$A,$A10,E:E) Row 10 is used only as a monthly divider and monthly subtotals (For this example). Column A is formatted as date in the "mm dd, yy" format. With values in column E that should return a sum total, I am instead getting "$0.00". My criteria ($A10) is not working. How can I change the criteria to look at only for the "mm, yy" of column A's "mm dd, yy" entries and not the full "mm dd, yy" date? Any help will be greatly appreciated. -Minitman |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Criteria For SU<IF
I don't know really, the formula works fine, I just re-tested it.
You can't use whole columns in SUMPRODUCT, unless you have Excel 2007, it must be an explicit range. -- __________________________________ HTH Bob "Minitman" wrote in message ... Hey Bob, Thanks for the reply. I copied and pasted your code: =SUMPRODUCT(--(YEAR(A1:A100)=YEAR(A10)),--(MONTH(A1:A100)=MONTH(A10)),E1:E100) And ran a test. It returned the #VALUE! error. I tried to modify it like so: =SUMPRODUCT(--(YEAR(A:A)=YEAR(A10)),--(MONTH(A:A)=MONTH(A10)),E:E) And it returned the #NUM! error. That column is formatted as currency, but that shouldn't make any difference, should it? What did I do wrong? -Minitman On Mon, 1 Dec 2008 21:29:20 -0000, "Bob Phillips" wrote: =SUMPRODUCT(--(YEAR(A1:A100)=YEAR(A10)),--(MONTH(A1:A100)=MONTH(A10)),E1:E100) __________________________________ HTH Bob "Minitman" wrote in message . .. Greetings, I am trying to get the sum of a column of numbers (column E) if the date in column A is within the same month and year of the date in the row this formula is sitting in. This sheet covers several years. What I have so far is this formula sitting in E10 (the cell where I want the sum to be): =SUMIF($A:$A,$A10,E:E) Row 10 is used only as a monthly divider and monthly subtotals (For this example). Column A is formatted as date in the "mm dd, yy" format. With values in column E that should return a sum total, I am instead getting "$0.00". My criteria ($A10) is not working. How can I change the criteria to look at only for the "mm, yy" of column A's "mm dd, yy" entries and not the full "mm dd, yy" date? Any help will be greatly appreciated. -Minitman |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Criteria For SU<IF
Hi,
First, you are comparing A10 to A10 since A10 is in the range A1:A100, is this really what you want? Second the formula will return an error is the dates in column A are not Excel acceptable dates or if the entries in column E are formulas anyone of which returns a VALUE error. Also the dates you are comparing the range to must be a date, in my example below that is cell A10 =SUMPRODUCT(--(YEAR(B1:B100)=YEAR(A10)),--(MONTH(B1:B100)=MONTH(A10)),F1:F100) I have tested the formula Bob gave you and it works fine if none of the problems mentioned above are going on. If this helps, please click the Yes button. Cheers, Shane Devenshire "Minitman" wrote: Hey Bob, Thanks for the reply. I copied and pasted your code: =SUMPRODUCT(--(YEAR(A1:A100)=YEAR(A10)),--(MONTH(A1:A100)=MONTH(A10)),E1:E100) And ran a test. It returned the #VALUE! error. I tried to modify it like so: =SUMPRODUCT(--(YEAR(A:A)=YEAR(A10)),--(MONTH(A:A)=MONTH(A10)),E:E) And it returned the #NUM! error. That column is formatted as currency, but that shouldn't make any difference, should it? What did I do wrong? -Minitman On Mon, 1 Dec 2008 21:29:20 -0000, "Bob Phillips" wrote: =SUMPRODUCT(--(YEAR(A1:A100)=YEAR(A10)),--(MONTH(A1:A100)=MONTH(A10)),E1:E100) __________________________________ HTH Bob "Minitman" wrote in message .. . Greetings, I am trying to get the sum of a column of numbers (column E) if the date in column A is within the same month and year of the date in the row this formula is sitting in. This sheet covers several years. What I have so far is this formula sitting in E10 (the cell where I want the sum to be): =SUMIF($A:$A,$A10,E:E) Row 10 is used only as a monthly divider and monthly subtotals (For this example). Column A is formatted as date in the "mm dd, yy" format. With values in column E that should return a sum total, I am instead getting "$0.00". My criteria ($A10) is not working. How can I change the criteria to look at only for the "mm, yy" of column A's "mm dd, yy" entries and not the full "mm dd, yy" date? Any help will be greatly appreciated. -Minitman |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Criteria For SU<IF
Hey Shane,
Thanks for the reply. I think you got it right!!! My approach seems to be less then ideal. I am trying to get monthly subtotals of 25 columns. Do you have any suggestions as to a practical way to do this (it appears that my idea was not very practical)? Any help is appreciated. -Minitman On Mon, 1 Dec 2008 20:44:02 -0800, Shane Devenshire wrote: Hi, First, you are comparing A10 to A10 since A10 is in the range A1:A100, is this really what you want? Second the formula will return an error is the dates in column A are not Excel acceptable dates or if the entries in column E are formulas anyone of which returns a VALUE error. Also the dates you are comparing the range to must be a date, in my example below that is cell A10 =SUMPRODUCT(--(YEAR(B1:B100)=YEAR(A10)),--(MONTH(B1:B100)=MONTH(A10)),F1:F100) I have tested the formula Bob gave you and it works fine if none of the problems mentioned above are going on. If this helps, please click the Yes button. Cheers, Shane Devenshire "Minitman" wrote: Hey Bob, Thanks for the reply. I copied and pasted your code: =SUMPRODUCT(--(YEAR(A1:A100)=YEAR(A10)),--(MONTH(A1:A100)=MONTH(A10)),E1:E100) And ran a test. It returned the #VALUE! error. I tried to modify it like so: =SUMPRODUCT(--(YEAR(A:A)=YEAR(A10)),--(MONTH(A:A)=MONTH(A10)),E:E) And it returned the #NUM! error. That column is formatted as currency, but that shouldn't make any difference, should it? What did I do wrong? -Minitman On Mon, 1 Dec 2008 21:29:20 -0000, "Bob Phillips" wrote: =SUMPRODUCT(--(YEAR(A1:A100)=YEAR(A10)),--(MONTH(A1:A100)=MONTH(A10)),E1:E100) __________________________________ HTH Bob "Minitman" wrote in message .. . Greetings, I am trying to get the sum of a column of numbers (column E) if the date in column A is within the same month and year of the date in the row this formula is sitting in. This sheet covers several years. What I have so far is this formula sitting in E10 (the cell where I want the sum to be): =SUMIF($A:$A,$A10,E:E) Row 10 is used only as a monthly divider and monthly subtotals (For this example). Column A is formatted as date in the "mm dd, yy" format. With values in column E that should return a sum total, I am instead getting "$0.00". My criteria ($A10) is not working. How can I change the criteria to look at only for the "mm, yy" of column A's "mm dd, yy" entries and not the full "mm dd, yy" date? Any help will be greatly appreciated. -Minitman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
EXCEL - Meet 2 criteria, then find next case of third criteria | Excel Worksheet Functions | |||
=DMIN(database,field,criteria) question about criteria | Excel Discussion (Misc queries) | |||
Countif using format criteria not number criteria? | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions | |||
Counting Cells with multiple criteria.One criteria supporting wild | Excel Worksheet Functions |