Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    May 2002
    Loveland, Ohio, USA
    Thanked 0 Times in 0 Posts

    sumif Function (Office 2002 SP2)

    I have a spreadsheet with 3 columns that have the following in them:

    Quantity Code Revenue

    I have done a sumif of the revenue based on the code value, and a sumif on the Quantity based on the code value. The last thing I would like to do is to do a sumif on the Quantity based on Code Value for those lines that have revenue greater than 0.

    I tried to do this with the following:
    =SUMIF($G$2:$G$180,"=VSC41105 && H2:h180>0",$F$2:$F$180)

    the middle entry in the ()'s is the problem area. How can I do this?

    I know that I can create a hidden column where I put the quantity values only if there is no-zero revenue, but I wanted to avoid doing that. Any thoughts?



  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    South Carolina, USA
    Thanked 0 Times in 0 Posts

    Re: sumif Function (Office 2002 SP2)

    I could not figure out from your formual exactly what your spreadsheet looks like. So, I will have to show you how to do it using a different setup.

    You can't do this with SUMIF since it can not handle more than one condition. You will need an array formula. If the Quantity is in A2:A180, and the Code is in B2:B180, and the Revenue is in C2:C180, then the following array formula will sum the revenue where the Quantity=1, the Code="A", and the Revenue>0:

    <pre>=SUM((A2:A180=1)*(B2:B180="A")*(C2:C180>0)*C2 :C180)

    Again, this is an Array Formula, so you must hold down the Ctrl and Shift keys when you press the Enter key to enter the formula.
    Legare Coleman

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: sumif Function (Office 2002 SP2)

    I think you want (ARRAY confirm with ctrl-shift-enter):

    <pre>=SUM(IF(($G$2:$G$180="VSC41105")*(H2:h180>0), $F$2:$F$180))</pre>

    This will give you the sum of the items in F2:F180 where the rows in G2:G180 = "VSC41105" AND the values in H2:H180>0


Posting Permissions

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