Results 1 to 7 of 7

Thread: sumif (2000)

  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    sumif (2000)

    In column A I have listed 50 inventory items. In column B I have their respective prices. I want to add the respective prices of, say, three of the inventory items: Widgets, screwdrivers, & hammers. Both columns are 50 rows in length. It has to be any easy answer, but it just escapes me now. Any help?

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sumif (2000)

    How would you like to identify the items.

    By entering the names in a separate list?
    By using a checkbox?
    By entering some text in an adajenct cell?
    Etc

    Please let us know as it will affect the solution.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: sumif (2000)

    If the 50 items are unique, you can use three VLOOKUP expressions:

    =VLOOKUP("Widget",A2:B51,2,FALSE)+VLOOKUP("Screwdr iver",A2:B51,2,FALSE)+VLOOKUP("Hammer",A2:B51,2,FA LSE)

    (spelling of the items must correspond exactly to that in the list; I have assumed that your data are in A2:B51 with A1:B1 as field names)

    If the items are not unique (i.e. there are multiple entries for widgets etc.), you can use three SUMIF expressions:

    =SUMIF(A2:A51,"Widget",B2:B51)+SUMIF(A2:A51,"Screw driver",B2:B51)+SUMIF(A2:A51,"Hammer",B2:B51)

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: sumif (2000)

    Thanks, Hans-I got all balled up when I tried combining all three SUMIFs into one instead of separating them out!

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: sumif (2000)

    One further question: assume the same facts as originally, but that the data are moved over one column so that the names of the products (Widget, Screwdriver, etc.) are in column B and their respective prices in column C. Now, column A lists the dates the products were acquired. Question: how do I include, say, all of the inventory/products acquired between January and May, inclusive, and exclude any product acquired after May 31?

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: sumif (2000)

    How about this ARRAY (confirm wiht ctrl-shift-enter), I think I got all the parentheses in it)

    =sum(if(((B2:B51="Widget")+(B2:B51="Screwdriver")+ (B2:B51="Hammer"))*(A2:A51>=date(2003,1,1))*(A2:A5 1<date(2003,6,1)),c2:c51))

    Steve

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: sumif (2000)

    Steve-works great.
    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •