Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Feb 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    I need a SUM IF and PRODCUT in the same formula (excel 2000)

    I am working in excel and dont know how to put a SUMPRODUCT as one of the variables in the IF

  2. #2
    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: I need a SUM IF and PRODCUT in the same formula (excel 2000)

    Could you give a little more details please.

    Steve

  3. #3
    New Lounger
    Join Date
    Feb 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: I need a SUM IF and PRODCUT in the same formula (excel 2000)

    I have this formula:
    =SUMIF($AJ$30:$AJ$739,$G$753, J$30:J$739)
    where column AJ looks for a value to match G753. i do not want it to return j30:j739. Instead I want the numbers in the J column to be mulitplied by numbers in another column.

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

    Re: I need a SUM IF and PRODCUT in the same formula (excel 2000)

    You can use this array formula (confirm with Ctrl+Shift+Enter instead of just Enter):

    =SUM((AJ30:AJ739=$G$753)*J30:J739*K30:K739)

    where K30:K739 is the "another column" you want to multiply with. The explanation for this formula is that AJ30:AJ739=$G$753 returns an array of TRUE/FALSE values, and in calculations, TRUE = 1 and FALSE = 0. The values from the other columns are multiplied by 1 i.e. included if there is a match in column AJ, and multiplied by 0 i.e. ignored if there is no match.

  5. #5
    New Lounger
    Join Date
    Feb 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: I need a SUM IF and PRODCUT in the same formula (excel 2000)

    dosen't seem to be working. will this only multiply the values in K and J for the rows in which AJ=G753?

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

    Re: I need a SUM IF and PRODCUT in the same formula (excel 2000)

    Yes, if you "save" the formula by pressing Ctrl+Shift+Enter. If it is not what you want, please try to explain in more detail what you expect the formula to do.

  7. #7
    New Lounger
    Join Date
    Feb 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: I need a SUM IF and PRODCUT in the same formula (excel 2000)

    i want J30:J739 to be mulitplied by K30:K739 IF AJ30:AJ739 =G753
    If AJ30:AJ739=G753, i only want J and K to be mulitplied in the rows where AJ=G753

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

    Re: I need a SUM IF and PRODCUT in the same formula (excel 2000)

    You don't mention a SUM any more. Is that intentional?

  9. #9
    New Lounger
    Join Date
    Feb 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: I need a SUM IF and PRODCUT in the same formula (excel 2000)

    this is where i am not sure..... do i need to use a SUM? i am not really "summing" anything..... I am multiplying the values in 2 columns but only if the number in a third column equals G753

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

    Re: I need a SUM IF and PRODCUT in the same formula (excel 2000)

    Since the subject of your post is "I need a SUM IF and PRODCUT in the same formula", you shouldn't be surprised if you get a reply that involves a SUM.

    Try this instead: in cell L30, enter this formula:<pre>IF(AJ30=$G$753,J30*K30,"")</pre>

    Confirm by clicking the green check to the left of the formula in the formula bar. This will leave cell L30 selected. You will see a black square in the lower right corner of the cell. This is called the fill grip. Double click this fill grip to fill your formula down to L739.

    If this is still not what you want, you will have to explain again.

  11. #11
    New Lounger
    Join Date
    Feb 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: I need a SUM IF and PRODCUT in the same formula (excel 2000)

    ok this works, however, i forgot to mention 1 thing... this is what's giving me the hardest time. i want to do this for column J but also for columns H and I. Meaning that I also want to multiply columns H and I by column K if AJ=G753
    Thanks!

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

    Re: I need a SUM IF and PRODCUT in the same formula (excel 2000)

    Is it so hard to replace J30 by H30 or by I30 in the formula (in another cell), or am I misunderstanding the question again?

  13. #13
    New Lounger
    Join Date
    Feb 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: I need a SUM IF and PRODCUT in the same formula (excel 2000)

    not hard at all, but i wanted to know if i could do this all in one cell.

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

    Re: I need a SUM IF and PRODCUT in the same formula (excel 2000)

    You want to multiply all of them in one cell? Use H30*I30*J30*K30 in the formula instead of just J30*K30.

  15. #15
    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: I need a SUM IF and PRODCUT in the same formula (excel 2000)

    In addition to the method you described:
    <pre>=Product(H30:K30) </pre>


    Will also do it and it easier on the typing/selecting.

    Steve

Posting Permissions

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