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

	A​	B​	C​	D​	E​	F​
1​	Months​	Value​			June​	1750​
2​	APRIL​	500​
3​	MAY​	550​
4​	JUNE​	700​
5​	JULY​	800​
6​

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

M.

 

[Eric W]

How about:

	A	B	C	D	E
1	Month				JUNE
2					1750
3	APRIL	500
4	MAY	550
5	JUNE	700
6	JULY	800
7	AUGUST	860
8

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet13​

Worksheet Formulas Cell Formula E2 =SUM(INDEX(B2:B10,MATCH("APRIL",A2:A10,0)):INDEX(B2:B10,MATCH(E1,A2:A10,0))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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

	A​	B​	C​	D​	E​	F​
1​	Months​	Value​			June​	1750​
2​	APRIL​	500​
3​	MAY​	550​
4​	JUNE​	700​
5​	JULY​	800​
6​

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.