Sumifs with a dynamic list criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-1442506%22%20slang%3D%22en-US%22%3ESumifs%20with%20a%20dynamic%20list%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1442506%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20use%20Sumifs%20ehre%20one%20of%20the%20criteria%20is%20from%20a%20dynamic%20list.%20It%20fills%20down%20the%20correct%20number%20of%20rows%2C%20but%20only%20choose%20the%20first%20item%20from%20the%20dynamic%20list.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECommand%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMIFS(Tabell1%5BOmsetning%5D%3BTabell1%5BSelger%5D%3B%3CSTRONG%3ESelgere!B20%23%3C%2FSTRONG%3E%3BTabell1%5BKvartal%5D%3B2%3BTabell1%5BProdukt%5D%3BSelgere!%24B%2416)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20list%20starting%20in%20B20%20is%20buildt%20with%20Sort(Unic%20function.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22sumifs%20dynamic.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F196690i173EF8698D9F1E9B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22sumifs%20dynamic.PNG%22%20alt%3D%22sumifs%20dynamic.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAll%20numbers%20is%20for%20%22Hansen%22%20Any%20solution%20for%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20Regards%3C%2FP%3E%3CP%3E-%20Geir%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1442506%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1442656%22%20slang%3D%22en-US%22%3ERe%3A%20Sumifs%20with%20a%20dynamic%20list%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1442656%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9195%22%20target%3D%22_blank%22%3E%40Geir%20Hogstad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EGeir%2C%20perhaps%20you%20may%20illustrate%20on%20the%20sample%20file%2C%20not%20on%20screenshot%20what's%20wrong.%20Here%20it%20looks%20like%20works%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20465px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F196701i2C45AF4034F8080F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EFile%20is%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1442677%22%20slang%3D%22en-US%22%3ERe%3A%20Sumifs%20with%20a%20dynamic%20list%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1442677%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9195%22%20target%3D%22_blank%22%3E%40Geir%20Hogstad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELooks%20fine%20syntactically%20to%20me.%20Can%20you%20upload%20a%20workbook%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1442771%22%20slang%3D%22en-US%22%3ERe%3A%20Sumifs%20with%20a%20dynamic%20list%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1442771%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9195%22%20target%3D%22_blank%22%3E%40Geir%20Hogstad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20update%20criteria%20with%20spill%2C%20the%20same%3C%2FP%3E%0A%3CDIV%20id%3D%22tinyMceEditorSergei%20Baklan_0%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20487px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F196709iB4D38C8C80DCD121%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1443120%22%20slang%3D%22en-US%22%3ERe%3A%20Sumifs%20with%20a%20dynamic%20list%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1443120%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bplease%20find%20attached%20a%20copu%20of%20the%20Worksheet%2C%20where%20I%20try%20to%20use%20an%20dynamic%20list%2Cinstead%20of%20fixed%20values.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1443122%22%20slang%3D%22en-US%22%3ERe%3A%20Sumifs%20with%20a%20dynamic%20list%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1443122%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20your%20interest%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676180%22%20target%3D%22_blank%22%3E%40Jos_Woolley%3C%2FA%3E%26nbsp%3Bplease%20find%20an%20example%20in%20the%20thread%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-%20Geir%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1443275%22%20slang%3D%22en-US%22%3ERe%3A%20Sumifs%20with%20a%20dynamic%20list%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1443275%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9195%22%20target%3D%22_blank%22%3E%40Geir%20Hogstad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20see.%20Please%20remove%20sheet%20name%20from%20the%20spill%20reference%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20696px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F196745iCA72A1B82B2DF32A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewhen%20it'll%20be%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20741px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F196746i6C4BD8CE57D325D7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EI%20do%20one%20by%20one%20it's%20the%20same%20result.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1443319%22%20slang%3D%22en-US%22%3ERe%3A%20Sumifs%20with%20a%20dynamic%20list%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1443319%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20function%20is%20correct%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20I%20did%20a%20double%20check%20with%20fixed%20values.%20And%20they%20do%20have%20the%20same%20values.%20Sorry.%20I%20used%20RANDARRAY%20to%20generate%20numbers.%20And%20I%20did%20not%20think%20it%20would%20be%20some%20identical%20numbers%20there%2C%20for%20the%20same%20product.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20helping.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-%20Geir%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1443323%22%20slang%3D%22en-US%22%3ERe%3A%20Sumifs%20with%20a%20dynamic%20list%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1443323%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20are%20correct%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676180%22%20target%3D%22_blank%22%3E%40Jos_Woolley%3C%2FA%3E%26nbsp%3Bthe%20syntax%20is%20fine.%20It%20works%20as%20it%20is.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-%20Geir%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1443515%22%20slang%3D%22en-US%22%3ERe%3A%20Sumifs%20with%20a%20dynamic%20list%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1443515%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9195%22%20target%3D%22_blank%22%3E%40Geir%20Hogstad%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%0A%3CP%3ETo%20be%20more%20clear%2C%20you%20may%20use%26nbsp%3B%3CSTRONG%3ESelgere!B20%23%3C%2FSTRONG%3E%20or%20%3CSTRONG%3EB20%23%3C%2FSTRONG%3E%2C%20result%20will%20be%20exactly%20the%20same.%20Just%20for%20the%20formula%20in%20first%20case%20only%20first%20cell%20will%20be%20highlighted%20in%20the%20spill%20and%20in%20second%20case%20entire%20spill.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Frequent Contributor

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.

sumifs dynamic.PNG

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

 

Best Regards

- Geir

 

9 Replies
Highlighted

@Geir Hogstad 

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

image.png

File is attached.

Highlighted

@Geir Hogstad 

 

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

 

Regards

Highlighted

@Geir Hogstad 

I update criteria with spill, the same

 

image.png

Highlighted

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

Highlighted

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

 

- Geir

Highlighted

@Geir Hogstad 

I see. Please remove sheet name from the spill reference

image.png

when it'll be like

image.png

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

Highlighted

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

Highlighted

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

 

Thank you.

 

- Geir

Highlighted

@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.