sumifs

%3CLINGO-SUB%20id%3D%22lingo-sub-2613160%22%20slang%3D%22en-US%22%3Esumifs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2613160%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20insert%20the%20criteria%20argument%20in%20%22sumifs%22%20using%20a%20formula%20that%20references%20a%20simple%20formula%20tied%20to%20a%20value%20found%20in%20another%20cell.%26nbsp%3B%20%26nbsp%3BBut%20the%20result%20keeps%20returning%20zero%20(0).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20use%20the%20actual%20values%20with%20the%20formulas%20that%20are%20in%20the%20reference%20cells%20-%20the%20%22sumifs%22%20function%20works.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20thoughts%2Fsuggestions%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20I%20want%20to%20use%20that%20DOES%20NOT%20work%20is%3A%3C%2FP%3E%3CP%3E%3D-SUMIFS(B50%3AB649%2CA50%3AA649%2C%22%26gt%3BCK31%22%2CA50%3AA649%2C%22%26lt%3B%3DCL31%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20works%20if%20I%20insert%20the%20values%20referenced%20in%20Cells%20CK31%20and%20CL31%20and%20is%20as%20follows%3A%3C%2FP%3E%3CP%3E%3D-SUMIFS(B50%3AB649%2CA50%3AA649%2C%22%26gt%3B44%22%2CA50%3AA649%2C%22%26lt%3B%3D56%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20hoping%20to%20make%20it%20work%20with%20the%20cell%20addresses%20returning%20the%20contained%20values%20so%20I%20can%20copy%20the%20formula%20across%20to%20other%20cells%20in%20the%20row%20and%20have%20it%20adjust%20to%20the%20relative%20criteria%20test%20values.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20computer%20is%20a%202014%20MS%20Windows%20Surface%20Pro%203%20and%20my%20operating%20system%20is%20Windows%2010%20Pro%2C%20Ver%2020H2%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20very%20much%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2613160%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-2613225%22%20slang%3D%22en-US%22%3ERe%3A%20sumifs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2613225%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1120840%22%20target%3D%22_blank%22%3E%40JFrancoz%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20it%20like%20this...%3C%2FP%3E%3CP%3E%3D-SUMIFS(B50%3AB649%2CA50%3AA649%2C%3CSTRONG%3E%22%26gt%3B%22%26amp%3BCK31%3C%2FSTRONG%3E%2CA50%3AA649%2C%3CSTRONG%3E%22%26lt%3B%3D%22%26amp%3BCL31%3C%2FSTRONG%3E)%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I am trying to insert the criteria argument in "sumifs" using a formula that references a simple formula tied to a value found in another cell.   But the result keeps returning zero (0).

 

If I use the actual values with the formulas that are in the reference cells - the "sumifs" function works.

 

Any thoughts/suggestions?

 

The formula I want to use that DOES NOT work is:

=-SUMIFS(B50:B649,A50:A649,">CK31",A50:A649,"<=CL31")

 

The formula works if I insert the values referenced in Cells CK31 and CL31 and is as follows:

=-SUMIFS(B50:B649,A50:A649,">44",A50:A649,"<=56")

 

I am hoping to make it work with the cell addresses returning the contained values so I can copy the formula across to other cells in the row and have it adjust to the relative criteria test values.

 

Any ideas?

 

My computer is a 2014 MS Windows Surface Pro 3 and my operating system is Windows 10 Pro, Ver 20H2

 

Thanks very much

3 Replies

@JFrancoz 

Try it like this...

=-SUMIFS(B50:B649,A50:A649,">"&CK31,A50:A649,"<="&CL31)

@Subodh_Tiwari_sktneer 

 

Thank you - this worked

 

You're welcome @JFrancoz! Glad it worked as desired.

 

Please take a minute to accept the post with the proposed solution as a Best Response to mark your question as Solved.