SUMIF/SUMIFS Help

[Chris_010101]

Hello

I am trying to track my energy usage and have a spreadsheet with two sheets

Energy Bill Tracker V1.0.xlsx
	A	B	C	D	E	F	G
1	No.	Period From	Period to	Electric	Gas	VAT	Total
2	1	01-Mar-22	29-Mar-22
Energy Bill

Energy Bill Tracker V1.0.xlsx
	A	B	C	D	E	F	G	H	I	J
1	No.	Period from	Period to	Fuel / Standing	Type	Previous Reading	Latest Reading	Units Used	KWH Rate or Standing Charge Daily Rate (Pence)	Charge
2	1	01-Mar-22	29-Mar-22	Electric	Day	558	659	101	£0.2253	£22.76
3	1	01-Mar-22	29-Mar-22	Electric	Night	106	123	17	£0.0793	£1.35
4	1	01-Mar-22	29-Mar-22	Gas	N/A	69	121	52	£0.3542	£20.58
5	1	01-Mar-22	29-Mar-22	Electric	Standing charge	0	29	29	£0.2272	£6.59
6	1	01-Mar-22	29-Mar-22	Gas	Standing charge	0	29	29	£0.2533	£7.35
Particulars

Cell Formulas
Range	Formula
H2:H6	H2	=IF(OR(ISBLANK(F2), ISBLANK(G2)), "", SUM(G2-F2))
J2:J3,J5:J6	J2	=IF(OR(ISBLANK(B2)),"",SUM(I2*H2))
J4	J4	=ROUNDUP(SUM(H4*39.3)*SUM(1.02264/3.6), 0)/SUM(100)*SUM(3.542)

Cells with Data Validation
Cell	Allow	Criteria
D2:D1048576	List	Electric, Gas, Electric Standing charge (daily), Gas Standing charge (daily)
E2:E1048576	List	Day, Night, Standing charge, N/A

In the 'Energy Bill' sheet, under the relevant column header (D/E), I would like a sum of all the charges for the relevant type (Electric/Gas) in the 'particulars' sheet (column E), based upon the bill number in column A.

In simple terms, bill number 1, sum all charges for "electric" - and so on

Kindest
Chris

 

[Fluff]

How about

=SUMIFS(Particulars!$J:$J,Particulars!$A:$A,$A2,Particulars!$D:$D,D$1)

 

[Chris_010101]

How about

=SUMIFS(Particulars!$J:$J,Particulars!$A:$A,$A2,Particulars!$D:$D,D$1)

Perfect, thanks!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Chris_010101]

You're welcome & thanks for the feedback.

This will work for both electricity and Gas? and when I add bill 2?

Energy Bill Tracker V1.0.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	No.	Period from	Period to	Fuel / Standing	Type	Previous Reading	Latest Reading	Units Used	KWH Rate or Standing Charge Daily Rate (Pence)	Charge	VAT @ 5%	Sub-total
2	1	01-Mar-22	29-Mar-22	Electric	Day	558	659	101	£0.2253	£22.76	£1.14	£23.89
3	1	01-Mar-22	29-Mar-22	Electric	Night	106	123	17	£0.0793	£1.35	£0.07	£1.42
4	1	01-Mar-22	29-Mar-22	Gas	N/A	69	121	52	£0.3542	£20.58	£1.03	£21.61
5	1	01-Mar-22	29-Mar-22	Electric	Standing charge	0	29	29	£0.2272	£6.59	£0.33	£6.92
6	1	01-Mar-22	29-Mar-22	Gas	Standing charge	0	29	29	£0.2533	£7.35	£0.37	£7.71
7	2	30-Mar-22	02-May-22	Electric	Day	659	751	92	£0.2253	£20.73	1.03638	21.76398
8	2	30-Mar-22	02-May-22	Electric	Night	123	148	25	£0.0793	£1.9825	£0.10	2.081625
9	2	30-Mar-22	02-May-22	Gas	N/A	121	163	42	£0.3542	£16.61	0.830599	17.44258
10	2	30-Mar-22	02-May-22	Electric	Standing charge	0	34	34
11	2	30-Mar-22	02-May-22	Gas	Standing charge	0	34	34
Particulars

Cell Formulas
Range	Formula
J2:J3,J5:J8	J2	=IF(OR(ISBLANK(B2)),"",SUM(I2*H2))
K2:K9	K2	=SUM(J2/100)*5
L2:L9	L2	=SUM(J2+K2)
J4,J9	J4	=ROUNDUP(SUM(H4*39.3)*SUM(1.02264/3.6), 0)/SUM(100)*SUM(3.542)
H2:H11	H2	=IF(OR(ISBLANK(F2), ISBLANK(G2)), "", SUM(G2-F2))

Cells with Data Validation
Cell	Allow	Criteria
D2:D1048576	List	Electric, Gas, Electric Standing charge (daily), Gas Standing charge (daily)
E2:E1048576	List	Day, Night, Standing charge, N/A

 

[Chris_010101]

You're welcome & thanks for the feedback.

I have changed to =SUMIFS(Particulars!J:J, Particulars!$A:$A, 'Energy Bill'!A2, Particulars!$D:$D, 'Energy Bill'!$D$1)

And now it appears to reference correctly and the formula drags down.

Thanks again!

 

[Fluff]

There was no need to change the formula, just drag it down & across.
Also you should never refer to the name of the sheet the formula is on.

 

[Chris_010101]

There was no need to change the formula, just drag it down & across.
Also you should never refer to the name of the sheet the formula is on.

Yes, strangely, I did that and it returned £0.00 the first few times.

I've just re-added it and tried again and it works... Excel boggles the mind.

Thank you again.

 

[Fluff]

My pleasure.