Improve on "schoolboy" formula

[sykes]

Folks
I'm analysing the daily total of EV charging costs.
I'm allowing for a total of 3 charges per day ("Charges 1-3") - to allow for different vehicle movement schedules, and therefore possible charge opportunities.
I want to apply 3 different charging "types", namely "Cheap" (overnight/off peak) = £0.055/kWh), "Max" (day/peak) = £0.1293/kWh) and "E+" (purely from solar PV, which I'm considering as free).
Cols B, D & F hold the 3 charge "types."
Cols C, E & G hold the charge vol delivered to the EV's battery, during each charge session.
In col H, I'm wishing to total the cost of that day's charging activities. I'm only needing to consider "Cheap" and "Max" charges, which makes life a little more simple.

There are more analytical cols after col H, but I've omitted these, as their content isn't relevant to my question.

I do have a formula in col H, which works, but it seems very "Schoolboy" to just use a series of "Ifs".

Any more slick way of achieving this?

I had a look at SUMIFS, but couldn't get it to work.

TIA

Scott electricity calcs.xlsm
	A	B	C	D	E	F	G	H
1	Date	Charge 1 (type)	charge vol kWh	Charge 2 (type)	charge vol kWh	Charge 3 (type)	charge vol kWh	Daily cost £
2	Thu-11-Mar-21	E+	1					0.00
3	Fri-12-Mar-21	Cheap	4.7					0.26
4	Sat-13-Mar-21	Cheap	7	E+	6	Max	3	0.77
e2008

 

[Snakehips]

You could try this in H2 and copy down....

=(SUMIF(B2:G2,"Cheap",C2:H2)*0.055)+(SUMIF(B2:G2,"Max",C2:H2)*0.1293)

Hope that helps.

 

[Snakehips]

Apologies! Typo in the above formula.

Should read =(SUMIF(B2:F2,"Cheap",C2:G2)*0.055)+(SUMIF(B2:F2,"Max",C2:G2)*0.1293)

 

[jasonb75]

Another way to do the same thing

=SUM(SUMIF(B2:F2,{"Cheap","Max"},C2:G2)*{0.055,0.1293})

 

[sykes]

Apologies! Typo in the above formula.

Should read =(SUMIF(B2:F2,"Cheap",C2:G2)*0.055)+(SUMIF(B2:F2,"Max",C2:G2)*0.1293)

Thanks Tony

This works.
The only thing I'm not getting my head around, is how Excel "knows" to reference the adjacent cell to the right of the one where the searched value is found.
I realise that the 3rd argument is a range offset by one, to the first argument, but didn't realise that the summed cell would also be offset by the same degree.

 

[sykes]

Another way to do the same thing

=SUM(SUMIF(B2:F2,{"Cheap","Max"},C2:G2)*{0.055,0.1293})

Thanks Jason
As this is the first time I've actually used an array formula, I've gone with your solution. I like the fact that it's so curt.

For the avoidance of any doubt (for anyone else looking at these solutions), that provided by @Snakehips yielded just the same result.

I've marked this post as the solution, purely because I don't think I can mark them both (although I will try to, once I've posted this.)

 

[Snakehips]

Thanks Tony

This works.
The only thing I'm not getting my head around, is how Excel "knows" to reference the adjacent cell to the right of the one where the searched value is found.
I realise that the 3rd argument is a range offset by one, to the first argument, but didn't realise that the summed cell would also be offset by the same degree.

As the 'criteria ' range and the 'sum' range are, of necessity, matching in dimension, the pattern of 'Trues' found in the criteria range will reflect the values to sum, when applied to the sum range.

 

[sykes]

As the 'criteria ' range and the 'sum' range are, of necessity, matching in dimension, the pattern of 'Trues' found in the criteria range will reflect the values to sum, when applied to the sum range.

Thank you Tony.

A very eloquently-put response!
That now makes perfect sense, and will, hopefully, be of help to others referencing this thread.

 

[jasonb75]

Not criticising in any way, Tony, just a bit of info that you may not be aware of.

As the 'criteria ' range and the 'sum' range are, of necessity, matching in dimension,

With SUMIF it is possible to break that theory using

=SUM(SUMIF(B2:F2,{"Cheap","Max"},C2)*{0.055,0.1293})

or

=SUM(SUMIF(B2,{"Cheap","Max"},C2:G2)*{0.055,0.1293})

The first of which will return the correct result, the second will not.

This anomaly is only possible (to the best of my knowledge) with SUMIF and AVERAGEIF, when the value and criteria ranges are of different sizes the criteria range is resized to match the values range. If the criteria range is smaller then the formula also becomes volatile, it is possible that a smaller values range will make the formula volatile as well, I have not done any testing to check this.

SUMIFS does not behave the same way and instead will give you a #VALUE! error if the ranges are not equal in size.