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

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)
Solved by Barry on Apr 15, 2013 3:56 PM Solved

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 by Jerrold Green1 on Apr 16, 2013 11:48 AM Helpful

KW,

 

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

 

Jerry

Reply by Jerrold Green1 on Apr 17, 2013 7:53 AM Helpful

Look for the file on your Desktop.

 

Jerry

All replies

  • Barry Level 7 Level 7

    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

  • kwizs Level 1 Level 1

    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?

  • Jerrold Green1 Level 7 Level 7

    KW,

     

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

     

    Jerry

  • Barry Level 7 Level 7

    "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

     


  • kwizs Level 1 Level 1

    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?

  • Jerrold Green1 Level 7 Level 7

    Look for the file on your Desktop.

     

    Jerry

  • kwizs Level 1 Level 1

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

    Screen Shot 2013-04-17 at 8.44.43 AM.png

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

     

    -Kirk