I need help on a sumproduct if formula

November 15, 2010 at 09:11:59
Specs: Windows XP
 I am tryin to create a formula that says= "if column a=atlexp and column I=open, then add the dollar amounts in column J.=SUMPRODUCT(--(A2:A500="ATLEXP"),--(I2:I500="open")) this tells me the # of days open... but I need to add on a dollar amount, so do I add an IF to the formula???

See More: I need help on a sumproduct if formula

#1
November 15, 2010 at 10:12:12
 You don't need an IF, you just need to a different construction of your SUMPRODUCT formula.Note the use of the asterisk in place of the comma.=SUMPRODUCT((--(A2:A500="ATLEXP") * --(I2:I500="open")) * (J2:J500))Posting Tip: Before posting Data or VBA Code, read this How-To.

Report •

#2
November 15, 2010 at 11:16:45
 I am getting "#value" response when I apply the formula sugested above... Any suggestions why?

Report •

#3
November 15, 2010 at 15:32:15
 The formula works fine for me, as long as there are numbers or empty cells in J2:J500. If there is text anywhere in J2:J500, you'll get a #VALUE error.=SUMPRODUCT((--(A2:A500="ATLEXP") * --(I2:I500="open")) * (J2:J500))With this setup, the above formula returns 10``` A I J 1 2 ATLEXP open 1 3 ATLEXP open 2 4 ATLEXP open 3 5 ATLEXP open 4 ```With this setup, the above formula returns 8``` A I J 1 2 ATLEXP open 1 3 NOT open 2 4 ATLEXP open 3 5 ATLEXP open 4 ```With this setup, the above formula returns 6``` A I J 1 2 ATLEXP open 1 3 ATLEXP open 2 4 ATLEXP open 3 5 ATLEXP NOT 4 ```The only way i can get a #VALUE error is if I have text in any cell in J2:J500.Posting Tip: Before posting Data or VBA Code, read this How-To.

Report •

Related Solutions

#4
April 14, 2011 at 00:13:28
 Help required please... Really struggling for more than a week to find this out. A1 B1 C1 D1 E1 F1 G1Fruit Flower Component Weight 24-Mar-11 31-Mar-11 7-Apr-11Apple Rose A 108 30% 100% 100%Mango Sunflower B 108 0% 25% 65%Banana Lily C 1200 0% 0% 0%Jack Sunflower D 1200 0% 25% 25%Pears Sunflower E 128 0% 0% 0%Mango Lily C 128 0% 0% 0%Banana Lily A 128 0% 0% 0%Apple Rose B 62.5 0% 0% 0%The attached table is my input, The output required is sumproduct of column D1 and Max(E1:G1), provided the column A1 must refer only to "Apple" and the date should be less than or equal to 31-Mar-11 in Row 1

Report •