Sumifs with a dynamic list criteria

[Geir Hogstad]

Hi

I am trying to use Sumifs ehre one of the criteria is from a dynamic list. It fills down the correct number of rows, but only choose the first item from the dynamic list. 

Command: 

=SUMIFS(Tabell1[Omsetning];Tabell1[Selger];Selgere!B20#;Tabell1[Kvartal];2;Tabell1[Produkt];Selgere!$B$16)

The list starting in B20 is buildt with Sort(Unic function.

All numbers is for "Hansen" Any solution for this?

Best Regards

- Geir

[#2]

@Geir Hogstad 

Geir, perhaps you may illustrate on the sample file, not on screenshot what's wrong. Here it looks like works:

File is attached.

[#3]

@Geir Hogstad 

Looks fine syntactically to me. Can you upload a workbook?

Regards

[#4]

@Geir Hogstad 

I update criteria with spill, the same

[#5]

Hi @Sergei Baklan please find attached a copu of the Worksheet, where I try to use an dynamic list,instead of fixed values.

[#6]

Thank you for your interest @Jos_Woolley please find an example in the thread here.

- Geir

[#7]

@Geir Hogstad 

I see. Please remove sheet name from the spill reference

when it'll be like

I do one by one it's the same result.

[#8]

The function is correct @Sergei Baklan :) I did a double check with fixed values. And they do have the same values. Sorry. I used RANDARRAY to generate numbers. And I did not think it would be some identical numbers there, for the same product.

Thank you for helping.

- Geir

[#9]

You are correct @Jos_Woolley the syntax is fine. It works as it is.

Thank you.

- Geir

[#10]

@Geir Hogstad , you are welcome

To be more clear, you may use Selgere!B20# or B20#, result will be exactly the same. Just for the formula in first case only first cell will be highlighted in the spill and in second case entire spill.