Find subtotal values in a dynamic range - Formula or VBA

[Izzy10]

Hello everyone,

This is my first post here on this forum.

Please see the sample set of data below, this data is on a worksheet called Éxpenses'.
It is a report extracted from our database so there are no formulas on this sheet.

Book1
	A	B	C	D
1	Telephone
2	01-May	Name1	Description	100
3	02-May	Name2	Description	200
4	Sub total			300
5
6	Stationery
7	03-May	Name1	Description	10
8	04-May	Name2	Description	50
9	04-May	Name3	Description	45
10	04-May	Name4	Description	20
11	06-May	Name5	Description	18
12	Sub total			143
13
14	Car
15	05-May	Name1	Description	515
16	05-May	Name2	Description	1583
17	06-May	Name3	Description	200
18	Sub total			2298
Expenses

I am trying to find a way to capture subtotals for each categories (highlighted yellow),
the subtotal will always be in the same column (D), but the row is dynamic so it will always be different number of rows each time I run this calculation (once a week).
I would like the subtotal to be shown on a separate worksheet called 'Summary'
Cell B2 - Telephone subtotal
Cell B3 - Stationery subtotal
Cell B4 - Car subtotal

It would be great if I could achieve this using a formula.
If a formula is not possible, vba will be fine as well.

Thank you!

 

[Marcelo Branco]

Welcome to Mr Excel forum

Maybe something like this

31052022 Testes.xlsm
	A	B
1	Category	Sub Total
2	Telephone	300
3	Stationery	143
4	Car	2298
Summary

Cell Formulas
Range	Formula
B2:B4	B2	=INDEX(INDEX(Expenses!D:D,MATCH(A2,Expenses!A:A,0)):Expenses!D$1000,MATCH("Sub total",INDEX(Expenses!A:A,MATCH(A2,Expenses!A:A,0)):Expenses!A$1000,0))

M.

 

[Izzy10]

Thank you so much @Marcelo Branco, it is working great!

I have another question, in relation to this workbook.

In the 'Summary' sheet, there are many other categories in Column A.
For example, if there is a category called 'IT', but this data is not available in the 'Expense' Sheet,
It is now returning '#N/A' in the subtotal cell (column B)

How could the formula be modified so it will show '0' in these case?

Book1
	A	B
1	Category	Sub Total
2	Telephone	300
3	Stationery	143
4	Car	2298
5	IT	#N/A
Summary

Cell Formulas
Range	Formula
B2:B5	B2	=INDEX(INDEX(Expenses!D:D,MATCH(A2,Expenses!A:A,0)):Expenses!D$1000,MATCH("Sub total",INDEX(Expenses!A:A,MATCH(A2,Expenses!A:A,0)):Expenses!A$1000,0))

 

[Marcelo Branco]

How could the formula be modified so it will show '0' in these case?

Try
B2 copied down
=IF(COUNTIF(Expenses!A:A,A2),INDEX(INDEX(Expenses!D:D,MATCH(A2,Expenses!A:A,0)):Expenses!D$1000,MATCH("Sub total",INDEX(Expenses!A:A,MATCH(A2,Expenses!A:A,0)):Expenses!A$1000,0)),0)

M.

 

[Izzy10]

Try
B2 copied down
=IF(COUNTIF(Expenses!A:A,A2),INDEX(INDEX(Expenses!D:D,MATCH(A2,Expenses!A:A,0)):Expenses!D$1000,MATCH("Sub total",INDEX(Expenses!A:A,MATCH(A2,Expenses!A:A,0)):Expenses!A$1000,0)),0)

M.

Thank you again @Marcelo Branco, it works perfectly.

 

[Marcelo Branco]

You're welcome and thanks for the feedback

M.