VLOOKUP results in problems in comparing

[Chris J Denver]

Hi newsgroups,

I have a list of data, some of which are from vlookups from other
lists.

e.g.

a b c
1 y 4
1 y 5
1 n 8
0 n 10
0 n 2

Now, what I try is getting the sum of column c when a=1 and b=y.
However, b gets its data as a vlookup from another list.

What I came up with is

=sumproduct((C1:C5), ((A1:A5=1)*(B1:B5)="y"))

This however works with the check for the 1, but not for the check for
the "y", i.e. the vlookup column. I tried typing in "y" myself and it
then got it right. So can I not use the comparison with vlookup data
and is there any workaround for summing up data based on two (and
more) conditions?

Thanks,

Chris

 

[Pete_UK]

Check that the cell(s) which return the values to the VLOOKUP formulae
do not contain "y " (i.e. y<space>). If they do you can modify your
formula like this:

=sumproduct((A1:A5=1)*(LEFT(B1:B5)="y"),C1:C5)

I prefer to put the conditions first in SP.

Hope this helps.

Pete

 

[Chris J Denver]

Hi Pete,

Check that the cell(s) which return the values to the VLOOKUP formulae
do not contain "y " (i.e. y<space>). If they do you can modify your
formula like this:

it's always the easy things, isn't it? Of course this was the
reason...

Thanks a lot!

Chris

 

[Pete_UK]

You're welcome, Chris - thanks for feeding back.

Pete