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

Report •

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


Report •

Ask Question