Help with SUM formula based on data in separate table

[dogpile]

Could I get some assistance on a formula that I need in cell B11 below, where I'm looking to sum the total in column B for all of the units that are categorized as "old" (i.e. what's in cell A11) based on the table in F1:H7.

Excel 2013 64 bit
	A	B	C	D	E	F	G	H
1		Location1	Location2				Location1	Location2
2	Unit 1	1,000	15,000			Unit 1	old	new
3	Unit 2	1,000	15,000			Unit 2	new	new
4	Unit 3	2,500	2,000			Unit 3	new	new
5	Unit 4		3,000			Unit 4	old	new
6	Unit 4	100				Unit 5	new	new
7	Unit 5	1,500	4,000			Unit 6	old	new
8	Unit 6	500	800
9	Unit 6	500	600
10
11	old
Sheet1

Thanks in advance!

 

[Eric W]

Try:

=SUM(SUMIF($A$2:$A$9,IF(G$1:G$7="old",$F$1:$F$7,"xx"),B$2:B$9))

confirmed with Control+Shift+Enter.

 

[dogpile]

Brilliant, thank you. Can I add another criteria to the formula?
If column D is "cr" then include the negative of that amount (reverse sign) in the sum.

Excel 2013 64 bit
	A	B	C	D	E	F	G	H	I
1		Location1	Location2					Location1	Location2
2	Unit 1	1,000	15,000	dr			Unit 1	old	new
3	Unit 2	1,000	15,000	cr			Unit 2	new	new
4	Unit 3	2,500	2,000	cr			Unit 3	new	new
5	Unit 4		3,000	cr			Unit 4	old	new
6	Unit 4	100		cr			Unit 5	new	new
7	Unit 5	1,500	4,000	dr			Unit 6	old	new
8	Unit 6	500	800	dr
9	Unit 6	500	600	dr
10
11	old
Sheet2

 

[Eric W]

Well, by just modifying the previous formula, this would work:

=SUM(SUMIFS(B$2:B$9,$A$2:$A$9,IF(G$1:G$7="old",$F$1:$F$7,"xx"),$D$2:$D$9,"dr"))-SUM(SUMIFS(B$2:B$9,$A$2:$A$9,IF(G$1:G$7="old",$F$1:$F$7,"xx"),$D$2:$D$9,"cr"))

with CSE.

But on further reflection, this works as well and is shorter:

=SUM(IF(COUNTIFS($F$2:$F$7,$A$2:$A$9,$G$2:$G$7,"old"),$B$2:$B$9*IF($D$2:$D$9="cr",-1,1)))

with CSE.

 

[dogpile]

This is great, thanks so much!

 

[Eric W]

Glad to help!

 

[dogpile]

Now if only my computer doesn't sound like it's going to take off when I apply this formula to the much larger data I'm try to sum... it can't handle it!

 

[Eric W]

I don't know how to improve the efficiency of the formulas much more beyond what is there. You could try a UDF.

On a copy of your workbook, right click on the sheet tab on the bottom and select "View Code". From the VBA Editor menu bar, click Insert > Module. On the window that opens, paste this code:

Public Function SumSome(ByVal Tab1 As Range, ByVal Tab2 As Range, ByVal Tab3 As Range, ByVal Tab4 As Range, ByVal MyCode As String)
Dim MyData1 As Variant, MyData2 As Variant, MyData3 As Variant, MyDict As Object, r As Long

    If Tab1.Columns.Count > 1 Or _
       Tab2.Columns.Count > 1 Or _
       Tab3.Columns.Count > 1 Or _
       Tab4.Columns.Count <> 2 Or _
       Tab1.Rows.Count <> Tab2.Rows.Count Or _
       Tab1.Rows.Count <> Tab3.Rows.Count Then
       SumSome = "Invalid ranges in parameters"
       Exit Function
    End If
    
    MyData1 = Tab4.Value
    Set MyDict = CreateObject("Scripting.Dictionary")
    For r = 1 To UBound(MyData1)
        MyDict(MyData1(r, 1)) = MyData1(r, 2)
    Next r
    
    MyData1 = Tab1.Value
    MyData2 = Tab2.Value
    MyData3 = Tab3.Value
    For r = 1 To UBound(MyData1)
        If MyDict(MyData1(r, 1)) = MyCode Then SumSome = SumSome + IIf(MyData3(r, 1) = "cr", -1, 1) * MyData2(r, 1)
    Next r
    
End Function

Now go back to your Excel sheet and enter this formula:

=SumSome(A2:A9,B2:B9,D2:D9,F2:G7,"old")

That might work faster. Good luck!