Identify the cell number from a column from the last repeated value using the a value as a reference

[pjinacio]

Hi again,

I'm being burning my brain but till now I could not found a solution to my problem

[TABLE="width: 500"]
<tbody>[TR]
[TD]month
[/TD]
[TD]week
[/TD]
[TD]1wk
[/TD]
[TD]2wk
[/TD]
[TD]3wk
[/TD]
[TD]4wk
[/TD]
[TD]5wk
[/TD]
[TD]6wk
[/TD]
[TD]7wk
[/TD]
[TD]8wk
[/TD]
[TD]9wk
[/TD]
[TD]10wk
[/TD]
[TD]11wk
[/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD="align: center"]37
[/TD]
[TD]1000
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD="align: center"]38
[/TD]
[TD]975
[/TD]
[TD]950
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD="align: center"]39
[/TD]
[TD]950
[/TD]
[TD]925
[/TD]
[TD]900
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD="align: center"]40
[/TD]
[TD]925
[/TD]
[TD]900
[/TD]
[TD]875
[/TD]
[TD]850
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD="align: center"]41
[/TD]
[TD]900
[/TD]
[TD]875
[/TD]
[TD]850
[/TD]
[TD]825
[/TD]
[TD]800
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD="align: center"]42
[/TD]
[TD]875
[/TD]
[TD]850
[/TD]
[TD]825
[/TD]
[TD]800
[/TD]
[TD]775
[/TD]
[TD]750
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD="align: center"]43
[/TD]
[TD]850
[/TD]
[TD]825
[/TD]
[TD]800
[/TD]
[TD]775
[/TD]
[TD]750
[/TD]
[TD]725
[/TD]
[TD]700
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: center"]11
[/TD]
[TD="align: center"]44
[/TD]
[TD]825
[/TD]
[TD]800
[/TD]
[TD]775
[/TD]
[TD]750
[/TD]
[TD]725
[/TD]
[TD]700
[/TD]
[TD]675
[/TD]
[TD]650
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]11
[/TD]
[TD="align: center"]45
[/TD]
[TD]800
[/TD]
[TD]775
[/TD]
[TD]750
[/TD]
[TD]725
[/TD]
[TD]700
[/TD]
[TD]675
[/TD]
[TD]650
[/TD]
[TD]625
[/TD]
[TD]600
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]11
[/TD]
[TD="align: center"]46
[/TD]
[TD]775
[/TD]
[TD]750
[/TD]
[TD]725
[/TD]
[TD]700
[/TD]
[TD]675
[/TD]
[TD]650
[/TD]
[TD]625
[/TD]
[TD]600
[/TD]
[TD]575
[/TD]
[TD]550
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]11
[/TD]
[TD="align: center"]47
[/TD]
[TD]750
[/TD]
[TD]725
[/TD]
[TD]700
[/TD]
[TD]675
[/TD]
[TD]650
[/TD]
[TD]625
[/TD]
[TD]600
[/TD]
[TD]575
[/TD]
[TD]550
[/TD]
[TD]525
[/TD]
[TD]500
[/TD]
[/TR]
</tbody>[/TABLE]

I want to extract the information from a specific month identified in the cell $A$3 equal to (month number) and from the last week of the month.

So the result that I want to get is

example:
month 9 - result of 2.775
month 10 - result of 5.425
month 11 - result of 6.875

thanks in advance for your help.

 

[jimrward]

your results do not match your data, please supply more information

 

[pjinacio]

Maybe you are summing the column "week" . This column it the week number from the month.

For example if you count the month 9 the result should be the sum from 1wk till 11 wk.
so it will be 950 +925 + 900 = 2.775

and the same methodology for the other months

 

[Vidar]

Hi

This array formula might work if your data starts in cell A1 with the "month" header.
=SUM(INDIRECT("C"&LARGE(IF($A$1:$A$12=9,ROW($A$1:$A$12),1),1)&":M"&LARGE(IF($A$1:$A$12=9,ROW($A$1:$A$12),1),1)))
REMEMBER to hit Ctrl+Shift+Enter after typing or pasting the formula or else it wont work.
This will find the sum for September (9). Change the "9" in the formula to a cell reference where you put your month number to search for.

Vidar

 

[pjinacio]

Hi

This array formula might work if your data starts in cell A1 with the "month" header.
=SUM(INDIRECT("C"&LARGE(IF($A$1:$A$12=9,ROW($A$1:$A$12),1),1)&":M"&LARGE(IF($A$1:$A$12=9,ROW($A$1:$A$12),1),1)))
REMEMBER to hit Ctrl+Shift+Enter after typing or pasting the formula or else it wont work.
This will find the sum for September (9). Change the "9" in the formula to a cell reference where you put your month number to search for.

Vidar

Thanks a lot Vidar.

It worked like a glove!!!

Just perfect.

Thanks again.

 

[Vidar]

You're welcome , thanks for the reply