SOLVED

sumifs

Copper 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
best response confirmed by allyreckerman (Microsoft)
Solution

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

 

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@JFrancoz 

Try it like this...

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

View solution in original post