proper syntax of a condition argument containing a cell reference and a comparison operator

Question

Level 1

0 points

proper syntax of a condition argument containing a cell reference and a comparison operator

I'm using a SUMIFS function to compile the values of tools bought for my business in a given year. I don't know how to upload a screenshot, as I see has been done in some other discussions, so I'll make do with pasting my formulas and trying to describe what I'm doing.

=SUMIFS(Itemized Listing :: B,Itemized Listing :: ACQUIRED,A2,DISPOSED,"")

This is the basic formula I'm using, and it works as it is. The first condition stipulates that the value in the test range (Itemized Listing :: ACQUIRED) equals the value in (A2). The problem arises when I try to include a comparison operator in the argument:

=SUMIFS(Itemized Listing :: B,Itemized Listing :: ACQUIRED,<A2,DISPOSED,"")

returns a syntax error

=SUMIFS(Itemized Listing :: B,Itemized Listing :: ACQUIRED,"<A2",DISPOSED,"")

returns a syntax error

=SUMIFS(Itemized Listing :: B,Itemized Listing :: ACQUIRED,"<"A2,DISPOSED,"")

returns a syntax error

=SUMIFS(Itemized Listing :: B,Itemized Listing :: ACQUIRED,<"A2",DISPOSED,"")

returns a syntax error

It's got me stumped. Help, please?

iMac, Mac OS X (10.7.5)

Posted on Apr 15, 2013 3:18 PM

Reply

Answer 1

Best reply

Barry

Level 9

59,861 points

Apr 15, 2013 3:56 PM in response to kwizs

Try this:

=SUMIFS(Itemized Listing :: B,Itemized Listing :: ACQUIRED,"<"&A2,Itemized Listing :: DISPOSED,"")

I've assumed that DISPOSED is a column on the itemized listing table.

Conditions must be expressed as a text string. the quotes ensure that < is recognized as text, not an operator. The concatenation operatot ( & ), outside the quotes, appends the contents of A2 to the text string.

I haven't checked on the second condition.

Regards,

Barry

Reply

Answer 2

kwizs Author

Level 1

0 points

Apr 16, 2013 10:09 AM in response to Barry

Thank you, that works. I'm still not sure why the concatenation function is necessary; it seems that a cell reference should be able to be part of a string. Whatever, I'm glad to have the problem solved, I just thought the answer would be a bit more intuitive.

A tangential question: Do you have a quick explanation for how to include a screenshot as a demo in these question forms?

Reply