Sum row range based on column header criteria

[jtfish]

I'm trying to figure out how to do the following when the above sales chart can be dynamic, meaning months can be added to the end, or even at the beginning, as more sales data is entered. If the user inputs a salesperson's name (Chris in the above example) and the range of months that they want sales totals for, it will return the sum of that range (275 + 375 + 425 + 500 in the above example) AND all sales through the end date starting with the very first month in the chart, whatever that month may be (400 + 265 + 275 + 375 + 425 + 500 in the above example). I've tried to do this using INDEX and MATCH, SUMPRODUCT, SUMIF and INDEX, SUMPRODUCT etc. but I can't quite get it to work. I've even tried using a helper row that returns the column number (using MATCH) of the chosen begin and end months, so I'm not opposed to using a helper row (above the year row for example) if that makes it easier to manage.

Is there a way to do this? Any help is greatly appreciated.

 

[Herakles]

View attachment 99508

I'm trying to figure out how to do the following when the above sales chart can be dynamic, meaning months can be added to the end, or even at the beginning, as more sales data is entered. If the user inputs a salesperson's name (Chris in the above example) and the range of months that they want sales totals for, it will return the sum of that range (275 + 375 + 425 + 500 in the above example) AND all sales through the end date starting with the very first month in the chart, whatever that month may be (400 + 265 + 275 + 375 + 425 + 500 in the above example). I've tried to do this using INDEX and MATCH, SUMPRODUCT, SUMIF and INDEX, SUMPRODUCT etc. but I can't quite get it to work. I've even tried using a helper row that returns the column number (using MATCH) of the chosen begin and end months, so I'm not opposed to using a helper row (above the year row for example) if that makes it easier to manage.

Is there a way to do this? Any help is greatly appreciated.

Welcome to MrExcel.

What version of Excel are you actually using?

It would be a lot easier if instead of going across the worksheet you went down with columns as follows:

Name
Date
Value

Use the first of the month.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[ExceLoki]

here's a solution for 365 w/ 2 helper rows added, hope this helps
---------------------

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	by name	400	265	275	375	425	500	305
2	dates	10/1/2022	11/1/2022	12/1/2022	1/1/2023	2/1/2023	3/1/2023	4/1/2023
3	year	2022	2022	2022	2023	2023	2023	2023
4	month	10	11	12	1	2	3	4		salesperson	chris
5	bob	210	330	250	360	420	500	240
6	sam	325	260	510	375	125	330	200			begin	end
7	chris	400	265	275	375	425	500	305		year	2022	2023
8	mary	365	295	415	355	270	300	500		month	12	3
9	jane	245	450	320	310	400	215	300
10										answer 1	1575	within range
11										answer 2	2240	through end year/month
Sheet1

Cell Formulas
Range	Formula
B1:H1	B1	=FILTER(B5:H9,A5:A9=K4)
B2:H2	B2	=DATE(B3:H3,B4:H4,1)
K10	K10	=SUMIFS(B1#,B2#,">="&DATE(K7,K8,1),B2#,"<="&DATE(L7,L8,1))
K11	K11	=SUMIFS(B1#,B2#,">="&DATE(1900,1,1),B2#,"<="&DATE(L7,L8,1))
Dynamic array formulas.

 

[jtfish]

MrExcel Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Year:	2022	2022	2022	2023	2023	2023	2023
2	Month:	10	11	12	1	2	3	4		Salesperson	Chris
3	Bob	210	330	250	360	420	500	240
4	Sam	325	260	510	375	125	330	200			Begin	End
5	Chris	400	265	275	375	425	500	305		Year	2022	2023
6	Mary	365	295	415	355	270	300	500		Month	12	3
7	Jane	245	450	320	310	400	215	300
8										Answer	1,575	(within above range)
9										Answer	2,240	(through end year/month)
Sheet1

Herakles - Here's the data in XL2BB format. I use Excel version Home and Business 2019 at work and Office 365 at home. I really need this to work in 365 and non-365 versions because users could be using any Excel version. The example I posted here is a very simplified version of my actual worksheet just for illustriative purposes. I could have up to 10K salespeople and my resulting report will need to list salespeople in the left-most column. I suppose I could covert the data as you suggest but I'd need to convert it back after calculating the sales totals.

ExceLoki - I believe this will work but don't believe the hash(#) will work in non-365 versions.

 

[ExceLoki]

then you could just update the formula to use the range:

multiple posts solutions-v3.xlsm
	J	K	L
10	answer 1	1575	within range
11	answer 2	2240	through end year/month
sum row range fromdate and name

Cell Formulas
Range	Formula
K10	K10	=SUMIFS(B1:H1,B2:H2,">="&DATE(K7,K8,1),B2:H2,"<="&DATE(L7,L8,1))
K11	K11	=SUMIFS(B1:H1,B2:H2,">="&DATE(1900,1,1),B2:H2,"<="&DATE(L7,L8,1))

 

[jtfish]

That works! Sorry, I didn't realize that's all the hash did. Thanks for your help. Extremely appreciated.

 

[ExceLoki]

np, happy to help