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???

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 thisHow-To.

I am getting "#value" response when I apply the formula sugested above... Any suggestions why?

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 8A I J 1 2 ATLEXP open 1 3 NOT open 2 4 ATLEXP open 3 5 ATLEXP open 4With 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 4The 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 thisHow-To.

Help required please... Really struggling for more than a week to find this out. A1 B1 C1 D1 E1 F1 G1

Fruit Flower Component Weight 24-Mar-11 31-Mar-11 7-Apr-11

Apple 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

Ask Your Question

Weekly Poll

Do you think Google's new Pixel 4 will gain traction?

Discuss in The Lounge

Poll History