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

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

Could you give a little more details please.

Steve

3. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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. 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
•