Search Row for 2 Matches and Sum Collumn Below

Hi guys and gals,

I have a PL that lists the 12 months of the year and the year itself.

I want a formula that uses these values and searches Row1 in Revenue! and then returns the sum of the values below.

I have made some notes in blue and given an example on the sheet attached.

Thank you so much!

Danny

worksheet name : Forecast

Cell D3 formula , Drag right but Interval jump

HTML Code: 

Hi guys,

Thank you very much for your help! I have a much larger sheet and I attempted to extract some of the data and make the file (Excel Help Forum.xlsx), but can see I wasn't clear enough (my apologies!). Unfortunately none of the above formulas work when the start month is a jon-January one.

The 12-month forecast has the option to select a different start month, consequently meaning that some of the latter months will go over into the next year.

I have now taken a snippet of the actual Workbook which should make it more clearer. For this example Month1 is May 2026. January to April is in the following year (2027).

I have coloured the desired cells in green on the Forecast! sheet. They need to use the month in the cell directly above and the year two cells up and one to the right (eg. Month1/cell F17 = Month F16; Year G15).

From the Revenue! sheet, you can see the Years and Months are all listed on row8 (big sheet), and I want the sum of the 50 rows below returned.

If this is impossible to do on 1 line, I can always copy the year into the cells above every month (row7) and hide it in white font.

Thank you all again.

Danny

TRY: =SUM(OFFSET(Revenue!$C$8,1,MATCH(F$16,Revenue!$D$8:$O$8,)+(G$15-Revenue!$C$8)*16,50))

Thank you so much guys. It works like a charm!

I've given you all the reputation you deserve!

Before I close the ticket, I'd really appreciate one more formula which I'm sure you can do

On Sheet Admin!, row2 has a long row of dates. I'd like the month and year to match the same cells as before (eg. Formula Month1/cell F17 = Month F16; Year G15).

When the corresponding cell/date has been found with this month and year, I want the cell [in Admin!] one up and to the right (green bold % cells) returned. It can be returned as a number (not a percentage).

This is for the orange coloured cell on the Forecast! sheet.

If a new thread is required please let me know.

Thank you once again,

Danny

I am not sure how you derive the pre-populated numbers.

Is this what you're trying to achieve?
=SUMPRODUCT((TEXT(A$2,"mmmm")=Forecast!$F$16:$AD$16)*(YEAR(A$2)=Forecast!$G$15:$AE$15)*Forecast!$F$17:$AD$17)

But your data set is confusing to me.
In your file Month 1 and Month 2 are now both called June.
Too many hard plugs in my view which will make any inputs and outputs extremely error prone.

Cheers

Thank you Raul, perfect!