VLOOKUP results in problems in comparing

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
 
P

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
 
C

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top