sumif/sumproduct formula help

[Guest]

I am trying to use the sumif or sumproduct formula and am coming up with
either no data or incorrect data.

=SUMIF('2007-01'!$E$3:$E$500,B:B,'2007-01'!$I3:$I$500)/1000000 ---- Gives
back a zero

{=SUMPRODUCT(--('2007-01'!$E$3:$E$500,$B:$B,'2007-01'!$I$3:$I$500))/100000}
----- Gives back #value!


{=SUM(IF('2007-01'!$E$3:$E$500,$B:$B,'2007-01'!$I$3:$I$500))/100000} ----
Gives back a value of the entire column I and is ignoring the criteria in
$B:$B

Any help would be greatly appreciated.

Robert

 

[Trevor Shuttleworth]

Robert

B:B needs to be a single cell or a value. Have a look at the Help for SUMIF

The SUMIF compares each cell in the range E3 to E500 with *a* cell in column
B (for example) and, where there is a match, adds up the values in I3 to
I500.

You don't need to limit the range of cells as you would with SUMPRODUCT, so
you could have:

=SUMIF('2007-01'!$E:$E, B1, '2007-01'!$I:$I)/1000000

Or something like that.

Regards

Trevor

 

[Guest]

I forgot to mention that I am pulling information from other unopen excel
files so the sumif calc will not work. That is why I was trying the
sumproduct and sumif with the array. My same question still applies. Any
suggestions?