SOLVED

dynamic arrays

%3CLINGO-SUB%20id%3D%22lingo-sub-1631290%22%20slang%3D%22en-US%22%3Edynamic%20arrays%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1631290%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EWhen%20I%20select%20month%20name%20from%20drop%20down%20list%2C%20I%20want%20to%20get%20corresponding%20services%20related%20to%20this%20month%20from%20data%20table%20in%20cell%20J19.%20Which%20formula%20is%20best%20to%20resolve%20this%20issue%3F%20This%20formula%20does%20not%20work%20100%25.%20%3DIFS(((I8%3AI12%26gt%3B%3DC1)*(I8%3AI12%26lt%3B%3DEOMONTH(C1%2C0)))%2CN8%3AN12).%20The%20problem%20is%20that%20dynamic%20arrays%20move%20down.%20How%20I%20can%20stop%20it.%20Please%20watch%20gif%20image.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Untitled%20Project.gif%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F216289i9016D6C6AE5AE17F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Untitled%20Project.gif%22%20alt%3D%22Untitled%20Project.gif%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1631290%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-1633106%22%20slang%3D%22en-US%22%3ERe%3A%20dynamic%20arrays%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1633106%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F780463%22%20target%3D%22_blank%22%3E%40Mubeen1380%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20simplify%20formula%20for%20the%20date%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%20453px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F216370i9C8CF19CA6082BC5%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%3EArray%20formulas%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DFILTER(%24N%248%3A%24N%2412%2C(%24I%248%3A%24I%2412%26gt%3B%3D%24C%241*(%24I%248%3A%24I%2412%26lt%3B%3DEOMONTH(%24C%241%2C0))))%0A%0Aand%20%0A%0A%3DSUMIFS(%24O%248%3A%24O%2412%2C%24N%248%3A%24N%2412%2CJ19%23)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1636150%22%20slang%3D%22en-US%22%3ERe%3A%20dynamic%20arrays%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1636150%22%20slang%3D%22en-US%22%3E%3CP%3E%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%3C%2FP%3E%3CP%3EThank%20you%20sir.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

When I select month name from drop down list, I want to get corresponding services related to this month from data table in cell J19. Which formula is best to resolve this issue? This formula does not work 100%. =IFS(((I8:I12>=C1)*(I8:I12<=EOMONTH(C1,0))),N8:N12). The problem is that dynamic arrays move down. How I can stop it. Please watch gif image.

Untitled Project.gif

 

3 Replies

@Mubeen1380 

You may simplify formula for the date

image.png

Array formulas could be

=FILTER($N$8:$N$12,($I$8:$I$12>=$C$1*($I$8:$I$12<=EOMONTH($C$1,0))))

and 

=SUMIFS($O$8:$O$12,$N$8:$N$12,J19#)
Best Response confirmed by Mubeen1380 (Occasional Contributor)
Solution

@Sergei Baklan 

Thank you sir. 

@Mubeen1380 , you are welcome