Formula for summing totals based on month and department, where department can change month by month...

[dshtesy]

Can anyone help with a formula for the below. I am wanting to get a monthly summary for the Total for each department. So, in cell B12, I would expect to see 21 as in May-23, Department A had Total costs of 15 (cell D3) and 6 (cell D4).

Most of the formulas I have tried are returning #Value!, I think because of the department values not being numbers and being included in the data.


TIA

 

[Peter_SSs]

Welcome to the MrExcel board!

Can you confirm whether those months in rows 1 and 10 are actual dates or text values?
If dates, are they the 1st day of the month?


It would also help greatly (& get faster help) if you could provide the sample data in a form that we can copy - instead of lots of typing.
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.

 

[dshtesy]

Monthly department wages example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Month	May-23	May-23	May-23	May-23	Jun-23	Jun-23	Jun-23	Jun-23	Jul-23	Jul-23	Jul-23	Jul-23
2		Salary	Bonus/Dividend	Total	Department	Salary	Bonus/Dividend	Total	Department	Salary	Bonus/Dividend	Total	Department
3	Person 1	5	10	15	A	5	2	7	D	5	7	12	D
4	Person 2	3	3	6	A	3	4	7	A	3	4	7	A
5	Person 3	6	8	14	B	6	6	12	B	6	2	8	B
6	Person 4	3	2	5	C	3	5	8	C	3	5	8	C
7	Person 5	9	3	12	D	9	4	13	D	9	8	17	D
8	Person 6	8	6	14	C	8	3	11	C	8	4	12	C
9
10		May-23	Jun-23	Jul-23
11	Department
12	A
13	B
14	C
15	D
Sheet1

Cell Formulas
Range	Formula
D3:D8,L3:L8,H3:H8	D3	=B3+C3

 

[dshtesy]

Welcome to the MrExcel board!

Can you confirm whether those months in rows 1 and 10 are actual dates or text values?
If dates, are they the 1st day of the month?


It would also help greatly (& get faster help) if you could provide the sample data in a form that we can copy - instead of lots of typing.
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.

Thank you very much Peter_SSs for both the welcome and the help.

I have tried to upload the sheet using the XL2BB Add-in. I think it has worked.

The months are actual dates and are dated the end of the month.

 

[Peter_SSs]

Thanks for the XL2BB sample data.
Would this be suitable?

23 06 22.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Month	May-23	May-23	May-23	May-23	Jun-23	Jun-23	Jun-23	Jun-23	Jul-23	Jul-23	Jul-23	Jul-23
2		Salary	Bonus/Dividend	Total	Department	Salary	Bonus/Dividend	Total	Department	Salary	Bonus/Dividend	Total	Department
3	Person 1	5	10	15	A	5	2	7	D	5	7	12	D
4	Person 2	3	3	6	A	3	4	7	A	3	4	7	A
5	Person 3	6	8	14	B	6	6	12	B	6	2	8	B
6	Person 4	3	2	5	C	3	5	8	C	3	5	8	C
7	Person 5	9	3	12	D	9	4	13	D	9	8	17	D
8	Person 6	8	6	14	C	8	3	11	C	8	4	12	C
9
10		May-23	Jun-23	Jul-23
11	Department
12	A	21	7	7
13	B	14	12	8
14	C	19	19	20
15	D	12	20	29
Monthly Totals

Cell Formulas
Range	Formula
D3:D8,L3:L8,H3:H8	D3	=B3+C3
B12:D15	B12	=LET(m,FILTER($B$3:$M$8,$B$1:$M$1=B$10),SUM(FILTER(m,INDEX(m,0,4)=$A12,0))/2)

 

[dshtesy]

Thanks for the XL2BB sample data.
Would this be suitable?

