What formula(s) can I use??

[BrianX_2024]

Hello,

I've tried quite a few lookup functions to work out how to create a formula to sum up our salaries to Oct Year-to-Date. I attach a screen shot and need a formula to add up the salaries (SAL) from Apr-24 to Oct-24 but I can't get it!

Does anybody have any suggestions?

I use Excel 2016. Thanks
Thanks,
Brian

 

[myall_blues]

Try this SUMPRODUCT formula. I put a date in cell I12 rather than your text (Reforecast Oct YTD) as this will make it movable or still work when you change for subsequent months.

As an aside, you should always include row numbers and column letters in screenshots.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3			Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24	Jan-25	Feb-25	Mar-25
4		SAL	1000	1000	1000	1000	1000	1000	1100	1100	1100	1100	1100	1100
5		SAL	200	200	200	200	200	200	210	210	210	210	210	210
6		SAL	100	100	100	100	100	100	105	105	105	105	105	105
7		MAR	500	500	500	500	500	500	450	450	450	450	450	450
8		RES	2000	2000	2000	2000	2000	2000	1950	1950	1950	1950	1950	1950
9		SOC	50	50	50	50	50	50	100	100	100	100	100	100
10
11									Reforecast YTD
12									Oct-24
13					SAL				9215
Sheet1

Cell Formulas
Range	Formula
I13	I13	=SUMPRODUCT(($B$4:$B$9=$E13)*($C$3:$N$3<=I$12)*$C$4:$N$9)

 

[BrianX_2024]

Try this SUMPRODUCT formula. I put a date in cell I12 rather than your text (Reforecast Oct YTD) as this will make it movable or still work when you change for subsequent months.

As an aside, you should always include row numbers and column letters in screenshots.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3			Apr-24	May-24	Jun-24	Jul-24	Aug-24	Sep-24	Oct-24	Nov-24	Dec-24	Jan-25	Feb-25	Mar-25
4		SAL	1000	1000	1000	1000	1000	1000	1100	1100	1100	1100	1100	1100
5		SAL	200	200	200	200	200	200	210	210	210	210	210	210
6		SAL	100	100	100	100	100	100	105	105	105	105	105	105
7		MAR	500	500	500	500	500	500	450	450	450	450	450	450
8		RES	2000	2000	2000	2000	2000	2000	1950	1950	1950	1950	1950	1950
9		SOC	50	50	50	50	50	50	100	100	100	100	100	100
10
11									Reforecast YTD
12									Oct-24
13					SAL				9215
Sheet1

Cell Formulas
Range	Formula
I13	I13	=SUMPRODUCT(($B$4:$B$9=$E13)*($C$3:$N$3<=I$12)*$C$4:$N$9)

Thank you so much, it works! So happy and really helpful. Yes, next time when I post screen shots I'll include row numbers and column letters. Thanks for your incredible help!

 

[myall_blues]

Thank you so much, it works! So happy and really helpful. Yes, next time when I post screen shots I'll include row numbers and column letters. Thanks for your incredible help!

You’re welcome. Thanks for the feedback.

 

[Peter_SSs]

Welcome to the MrExcel board!

I use Excel 2016.

I'm wondering why then you put 365 in your profile?

Helpers will generally be able to assist better if the correct version is shown in your profile.

Yes, next time when I post screen shots

Next time, even better than screen shots would be XL2BB
Not only will that include row and column labels but helpers can easily then copy your sample data to test with.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

 

[BrianX_2024]

Hi Peter_SSs,

Thanks you for advice. I have a lot to learn in this forum and it's much appreciated. At work, I use Excel 2016 and at home use 365. I will change it to Excel 2016 on my profile.

With XL2BB, I must admit, I've never heard of it! I'll certainly will have a look!

So much to know in Excel!☺

Thanks for your help!
Brian

 

[Peter_SSs]

At work, I use Excel 2016 and at home use 365.

In that case I suggest that you have both in your profile - as you currently have. Then when you start a thread, state which version or versions any suggested answers are required to work in.