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

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

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

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

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

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.

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.

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

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

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