how to sum with this table

[Andrea]

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

 

[Lars-Åke Aspelin]

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,MAX(NOT(ISBLANK(A3:A100))*(ROW(A3:A100))))))*(D3100))

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

 

[Andrea]

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

 

[Lars-Åke Aspelin]

In the following formula the "D04_x" can be in any cell from A1 to Z1.

=SUMPRODUCT((MONTH(A3:A100)=MONTH(INDEX(A1:A100,MAX(NOT(ISBLANK(A3:A100))*(ROW(A3:A100))))))*OFFSET(A3:A100,0,MATCH("D04_x",A1:Z1,0)-1))

Hope this helps / Lars-Åke

 

[Andrea]

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.

 

[Andrea]

I beg you pardon. Error reply is "#NAME!" and not "#NUM!".

 

[Lars-Åke Aspelin]

I beg you pardon. Error reply is "#NAME!" and not "#NUM!".

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

 

[Teethless mama]

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]

Many thanks teethless mama (and to everbody who reply to me, of course), its
works great !

Andrea.