Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to sum with this table
Hello,
My question is the follow. I've a table like this: DATA A01_x B01_y D04_x A01_y C03_k 01/01 5 3 12 4 6 01/10 7 5 14 6 8 01/12 5 15 9 14 6 01/25 9 5 8 13 1 02/05 12 11 11 13 7 02/06 13 8 6 1 5 03/17 17 12 2 6 18 03/08 1 15 10 5 17 04/09 6 10 10 3 12 04/10 16 11 7 14 4 I need to sum the values of column D04_x if month is the highest (I didn't specify "April" because I need to have an authomatic updating data if month changes, without modify formula every needs). Columns position could change according conditions I extract data from my server database. Thanks everybody will give to me a right advice. Andrea |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to sum with this table
On Sun, 14 Dec 2008 03:12:10 -0800, Andrea
wrote: Hello, My question is the follow. I've a table like this: DATA A01_x B01_y D04_x A01_y C03_k 01/01 5 3 12 4 6 01/10 7 5 14 6 8 01/12 5 15 9 14 6 01/25 9 5 8 13 1 02/05 12 11 11 13 7 02/06 13 8 6 1 5 03/17 17 12 2 6 18 03/08 1 15 10 5 17 04/09 6 10 10 3 12 04/10 16 11 7 14 4 I need to sum the values of column D04_x if month is the highest (I didn't specify "April" because I need to have an authomatic updating data if month changes, without modify formula every needs). Columns position could change according conditions I extract data from my server database. Thanks everybody will give to me a right advice. Andrea Assuming that your columns of dates is column A and that your columns of D04_x is column D, and that the data starts on row 3 i those columns, you may try the following formula: =SUMPRODUCT((MONTH(A3:A100)=MONTH(INDEX(A1:A100,MA X(NOT(ISBLANK(A3:A100))*(ROW(A3:A100))))))*(D3:D10 0)) change the 100 on four places to fit the number of rows with data that you have. Note that the A1 should not be changed. The formula will return the sum of D04_x for those rows that have the same month as the last date entered in column A. Hope this helps / Lars-Åke |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to sum with this table
Maybe your formula works only if D04_x is always present in column D. But, as
I written, if it change position ? So it would be a flexible formula that keeps in consideration this aspect. Thanks Assuming that your columns of dates is column A and that your columns of D04_x is column D, and that the data starts on row 3 i those columns, you may try the following formula: =SUMPRODUCT((MONTH(A3:A100)=MONTH(INDEX(A1:A100,MA X(NOT(ISBLANK(A3:A100))*(ROW(A3:A100))))))*(D3:D10 0)) change the 100 on four places to fit the number of rows with data that you have. Note that the A1 should not be changed. The formula will return the sum of D04_x for those rows that have the same month as the last date entered in column A. Hope this helps / Lars-Ã…ke |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to sum with this table
In the following formula the "D04_x" can be in any cell from A1 to Z1.
=SUMPRODUCT((MONTH(A3:A100)=MONTH(INDEX(A1:A100,MA X(NOT(ISBLANK(A3:A100))*(ROW(A3:A100))))))*OFFSET( A3:A100,0,MATCH("D04_x",A1:Z1,0)-1)) Hope this helps / Lars-Åke On Sun, 14 Dec 2008 04:32:00 -0800, Andrea wrote: Maybe your formula works only if D04_x is always present in column D. But, as I written, if it change position ? So it would be a flexible formula that keeps in consideration this aspect. Thanks Assuming that your columns of dates is column A and that your columns of D04_x is column D, and that the data starts on row 3 i those columns, you may try the following formula: =SUMPRODUCT((MONTH(A3:A100)=MONTH(INDEX(A1:A100,MA X(NOT(ISBLANK(A3:A100))*(ROW(A3:A100))))))*(D3:D10 0)) change the 100 on four places to fit the number of rows with data that you have. Note that the A1 should not be changed. The formula will return the sum of D04_x for those rows that have the same month as the last date entered in column A. Hope this helps / Lars-Åke |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to sum with this table
Hi Lars,
before all, thanks for your kind help. I try to put this formula, skipping the "NOT(ISBLANK(", because in my sheets data are without spaces, but the result is "#NUM!" reply. I checked well the commands as you give to me, but something doesn't like to excel. Have you an idea what kind of problem may occured having this reply ? Thanks again. Andrea. "Lars-Ã…ke Aspelin" wrote: In the following formula the "D04_x" can be in any cell from A1 to Z1. =SUMPRODUCT((MONTH(A3:A100)=MONTH(INDEX(A1:A100,MA X(NOT(ISBLANK(A3:A100))*(ROW(A3:A100))))))*OFFSET( A3:A100,0,MATCH("D04_x",A1:Z1,0)-1)) Hope this helps / Lars-Ã…ke On Sun, 14 Dec 2008 04:32:00 -0800, Andrea wrote: Maybe your formula works only if D04_x is always present in column D. But, as I written, if it change position ? So it would be a flexible formula that keeps in consideration this aspect. Thanks Assuming that your columns of dates is column A and that your columns of D04_x is column D, and that the data starts on row 3 i those columns, you may try the following formula: =SUMPRODUCT((MONTH(A3:A100)=MONTH(INDEX(A1:A100,MA X(NOT(ISBLANK(A3:A100))*(ROW(A3:A100))))))*(D3:D10 0)) change the 100 on four places to fit the number of rows with data that you have. Note that the A1 should not be changed. The formula will return the sum of D04_x for those rows that have the same month as the last date entered in column A. Hope this helps / Lars-Ã…ke |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to sum with this table
I beg you pardon. Error reply is "#NAME!" and not "#NUM!".
"Andrea" wrote: Hi Lars, before all, thanks for your kind help. I try to put this formula, skipping the "NOT(ISBLANK(", because in my sheets data are without spaces, but the result is "#NUM!" reply. I checked well the commands as you give to me, but something doesn't like to excel. Have you an idea what kind of problem may occured having this reply ? Thanks again. Andrea. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to sum with this table
Copy the formula from the formula bar and paste it here into the newsgroup
so that we can see what you are trying. -- David Biddulph "Andrea" wrote in message ... I beg you pardon. Error reply is "#NAME!" and not "#NUM!". "Andrea" wrote: Hi Lars, before all, thanks for your kind help. I try to put this formula, skipping the "NOT(ISBLANK(", because in my sheets data are without spaces, but the result is "#NUM!" reply. I checked well the commands as you give to me, but something doesn't like to excel. Have you an idea what kind of problem may occured having this reply ? Thanks again. Andrea. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to sum with this table
On Sun, 14 Dec 2008 05:40:06 -0800, Andrea
wrote: I beg you pardon. Error reply is "#NAME!" and not "#NUM!". "Andrea" wrote: Hi Lars, before all, thanks for your kind help. I try to put this formula, skipping the "NOT(ISBLANK(", because in my sheets data are without spaces, but the result is "#NUM!" reply. I checked well the commands as you give to me, but something doesn't like to excel. Have you an idea what kind of problem may occured having this reply ? Thanks again. Andrea. I forgot to mention that the formula is an array formula that has to be confirmed with CTRL+SHIFT+ENTER rather than just ENTER. By removing the "NOT(ISBLANK(" part of the formula you will not get the correct result if you don't have the range A3:A100 completely filled with dates. I assumed that you added more data at the end of the table every month and that you did NOT want to change the formula. By defining a sufficiently large range for these data you can have the same formula all the time. But then you DO need the "NOT(ISBLANK(" part of the formula, or something with a similar condition to find the last row with data. Lars-Åke |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to sum with this table
Try this:
Assuming your data in A1:F11 Header in Row 1 =SUMPRODUCT((MONTH(A2:A11)=MAX(INDEX(MONTH(A2:A11) ,)))*(B1:F1="D04_x")*B2:F11) Adjust your range to suit "Andrea" wrote: Hello, My question is the follow. I've a table like this: DATA A01_x B01_y D04_x A01_y C03_k 01/01 5 3 12 4 6 01/10 7 5 14 6 8 01/12 5 15 9 14 6 01/25 9 5 8 13 1 02/05 12 11 11 13 7 02/06 13 8 6 1 5 03/17 17 12 2 6 18 03/08 1 15 10 5 17 04/09 6 10 10 3 12 04/10 16 11 7 14 4 I need to sum the values of column D04_x if month is the highest (I didn't specify "April" because I need to have an authomatic updating data if month changes, without modify formula every needs). Columns position could change according conditions I extract data from my server database. Thanks everybody will give to me a right advice. Andrea |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how to sum with this table
Many thanks teethless mama (and to everbody who reply to me, of course), its
works great ! Andrea. "Teethless mama" wrote: Try this: Assuming your data in A1:F11 Header in Row 1 =SUMPRODUCT((MONTH(A2:A11)=MAX(INDEX(MONTH(A2:A11) ,)))*(B1:F1="D04_x")*B2:F11) Adjust your range to suit |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Macro specific table to generic table? | Excel Discussion (Misc queries) | |||
Pivit table- cell on spread sheet referencing pivot table field | Charts and Charting in Excel | |||
entry removed from source table remains in pivot table pull down | Excel Worksheet Functions | |||
Excel Pivot Table Plugin? (crosstab to data table) | Excel Discussion (Misc queries) | |||
PIVOT TABLE - Summary Table into a Databasae Table. | Excel Worksheet Functions |