Index/Match question?

[MOB]

With the following example;

APRIL 500
MAY 550
JUNE 700
JULY 800


The above months are text rather than a date entry.

In cell E1 I would enter a text month name.

I need a formula that sums the data above for all rows starting April, up to the row matching the entry in cell E1.

Eg E1 contains "JUNE" sum would be 1750

I'm thinking some kind of Index/Match but cant figure it out!

Thanks

 

[Marcelo Branco]

Try


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Months​

[/td][td]

Value​

[/td][td][/td][td][/td][td]

June​

[/td][td]

1750​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

APRIL​

[/td][td]

500​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

MAY​

[/td][td]

550​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

JUNE​

[/td][td]

700​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

JULY​

[/td][td]

800​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in F1
=SUM(B2:INDEX(B2:B5,MATCH(E1,A2:A7,0)))

M.

 

[Eric W]

How about:

	A	B	C	D	E
Month	JUNE
APRIL
MAY
JUNE
JULY
AUGUST

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1750[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]550[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]700[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]800[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]860[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet13​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=SUM(INDEX(B2:B10,MATCH("APRIL",A2:A10,0)):INDEX(B2:B10,MATCH(E1,A2:A10,0)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

=SUMPRODUCT(--(TEXT("01/"&A1:A4,"dd-mm-yy")+0<=F1)*(B1:B4))

where column A has months and figures in column B

 

[MOB]

Try


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Months​

[/td][td]

Value​

[/td][td][/td][td][/td][td]

June​

[/td][td]

1750​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

APRIL​

[/td][td]

500​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

MAY​

[/td][td]

550​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

JUNE​

[/td][td]

700​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

JULY​

[/td][td]

800​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in F1
=SUM(B2:INDEX(B2:B5,MATCH(E1,A2:A7,0)))

M.

Perfect, thanks

Thanks to the other posters too

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.