Sumif errors

[Guest]

I have tried a few different formulas but have not got the results I expected.

=SUMPRODUCT(--('2007-01'!$E$3:$E$500,$B:$B,'2007-01'!$I$3:$I$500))/1000000
---- I get #Value!

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

=SUM(IF('2007-01'!$E$3:$E$500,$B:$B,'2007-01'!$I$3:$I$500))/1000000 ---- I
get a value equal to the entire column...it is not picking up the
$B:$B...seems like $B;$B is a dead cell and no matter what I put there is not
being read. I have tried to change the cell formatting but nothing seems to
work

 

[T. Valko]

The problem is that B:B part.

You're comparing the range E3:E500 with the entire column B. Typically, you
want to compare the range E3:E500 with just a single cell from column B.
Like this:

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

This will sum the range I3:I500 where the corresponding cells in the range
E3:E500 equal cell B1 then divide by 1000000..

If that's not what you want then you'll need to tell us what you're
comparing E3:E500 to.

Biff

 

[Arvi Laanemets]

Hi

=SUMPRODUCT(--('2007-01'!$E$3:$E$500,$B:$B,'2007-01'!$I$3:$I$500))/1000000

For sumproduct, all ranges involved MUST be of same dimension! And
whole-column-references aren't accepted at all!
So right will be p.e.
=SUMPRODUCT(--('2007-01'!$E$3:$E$500,$B$3:$B$500,'2007-01'!$I$3:$I$500))/1000000
or
=SUMPRODUCT(--('2007-01'!$E$3:$E$500,$B$1:$B$498,'2007-01'!$I$3:$I$500))/1000000
etc.
The formula calculates (for 1st example)
SUM('2007-01'!$E$3*$B$3*'2007-01'!$I$3+'2007-01'!$E$4*$B$4*'2007-01'!$I$4+...)

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

With SUMIF, you compare condition range ('2007-01'!$E$3:$E$500 in your
formula) with some fixed value (TRUE, "whatever string", 999, etc. as second
parameter) - you have there a range instead. Probably Excel simply takes the
value from cell B1 as condition, i.e it sums all values from
'2007-01'!$I3:$I$500 for which in condition range is same value as in B1.

=SUM(IF('2007-01'!$E$3:$E$500,$B:$B,'2007-01'!$I$3:$I$500))/1000000

I'm not sure. Probably this formula will have some meaning, when you replace
B:B reference with some determined range like for SUMPRDUCT formula, and you
enter it as an array formula (Ctrl+Shift+Enter) - but only when in range
'2007-01'!$E$3:$E$500 all values are Boolean.

 

[Nigel Rablin]

err, what are you trying to do...

? Sum the value in "I" if "E"="B" ? (Ignoring that they are on
different sheets for now)