Sumif with multiple criteria in one column using named ranges

[Wgijbels]

Hi everbody

I am trying to use the SUMIFS-function for multiple criteria in one column where my criterias are 'named ranges' within this column.
Is there any possibility to do this?
Excel does not seem to accept named ranges as a criteria, f.e.:

=SUM(SUMIFS('Mapping GVV'!D:D;'Mapping GVV'!C:C;{Rent,Rentaldiscount;Rentfree}))

where Rent, Rentaldiscount and Rent free are named ranges within the criteria column.

Thank you very much in advance for any help.

Kind regards

W.

 

[jasonb75]

You can only enter numbers or text strings in array constants, not references to ranges (whether named or not) or other functions.

You could try (untested)

=SUM(SUMIFS('Mapping GVV'!D:D;'Mapping GVV'!C:C;CHOOSE({1\2\3};Rent;Rentaldiscount;Rentfree)))

Which I believe should work if the named ranges are single cells. It might work with larger ranges but I can't say for sure without testing it first.

 

[Wgijbels]

It works, you're amazing!

I can even use the SUMIF now.

 

[Wgijbels]

There is still something I don't understand.

If I use the function below:

and afterwards change the value of my named range 'Recuperatieverzekeringspremies' (from zero to 2).

Excel doubles the value (so +4).

Do you have any idea why this is?

Thank you again!

 

[Fluff]

It's because your ranges are not the same size, if you use the evaluate formula button on the ribbon & step through the calculation you will see what happens.
How about

+Fluff v2.xlsm
	A	B	C	D
1
2
3			a	3622
4			b	2842
5			d	-3622
6			d	-2842
7			c	2
8	6466
Main

Cell Formulas
Range	Formula
A8	A8	=SUM(FILTER(D3:D7,(COUNTIF(test1,C3:C7))+(COUNTIF(test2,C3:C7))))

Named Ranges
Name	Refers To	Cells
test1	=Main!$C$3:$C$4	A8
test2	=Main!$C$7	A8

 

[Wgijbels]

Thanks!!

 

[Fluff]

Glad we could help & thanks for the feedback.