SumIf with criteria in array

[Hermanie]

Hi all,

I'm looking for a formula which is able to sum values with criteria which are in an specified array. I'm looking for this formula so I can add a criteria in the array, withouth adding another SUMIF to the original SUMIF formula, so it won't look like SUMIF(criteria1)+SUMIF(criteria2)+SUMIF(criteria3) etc.

Example:

The array A1:A5 has 2 fields with texts. The criteria range is column B and the sum value in column C. I would like to have a formula which looks at all the fields in the array and sums the every matched criteria. So if I want to add a criteria in A3, I will not have to edit the formulas because it's looking at the array A1:A5.

Please note; it doesn't matter if the array is horizontal or vertical, whatever suites best (so A1:A5 or A1:E1)

Thanks in advance!

 

[DHayes]

Have you tried SUMPRODUCT with this you can use multiple criteria's. if you give an example of what you wan't to achieve we can help.

 

[Hermanie]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Row[/TD]
[TD]Column A
[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Bank[/TD]
[TD][/TD]
[TD]Bank[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Car[/TD]
[TD][/TD]
[TD]House[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]-[/TD]
[TD][/TD]
[TD]Car[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]-[/TD]
[TD][/TD]
[TD]Bank[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]-[/TD]
[TD][/TD]
[TD]Garden[/TD]
[TD]25[/TD]
[/TR]
</tbody>[/TABLE]

So what I want is 1 formula in which the specific array with criteria in Column A, cells 1 to 5. The sum range is column D, the criteria range column C. So with this example the output should be 40, as Bank and Car matched in column C and sums to 40. When I add Garden in cell A3, the formula should return 65 because it's looking at the criteria in array A1:A5.

I'm too unfamiliar with SUMPRODUCT unfortunately.

 

[Weazel]

maybe something like

Unknown[TABLE="class: grid, width: 300"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Bank[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Bank[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Car[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]House[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]-[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Car[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]-[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Bank[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]-[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Garden[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F1[/TH]
[TD="align: left"]=SUMPRODUCT(SUMIF(C1:C5,A1:A5,D1:D5))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Hermanie]

This is perfect, thank you very much!