7 Replies Latest reply: Apr 17, 2013 9:57 AM by kwizs
Level 1 (0 points)

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)
• ###### 1. Re: proper syntax of a condition argument containing a cell reference and a comparison operator
Level 7 (29,180 points)

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

• ###### 2. Re: proper syntax of a condition argument containing a cell reference and a comparison operator
Level 1 (0 points)

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?

• ###### 3. Re: proper syntax of a condition argument containing a cell reference and a comparison operator
Level 7 (28,995 points)

KW,

A cell reference is an object, not text. That's why it can't be part of the string.

Jerry

• ###### 4. Re: proper syntax of a condition argument containing a cell reference and a comparison operator
Level 7 (29,180 points)

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

There are four types of screen shots. Procedure for all is similar:

 Screen Shots To file To clipboard Full screen Press shift-command-3 Press shift-control-command-3. Portion of screen Press shift-command-4. Drag to enclose the desired portion of the screen. Release mouse button to take the shot. Press shift-control-command-4 Drag to enclose the desired portion of the screen. Release mouse button to take the shot.

The one on the bottom left is suitable for inserting in this forum. Click the camera icon above the composition space, then follow the prompts.

Regards,

Barry

• ###### 5. Re: proper syntax of a condition argument containing a cell reference and a comparison operator
Level 1 (0 points)

I pressed the keys, dragged the mouse to enclose the portion of screen, released the mouse, and nothing happened. How do I save the shot to a file?

• ###### 6. Re: proper syntax of a condition argument containing a cell reference and a comparison operator
Level 7 (28,995 points)

Look for the file on your Desktop.

Jerry

• ###### 7. Re: proper syntax of a condition argument containing a cell reference and a comparison operator
Level 1 (0 points)

Of course, I should have thought of that. Here's a successful screen shot of my successful formula:

Victory is ours. Thanks, guys, you've been great.

-Kirk