Solved Sumifs formula refer to field value

November 9, 2012 at 06:17:50
Specs: Windows 7
I have a simple sumifs formula which work well if I put a "=<5" value in criteria field. I want to refer to a field value to replace the"5" with different numeric values.

Sample : =SUMIFS(D136:D159,C136:C159,"=<5") This formula works.I want to replace the "=<5" with "=<C135" but don't know what the format should be when refering to field.

Thanks in advance


See More: Sumifs formula refer to field value

Report •

#1
November 9, 2012 at 07:48:41
✔ Best Answer
Your SUMIFS formula doesn't work. I assume you meant to use:

=SUMIFS(D136:D159,C136:C159,"<=5")

as in "less than or equal to" not "equal to or less than".

As for your question, try this:

=SUMIFS(D136:D159,C136:C159,"<=" & C135)

P.S. C135 is not a "field", it's a "cell" or a "range". Using the wrong terminology can be confusing, especially when the terms you use actually refer to something else that exists.


Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
November 9, 2012 at 09:07:32
Thanks a lot. I left out the "&" !! Sorry for the terminology confusion. Was working with a database at same time where fields are used. My apologies

Report •

#3
November 9, 2012 at 11:00:35
A minor point:

You said: I left out the "&"!

Make sure you are aware that the & and the cell reference go outside of the quotation marks. If you put them inside the quotes, Excel will pick them up as text and the formula won't work.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question