Sumif formulation

[Nova1979]

Hello All. I am finding myself in need of some assistance.

I am working with someone else's spreadsheet that I cannot change the layout (minor changes permitted) and excel 2007 or earlier.
I can work out basic sumif formula, but am needing something for the following information

Date	Groceries	$		Date	Groceries	$		Date	Groceries	$
3/1	Aldi	12.46				1,182.72				2,414.10
3/1	Arnolds	24.15		21/3	IGA	6.99		1/7	Coles	20.50
3/1	Coles	92.89		30/3	IGA	3.85		1/7	Birra Butcher	16.00
5/1	Chem' Warehouse	25.96		30/3	Woolworths	71.27		10/7	Coles	98.40
9/1	Coles	21.60		4/4	Coles	92.65		10/7	Birra Butcher	16.00
11/1	Woolworths	21.95		4/4	Bakers Delight	7.00		11/7	Arnolds	15.24
17/1	Arnolds	30.89		13/4	Coles	75.90		13/7	Mate St. Pharmacy	24.90
17/1	Coles	106.10		13/4	Birra Butcher	22.15
17/1	Butcher on Rise	14.00		24/4	Bakers Delight	7.00
17/1	IGA	3.75		24/4	Coles	101.77
28/1	Woolworths	86.05		28/4	Aldi	19.49
28/1	Arnolds	30.05		1/5	Harris Farm	48.85
8/2	Coles	151.17		1/5	Coles	61.48
8/2	Birra Butcher	17.95		1/5	Aldi	23.24
10/2	Harris Farm	33.97		9/5	Arnolds	16.21
10/2	Aldi	21.21		16/5	Coles	147.78
20/2	Arnolds	32.44		16/5	Bakers Delight	4.00
20/2	Coles	88.42		16/5	Birra Butcher	16.00
22/2	IGA	13.98		16/5	Chem W - Lens wipes	8.99
23/2	Woolworths	53.85		23/5	Coles	16.50
23/2	IGA	19.24		23/5	Harris Farm	41.07
1/3	Coles	17.30		29/5	Woolworths	88.40
1/3	Mate St Pharm'	24.90		30/5	Coles	16.30
7/3	Bakers Delight	7.00		3/6	Coles	58.82
7/3	Coles	7.60		13/6	Arnolds	35.32
8/3	Coles	26.39		16/6	Coles	91.40
11/3	IGA - Mariesfarewell	6.10		20/6	Harris Farm	48.70
13/3	Birra Butcher	16.00		20/6	Aldi	22.25
13/3	Bakers Delight	4.00		20/6	Woolworths	55.00
13/3	Coles	75.55		27/6	Harris Farm	23.00
20/3	Coles	85.51
20/3	Arnolds	22.75

I would like the sumif for total expenditure for each month. As can be seen in columns A and E the dates for March carry over. This is where I am having issues (as well as trying to get a sumif to work based on the date in general)

I could utilise query sheets and pivot tables, however the person I am doing this for is older and I am trying to make it as easy for them as possible by not having to remember to refresh multiple sheets.

Any assistance would be greatful

Thanks in advance

 

[etaf]

how many columns is this likely to be - why does it carry over to new columns ?

where is the date for march going to be put , and where do you want the totals

sumifs was added to 2007 version , you say earlier - what versions earlier are you using ???????

=SUMIFS(C4:C35,A4:A35,">="&DATEVALUE("1/3/23"),A4:A35,"<="&DATEVALUE("31/3/23"))+SUMIFS(G4:G35,E4:E35,">="&DATEVALUE("1/3/23"),E4:E35,"<="&DATEVALUE("31/3/23"))+SUMIFS(K4:K35,I4:I35,">="&DATEVALUE("1/3/23"),I4:I35,"<="&DATEVALUE("31/3/23"))

so add for each columns of entries

i have added the formula in cell B44 - as i assume this will go horizontally for ever ??????

you say total for March - I have hardcoded , but i suspect months will change how will that be entered and where ????

Book2
	A	B	C	D	E	F	G	H	I	J	K
1
2	Date	Groceries	$		Date	Groceries	$		Date	Groceries	$
3
4	3-Jan	Aldi	12.46				1,182.72				2,414.10
5	3-Jan	Arnolds	24.15		21-Mar	IGA	6.99		1-Jul	Coles	20.5
6	3-Jan	Coles	92.89		30-Mar	IGA	3.85		1-Jul	Birra Butcher	16
7	5-Jan	Chem' Warehouse	25.96		30-Mar	Woolworths	71.27		10-Jul	Coles	98.4
8	9-Jan	Coles	21.6		4-Apr	Coles	92.65		10-Jul	Birra Butcher	16
9	11-Jan	Woolworths	21.95		4-Apr	Bakers Delight	7		11-Jul	Arnolds	15.24
10	17-Jan	Arnolds	30.89		13-Apr	Coles	75.9		13-Jul	Mate St. Pharmacy	24.9
11	17-Jan	Coles	106.1		13-Apr	Birra Butcher	22.15
12	17-Jan	Butcher on Rise	14		24-Apr	Bakers Delight	7
13	17-Jan	IGA	3.75		24-Apr	Coles	101.77
14	28-Jan	Woolworths	86.05		28-Apr	Aldi	19.49
15	28-Jan	Arnolds	30.05		1-May	Harris Farm	48.85
16	8-Feb	Coles	151.17		1-May	Coles	61.48
17	8-Feb	Birra Butcher	17.95		1-May	Aldi	23.24
18	10-Feb	Harris Farm	33.97		9-May	Arnolds	16.21
19	10-Feb	Aldi	21.21		16-May	Coles	147.78
20	20-Feb	Arnolds	32.44		16-May	Bakers Delight	4
21	20-Feb	Coles	88.42		16-May	Birra Butcher	16
22	22-Feb	IGA	13.98		16-May	Chem W - Lens wipes	8.99
23	23-Feb	Woolworths	53.85		23-May	Coles	16.5
24	23-Feb	IGA	19.24		23-May	Harris Farm	41.07
25	1-Mar	Coles	17.3		29-May	Woolworths	88.4
26	1-Mar	Mate St Pharm'	24.9		30-May	Coles	16.3
27	7-Mar	Bakers Delight	7		3-Jun	Coles	58.82
28	7-Mar	Coles	7.6		13-Jun	Arnolds	35.32
29	8-Mar	Coles	26.39		16-Jun	Coles	91.4
30	11-Mar	IGA - Mariesfarewell	6.1		20-Jun	Harris Farm	48.7
31	13-Mar	Birra Butcher	16		20-Jun	Aldi	22.25
32	13-Mar	Bakers Delight	4		20-Jun	Woolworths	55
33	13-Mar	Coles	75.55		27-Jun	Harris Farm	23
34	20-Mar	Coles	85.51
35	20-Mar	Arnolds	22.75
36
37
38
39
40
41
42
43	March
44	total	375.21
45
Sheet1

Cell Formulas
Range	Formula
B44	B44	=SUMIFS(C4:C35,A4:A35,">="&DATEVALUE("1/3/23"),A4:A35,"<="&DATEVALUE("31/3/23"))+SUMIFS(G4:G35,E4:E35,">="&DATEVALUE("1/3/23"),E4:E35,"<="&DATEVALUE("31/3/23"))+SUMIFS(K4:K35,I4:I35,">="&DATEVALUE("1/3/23"),I4:I35,"<="&DATEVALUE("31/3/23"))

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone