Is it possible to create a form of SUMIF type of formula with criteria defined as a range?

[ppilot]

I wasn't sure if there was a way so create some kind of conditional summation formula that that can use a range of cells as the criteria instead of having to define each one as you do in SUMIFS.

[gmr4evr1]

I think we would need more info to be able to determine if it would work or not.
What range?
What is the condition?

[daddylonglegs]

If you use a range as the criteria in SUMIF then the result is an array so you need another function to sum that array, e.g. this formula

=SUMPRODUCT(SUMIF(A2:A100,C4:C7,B2:B100))

That will sum column B whenever any C4:C7 value occurs in the corresponding row in column A

If any values are repeated in C4:C7 then you will also sum the relevant rows multiple times.

An alternative without double counting:

=SUMPRODUCT(ISNUMBER(MATCH(A2:A100,C4:C7,0))+0,B2:B100)

[ppilot]

I think we would need more info to be able to determine if it would work or not.
What range?
What is the condition?

So I've attached a sample spreadsheet. Basically given the certain budget account in the Comparison tab, I want use the corresponding IS accounts on the Mapped Accounts Tab to act as the criteria for some kind of Sum function pulling the numbers from the IS tab.

Does that make sense?

[ppilot]

If you use a range as the criteria in SUMIF then the result is an array so you need another function to sum that array, e.g. this formula

=SUMPRODUCT(SUMIF(A2:A100,C4:C7,B2:B100))

That will sum column B whenever any C4:C7 value occurs in the corresponding row in column A

If any values are repeated in C4:C7 then you will also sum the relevant rows multiple times.

An alternative without double counting:

=SUMPRODUCT(ISNUMBER(MATCH(A2:A100,C4:C7,0))+0,B2:B100)

Ok I think we are definitely on the right track here as that first formula lets me use a range as the criteria. Now that hard part is finding the most elegant solution described in my above post. I might just add a fourth tab total all the numbers for the given budget accounts, and then use a VLookup to pull that to the comparison page.