23 06 22.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Month	May-23	May-23	May-23	May-23	Jun-23	Jun-23	Jun-23	Jun-23	Jul-23	Jul-23	Jul-23	Jul-23
2		Salary	Bonus/Dividend	Total	Department	Salary	Bonus/Dividend	Total	Department	Salary	Bonus/Dividend	Total	Department
3	Person 1	5	10	15	A	5	2	7	D	5	7	12	D
4	Person 2	3	3	6	A	3	4	7	A	3	4	7	A
5	Person 3	6	8	14	B	6	6	12	B	6	2	8	B
6	Person 4	3	2	5	C	3	5	8	C	3	5	8	C
7	Person 5	9	3	12	D	9	4	13	D	9	8	17	D
8	Person 6	8	6	14	C	8	3	11	C	8	4	12	C
9
10		May-23	Jun-23	Jul-23
11	Department
12	A	21	7	7
13	B	14	12	8
14	C	19	19	20
15	D	12	20	29
Monthly Totals

Cell Formulas
Range	Formula
D3:D8,L3:L8,H3:H8	D3	=B3+C3
B12:D15	B12	=LET(m,FILTER($B$3:$M$8,$B$1:$M$1=B$10),SUM(FILTER(m,INDEX(m,0,4)=$A12,0))/2)

Brill, that has worked. Thank you very much.

Is there a formula to count how many people are in each department each month?

 

[Peter_SSs]

Brill, that has worked. Thank you very much.

Good news! You are welcome.

Is there a formula to count how many people are in each department each month?

Like this?

23 06 22.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Month	May-23	May-23	May-23	May-23	Jun-23	Jun-23	Jun-23	Jun-23	Jul-23	Jul-23	Jul-23	Jul-23
2		Salary	Bonus/Dividend	Total	Department	Salary	Bonus/Dividend	Total	Department	Salary	Bonus/Dividend	Total	Department
3	Person 1	5	10	15	A	5	2	7	D	5	7	12	D
4	Person 2	3	3	6	A	3	4	7	A	3	4	7	A
5	Person 3	6	8	14	B	6	6	12	B	6	2	8	B
6	Person 4	3	2	5	C	3	5	8	C	3	5	8	C
7	Person 5	9	3	12	D	9	4	13	D	9	8	17	D
8	Person 6	8	6	14	C	8	3	11	C	8	4	12	C
9
10		May-23	Jun-23	Jul-23
11	Department
12	A	2	1	1
13	B	1	1	1
14	C	2	2	2
15	D	1	2	2
People Count

Cell Formulas
Range	Formula
D3:D8,L3:L8,H3:H8	D3	=B3+C3
B12:D15	B12	=COUNTIF(INDEX($B$3:$M$8,0,MATCH(B$10,$B$1:$M$1,0)+3),$A12)

 

[dshtesy]

Good news! You are welcome.


Like this?

23 06 22.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Month	May-23	May-23	May-23	May-23	Jun-23	Jun-23	Jun-23	Jun-23	Jul-23	Jul-23	Jul-23	Jul-23
2		Salary	Bonus/Dividend	Total	Department	Salary	Bonus/Dividend	Total	Department	Salary	Bonus/Dividend	Total	Department
3	Person 1	5	10	15	A	5	2	7	D	5	7	12	D
4	Person 2	3	3	6	A	3	4	7	A	3	4	7	A
5	Person 3	6	8	14	B	6	6	12	B	6	2	8	B
6	Person 4	3	2	5	C	3	5	8	C	3	5	8	C
7	Person 5	9	3	12	D	9	4	13	D	9	8	17	D
8	Person 6	8	6	14	C	8	3	11	C	8	4	12	C
9
10		May-23	Jun-23	Jul-23
11	Department
12	A	2	1	1
13	B	1	1	1
14	C	2	2	2
15	D	1	2	2
People Count

Cell Formulas
Range	Formula
D3:D8,L3:L8,H3:H8	D3	=B3+C3
B12:D15	B12	=COUNTIF(INDEX($B$3:$M$8,0,MATCH(B$10,$B$1:$M$1,0)+3),$A12)

Fantastic, thank you very much indeed

 

[Peter_SSs]

Cheers. No problem.