sumifs

[JFrancoz]

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

[#2 · Accepted solution]

@JFrancoz 

Try it like this...

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

[#3]

@Subodh_Tiwari_sktneer 

Thank you - this worked

[#4]

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.