=sum with 2 criteria

[jason]

Hi,

I've read through various articles regarding this topic
and have tried the following formula

=SUM(('2003-5907_Salary_Summary.CSV'!A1:A200="089*")*
('2003-5907_Salary_Summary.CSV'!C1:C200="CR-04"),'2003-
5907_Salary_Summary.CSV'!D1200)

the problem I'm running into is that it's totalling the
whole column d1:d200 instead of picking the rows I want
baseed on the 2 criteria.

I used ctrl+shift+enter to make it an array but same
problem.

Is there something obvious i'm missing?

your help is greatly appreciated.

jason

 

[Jason Morin]

Try:

=SUM((LEFT('2003-5907_Salary_Summary.CSV'!A1:A20,3)="089")*
('2003-5907_Salary_Summary.CSV'!C1:C20="CR-04")*('2003-
5907_Salary_Summary.CSV'!D120))

Array-entered.

HTH
Jason
Atlanta, GA

 

[Harlan Grove]

the result I'm getting now is #NA. there is definatley a
value in the cell(s). I had tried something similiar to
this earlier and had the same result. I've even tried
converting the referenced sheet to an .xls but same answer.

any other suggestions would be greatly appreciated. ...

...

The only way the formula above returns #N/A is if one of the ranges involved
contains #N/A.

 

[immanuel]

Try:

=SUM(IF((LEFT('2003-5907_Salary_Summary.CSV'!A1:A200,3)="089")*
('2003-5907_Salary_Summary.CSV'!C1:C200="CR-04"),
'2003-5907_Salary_Summary.CSV'!D1200,0))

/i.