Single formula to get sum of specific values for each cell in column based on criteria

[strangebiscuit]

Sorry if the title is a bit obtuse...little hard to explain.

I'm trying to come up with a single sum formula that evaluates each cell in a column and adds a specific value to the sum based on the contents of that cell and two other cells in the same row. Here's an example table:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Utility[/TD]
[TD]Status[/TD]
[TD]Usage[/TD]
[/TR]
[TR]
[TD]power[/TD]
[TD]good[/TD]
[TD]1200[/TD]
[/TR]
[TR]
[TD]gas[/TD]
[TD]good[/TD]
[TD]450[/TD]
[/TR]
[TR]
[TD]power[/TD]
[TD]bad[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]power[/TD]
[TD]good[/TD]
[TD]2500[/TD]
[/TR]
[TR]
[TD]gas[/TD]
[TD]good[/TD]
[TD]800[/TD]
[/TR]
</tbody>[/TABLE]

What I'd want in this situation is a single cell at the bottom with a formula in it that looks at each row, checks to see that the status is "good" and if so adds the following condition-based values to a total:

if the Utility is "power" and Usage >= 1000, add +5 to the total
if the Utility is "power" and Usage >= 2000, add +10 to the total
if the Utility is "gas" and Usage >= 300, add +5 to the total
if the Utility is "gas" and Usage >= 500, add +10 to the total

So in this case the total would be 30 (5+5+0+10+10).

This is just a simplified example, but I think if I had a sense of how to accomplish this (if it's even possible) I could expand it to fit other conditions.

I've done something similar in the past using SUMIFS, but in that case I was actually summing the values in the cells rather than summing other values inserted based on the values of the cell.

Again, I'm looking for a standalone formula rather than a formula per-row in a separate column or something based on a pivot table, etc... I know I could create another column and use nested IFs, i.e. something like

=IF(B2 = "good", IF(A2="power", IF(C2 >= 2000, 10, IF(C2 >= 1000, 5, 0)), IF(C2 >= 500, 10, IF(C2 >= 300, 5, 0))), 0)

and drag it down the column and then get a sum of that column. But a formula in a single cell would be much more desirable.

Obviously, it could be done in VBA as well, but it needs to be live updating so a formula seems better.

If anybody has any tips as to what functions I could combine to create this, it'd be super helpful and greatly appreciated. Or if someone can confirm that's impossible to do as a standalone formula, that'd be helpful too!

Thanks in advance for your assistance/advice!

 

[dreid1011]

Based off your sample and expected results, this seems to do the trick. Adjust the references to suit your data. (And someone may even have a nicer looking solution)

=SUMPRODUCT(($A$2:$A$6="power")*($B$2:$B$6="good")*(IFERROR(LOOKUP($C$2:$C$6,{1000,2000},{5,10}),0))+($A$2:$A$6="gas")*($B$2:$B$6="good")*(IFERROR(LOOKUP($C$2:$C$6,{300,500},{5,10}),0)))

This is an array formula entered with CTRL+SHIFT+ENTER, not just ENTER.

 

[Aladin Akyurek]

Looks like...

Control+shift+enter, not just enter:

=SUM(IF(B2:B6="good",IF(A2:A6="gas",LOOKUP(C2:C6,{0,300,500},{0,5,10}),IF(A2:A6="power",LOOKUP(C2:C6,{0,1000,2000},{0,5,10})))))

 

[strangebiscuit]

Wow, these are both fantastic replies! The second one is easier for me personally to understand, so I'm working with that at the moment. Thank you both so much for helping me with this. I'm extremely grateful!

But now I've run into another small problem as I'm new to these array formulas. I've expanded upon your examples for my final use case (including adding an if statement that sums negative values for "bad" entries) and I'm adding it to my VBA macro (which inserts a bunch of different formulas) but my formula is now over 255 characters and I'm running into the "Unable to set the FormulaArray property of the Range class" error.

I can see there's a workaround using .Replace, but I'm having some trouble finding a good guide for how to use this technique. My macro inserts formulas using a series of .Offset calls within a With statement, and I'm confused as to whether I need to use another With statement inside of this to do the .Replace...

Can either of you (or anyone else who cares to help) point me in the right direction as to how to insert this long function using .Replace?

Currently the line that inserts the formula (which is causing the "Unable to set the FormulaArray" error) looks like this:

.Offset(5, 1).FormulaArray = "=SUM(IF(G:G=""Accepted"",IF(F:F=""Gas"",LOOKUP(H:H,{0,250,500,700},{0,5,10,15}),IF(F:F=""POWER"",LOOKUP(H:H,{0,4000,6000,8000},{0,5,10,15}))),IF(G:G=""Clawback"",IF(F:F=""Gas"",LOOKUP(H:H,{0,250,500,700},{0,-5,-10,-15}),IF(F:F=""POWER"",LOOKUP(H:H,{0,4000,6000,8000},{0,-5,-10,-15}))))))"

The formula works great when I paste it directly into a cell on my sheet (with single quotes instead of double of course). If I need to use R1C1 for this I'm totally fine to modify it for that, but according to the posts I've been reading it looks like A1 notation is supposed to work with FormulaArray even though MS says it doesn't.

I'd be exceedingly grateful for any additional advice on this, and again I can't thank you enough for the help you've already provided. Thanks again in advance!

 

[strangebiscuit]

Actually, think I've got something working just through experimentation. Just don't know if it's overkill or not really the right way to do it...but it does seem to be inserting the complete working formula.

I came up with this:

Dim Part1 As String
Dim Part2 As String
Dim dummyForm As String


dummyForm = "=SUM(IF(G:G=""Accepted"",""Part1_"", ""Part2_""))"


Part1 = "IF(F:F=""Gas"",LOOKUP(H:H,{0,250,500,700},{0,5,10,15}),IF(F:F=""POWER"",LOOKUP(H:H,{0,4000,6000,8000},{0,5,10,15})))"
Part2 = "IF(G:G=""Clawback"",IF(F:F=""Gas"",LOOKUP(H:H,{0,250,500,700},{0,-5,-10,-15}),IF(F:F=""POWER"",LOOKUP(H:H,{0,4000,6000,8000},{0,-5,-10,-15}))))"


With .Offset(5, 1)
	.FormulaArray = dummyForm
	.Replace What:="""Part1_""", Replacement:=Part1, lookat:=xlPart
	.Replace What:="""Part2_""", Replacement:=Part2, lookat:=xlPart
End With

Is there a much better or simpler way of doing this I should be aware of?

Once again, thanks so much or all your generous support...these forums are an absolute life saver.