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
				Valid Codes
Alpha		Alpha
Bravo		Bravo
Green		Green
Car		Yellow
Mouse		Ice Cream
Green
Yellow
Hot
Ice Cream
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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]1001[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]1001[/TD]
[TD="align: right"]30[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]1001[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]1002[/TD]
[TD="align: right"]30[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]1001[/TD]
[TD="align: right"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]1001[/TD]
[TD="align: right"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]10[/TD]

</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