Results 1 to 15 of 15

20040219, 17:45 #1
 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

20040219, 18:11 #2
 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

20040219, 18:21 #3
 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.

20040219, 18:39 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20040219, 18:49 #5
 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?

20040219, 19:05 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20040219, 19:23 #7
 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

20040219, 19:31 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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?

20040219, 19:34 #9
 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

20040219, 19:43 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20040219, 20:31 #11
 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!

20040219, 20:35 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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?

20040219, 20:38 #13
 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.

20040219, 20:41 #14
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20040219, 22:38 #15
 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