Vlookup then add and return total

[Melville]

Hi,

Worksheet1
Column A ColumnB
26/07/2010 263
26/07/2010 117

Worksheet2
ColumnA ColumnB
26/07/2010 RESULT = 380


In Worksheet2 columnB I need to vlookup the columnA date in Worksheet1 ColumnA and then add worksheet1:columnB of the same date and return the result in worksheet2:columnB.

I hope that makes sense and someone can help please.

thankyou

Ru

[teylyn]

Hello melville,

in Sheet2, B1, try

=sumif(Worksheet1!$A$1:$A$10,A1,Worksheet1!$B$1:$B$10)

Adjust ranges to suit.

[Melville]

Hi Teylyn,

I just tired the above formula and it keeps returning a '0' when it shouldnt be as i have figures in Worksheet1 column B of the same date.

Do you know of any other way please.

Thank you

[teylyn]

post a data sample. if it returns zero, then it does not find any matching dates. check your dates are dates, not text. No leading or trailing spaces.

check that you are using the correct ranges.

I will not post another formula, because this one will work if applied correctly and the data is in shape.

[Melville]

I have exactly the same data I wrote in my first post, but it just keeps returning '0'. i checked the format of the cells, and these are in date format. the result column is in general format. So the format is correct, but I just dont understand why its returning '0'. I created a new workbook and tested the formula in there and it worked fine, so i dont understand why it keeps returning '0'.
My workbook is shared, but i even tired unsharing it, it the forumla didnt work, i made sure no filter was on, but still got '0'.

I really hope you can help because ive been trying to get it to work for the past 6hrs and keeping getting '0'.

appreicate your help.

thank you

[teylyn]

I can only repeat: post the workbook! I can't diagonse from remote what's wrong with your data or the way you put the formula in. There could be all kinds of stuff happening. Post your workbook so we can have a look at the real thing. Put in your formula so we see what you have in place.

Just lamenting that it does not work is not helpful.

[Melville]

Hi,

I have attached my workbook. please worksheet 2 columnC, it has the forumla in there. I cant get my head around what im doing wrong for it to keep =0.

please have a look and let me know what im doing wrong.

thanks

[WHER]

Melville,
Have a look on Sheet1 cell D75. As Teylyn suggested, some of the data you're trying to add, is text instead of numbers.

[martindwilson]

its the format of cols a and b sheet 1 that is doing it select col a then data /text to columns just click finish
repeat for column b.

[Melville]

MartinDWilson, it worked. thank you so much. I cant believe that was all I needed to do. Ive never had to use the option in the menu. know for the future now.

Thank you everyone for your help..

[Melville]

All Sorted everyone. MartinDWilson's answer worked.

thanks