SUMIFS function with Named Range

[Bjordion]

Hello,

I would like to use the SUMIFS function to sum the values of multiple cells that meet one criteria that falls within a Named Range I have already established on the worksheet.

Ex: Named Range "Income" contains text B01, B02, B03...B16. If any one of those text strings are present I would like the corresponding value returned for it.

My current formula is:

=SUMIFS(Sheet1!I:I,Sheet1!A:A,Income)

Sheet1!I:I is the value to be returned
Sheet1!A:A has the text strings B01, B02, B03, etc.

***There are additional criteria conditions to be met in the formula (which is why I am using SUMIFS rather than OR) but I have excluded them for the sake of simplicity of this question.

TIA

Bjordion

[daddylonglegs]

Because Income is a range your formula returns an array (one sum for every value in income), so for the total you need to sum that, try

=SUMPRODUCT(SUMIFS(Sheet1!I:I,Sheet1!A:A,Income))

[Richard Buttrey]

The word 'Income' in the formula needs to be a literal. i.e. "Income" not Income

Regards

[Bjordion]

That did the trick, thank you much!