VLOOKUP results in problems in comparing

  • Thread starter Thread starter Chris J Denver
  • Start date Start date
C

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
 
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
 
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:
Click to expand...

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

Thanks a lot! :)

Chris
 
Back
Top