Sumproduct Binary - multiple criteria in the same range

[kaiser_Soze]

I need to sum deposits for each account number for deposits that have valid codes. I've tried SUMIFS and SUMPRODUCT, and run into the same problem of setting criteria to a range of values (E2:E6).

Here is where i'm at from a SUMPRODUCT path:
=SUMPRODUCT(--($A:$A=$G2),--($C:$C=$E$2:$E$6),$B:$B)

Is there a way to modify the second sumproduct binary (--) to sum only the range of valid codes?
Yes, the easy way is the make 5 of them for the 5 codes, but, the real data set I'm working with has like 100 valid codes so I don't have that luxury.

 

[Peter_SSs]

Welcome to the MrExcel board!

For future reference, you will get many more potential helpers (& faster) if you post any sample data in small copyable screen shots directly in your post. Most helpers aren't so keen on having to type out sample data,
My signature block below has a link for several ways to do that. Test them in the Test Here forum. That way, if something goes wrong, you won’t be messing up a main forum.

I would also avoid using whole column references with SUPMPRODUCT. Choose a number of rows that will be sufficient to cover any amount of data you will realistically ever have.

<b>SUMPRODUCT</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:47px;" /><col style="width:33px;" /><col style="width:78px;" /><col style="width:24px;" /><col style="width:89px;" /><col style="width:18px;" /><col style="width:47px;" /><col style="width:33px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Valid Codes</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; text-align:right; ">1001</td><td style="font-size:10pt; text-align:right; ">10</td><td style="font-size:10pt; ">Alpha</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Alpha</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">1001</td><td style="font-size:10pt; text-align:right; ">30</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; text-align:right; ">1001</td><td style="font-size:10pt; text-align:right; ">10</td><td style="font-size:10pt; ">Bravo</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Bravo</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">1002</td><td style="font-size:10pt; text-align:right; ">30</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; text-align:right; ">1001</td><td style="font-size:10pt; text-align:right; ">10</td><td style="font-size:10pt; ">Green</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Green</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; text-align:right; ">1001</td><td style="font-size:10pt; text-align:right; ">10</td><td style="font-size:10pt; ">Car</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Yellow</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; text-align:right; ">1002</td><td style="font-size:10pt; text-align:right; ">10</td><td style="font-size:10pt; ">Mouse</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; ">Ice Cream</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; text-align:right; ">1002</td><td style="font-size:10pt; text-align:right; ">10</td><td style="font-size:10pt; ">Green</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; text-align:right; ">1002</td><td style="font-size:10pt; text-align:right; ">10</td><td style="font-size:10pt; ">Yellow</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; text-align:right; ">1002</td><td style="font-size:10pt; text-align:right; ">10</td><td style="font-size:10pt; ">Hot</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; text-align:right; ">1002</td><td style="font-size:10pt; text-align:right; ">10</td><td style="font-size:10pt; ">Ice Cream</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:10pt; text-align:right; ">1002</td><td style="font-size:10pt; text-align:right; ">10</td><td style="font-size:10pt; ">Dog</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >H2</td><td >=SUMPRODUCT(--<span style=' color:008000; '>($A$2:$A$20=$G2)</span>,--<span style=' color:008000; '>(ISNUMBER<span style=' color:#0000ff; '>(MATCH<span style=' color:#ff0000; '>($C$2:$C$20,$E$2:$E$6,0)</span>)</span>)</span>,$B$2:$B$20)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[kaiser_Soze]

Welcome to the MrExcel board!

For future reference, you will get many more potential helpers (& faster) if you post any sample data in small copyable screen shots directly in your post. Most helpers aren't so keen on having to type out sample data,
My signature block below has a link for several ways to do that. Test them in the Test Here forum. That way, if something goes wrong, you won’t be messing up a main forum.

I would also avoid using whole column references with SUPMPRODUCT. Choose a number of rows that will be sufficient to cover any amount of data you will realistically ever have.

SUMPRODUCT

	A	B	C	D	E	F	G	H
1					Valid Codes
2	1001	10	Alpha		Alpha		1001	30
3	1001	10	Bravo		Bravo		1002	30
4	1001	10	Green		Green
5	1001	10	Car		Yellow
6	1002	10	Mouse		Ice Cream
7	1002	10	Green
8	1002	10	Yellow
9	1002	10	Hot
10	1002	10	Ice Cream
11	1002	10	Dog

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:47px;"><col style="width:33px;"><col style="width:78px;"><col style="width:24px;"><col style="width:89px;"><col style="width:18px;"><col style="width:47px;"><col style="width:33px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula H2 =SUMPRODUCT(--($A$2:$A$20=$G2),--(ISNUMBER(MATCH($C$2:$C$20,$E$2:$E$6,0))),$B$2:$B$20) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web - Excel Jeanie Html 4

Thank you!
Is it possible to modify the formula to sum only negative column B values (<0)?

 

[Peter_SSs]

Is it possible to modify the formula to sum only negative column B values (<0)?

I assume that you mean to still include the other conditions. If so:

=SUMPRODUCT(--($A$2:$A$20=$G2),--(ISNUMBER(MATCH($C$2:$C$20,$E$2:$E$6,0))),--($B$2:$B$20<0),$B$2:$B$20)


BTW, best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only.

 

[mohadin]

Hi
You may like with code

Sub test()
    Dim a As Variant, i
    Dim d As Object
    a = Range("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row).Resize(, 3)
    x = InStr(1, Join(Application.Transpose(Range("e2").Resize(Cells(Rows.Count, 5).End(xlUp).Row - 1))), Range("e2"))
   Set d = CreateObject("scripting.dictionary")
        For i = 1 To UBound(a)
         x = InStr(1, Join(Application.Transpose(Range("e2").Resize(Cells(Rows.Count, 5).End(xlUp).Row - 1))), a(i, 3))
            If a(i, 3) <> "" And x Then
                If Not d.exists(a(i, 1)) Then
                    d.Add a(i, 1), a(i, 2)
                Else
                    d.Item(a(i, 1)) = d.Item(a(i, 1)) + a(i, 2)
                End If
            End If
        Next
        With [a1].Offset(1, 6).Resize(d.Count, 2)
        .Value = Application.Transpose(Array(d.keys, d.items))
    End With
End Sub