Sum (IF

[Kathy Goldie]

Not sure if the 1st posting ever was posted - I can not see it - I
apoligize itf this is a 2nd posting

I can not get t his to work
=SUM(IF($E$1:$E$20,"=1",IF($I$1:$I$20,"=2",$A$1:$A$20)))

per the below help text this is excatly what I want to do
If column is = 1 and column i = 2 sum of the value in column A

Creates a total value for one range based on two conditions.
For example, you want to calculate the total value of the cells
in F5:F25 where B5:B25 contains "Northwind" and the range
C5:C25 contains the region name "Western".


=SUM(IF(B5:B25="Northwind",
IF(C5:C25="Western",F5:F25)))


*** Sent via Developersdex http://www.developersdex.com ***

 

[Jim Thomlinson]

I think you are looking for a sumproduct formula...

=SUMPRODUCT(($E$1:$E$20=1)*($I$1:$I$20=2)*$A$1:$A$20)

Check out this link...
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Kathy Goldie]

=SUMPRODUCT(('reference data'!$E$2:$E$125=1)*('reference
data'!$I$2:$I$125=1)*('reference data'!$A$2:$A$125))


this word - here is the formula I used
THANK YOU!



*** Sent via Developersdex http://www.developersdex.com ***

 

[Casey]

Kathy,
Try this array formula. After you type in the formula enter it b
using Ctrl>Shift>Enter and it should look like this.

{=SUM(IF($E$1:$E$20=1,IF($I$1:$I$20=2,$A$1:$A$20),0))}

HT