VLookUp - Adding the items To Arrive At A Total

[John J.]

Hello To All,

I am working on a spreadsheet and have had difficulty determining the formula necessary to add up the data I collected. For example I have an account numbered 60000-00, and in this instance it represents the regular pay from the general ledger account for all employees. This account in a single cell on sheet one, next to it is an empty cell where I have entered the VLOOKUP Formula. On sheet twp and I have a datalist about 25,000 rowsdeept. There is a column that is titled account number, and this is where the number 60000-00 can be found with other account numbers. On sheet two there is also a Total colunm which represents the total of the account in numbers. I would like to be able to search through all the rows with the acct. # 60000-00 in sheet 2, and add up the totals into the one cell on sheet one. Does anyone know how to do this, and if VLookUp is the correct formula to use. This is as close as I have been able to get in the below listed formula;

=VlookUp(D13,'Q1 2011 Data'!A1:K25952,{2,3,4,,25952}, False)

I need to figure out how to add up the diffent colunm values that correspone to the number 60000-00, and that is what the {2,3,4,,25952} represents.
If you have any advice, or suggestions it would be tremendous.
Thankyou all,

John J.

[FDibbins]

Hi and welcome to the forum

a vlookup is used to seach for a specif value in a certain column, and then to return a corresponding value from an adjacent column. It will only return the very 1st match - after that, it stops looking.

From the sounds of it, you need to use a sumif() or sumifS() function. Take a look at those and see if they will work for you. If you type in =sumif( andthen click on the FX button onthe formula bar, a window will open up and help step you through what you need

let me know how you make out?

[John J.]

I have tried a sumif formula;

=SUMIF('Q1 2011 Data'!$E$1:$K$25952,'Q1 2011 Data'!$E$1:$E$25952='Q1 Comparision 2011-2012'!D13,'Q1 2011 Data'!K1:K25952)

But I get a value of 0.

[John J.]

Thanks for the help, I will let you know how it works out. Going to spend some like unsdeerstanding the formula, and then going to give it a whirl.

[FDibbins]

your ranges are a little hard to "see", but try something like this...

=SUMIF('Q1 2011 Data'!$E$1:$E$25952, 'Q1 Comparision 2011-2012'!D13, 'Q1 2011 Data'!K1:K25952)

[John J.]

I have tried the following formula for sumif

=SUMIF($D$13:$D$129,$D$13,'Q1 2011 Data'!E2:K25952)

D13-D19 represent all the account numbers from 60000-00 to 60090-00, and D13 is equal to 60000-00. The sum range is on a separate sheet and I selected all the data from E2:K2952.

So what my formula is trying to say is; these are all the account possibilites, this is the exact possiblity we are looking for, and this is the data range in which those possibilites exist. The problem I am facing is I think becasue I have selected data from a separate sheet if is causing the sum to equal zero.

[John J.]

I tried the formula;

=SUMIF(E2:E25952,E24795,K2:K25952)

in the data sheet and it works. What i would like to be able to do is from a separate sheet analyze the data on the data sheet, and come up with the same total. not sure how to do this though. If anyone has any suggestions it would be very much appreciated. Thanks a bunch.

[John J.]

That works amazing, Thank you so much for the guidence and support on this problem. You are amazing!!

[FDibbins]

Either just add the sheet name
=SUMIF(sheet_name!E$2:$E$25952,sheet_name!E24795,sheet_name!K2:K25952)
or re-do the formula, pointing to the other sheet?

[FDibbins]

Ok seems our timing is a bit off

Thanks for the kind words, happy to help
If this answers your question, please mark this hread "solved" - see point 2 below