formula to look up and sum the Grand totals based on the Heading

N

Narnimar

I am monthly getting a list of Values for a Item group Heading. Every month I
receive the list with its grand total which is always not in the same cell
due to no of item in the list. If Jan list has 26 items, Feb will be 32
items. I want a formula to look up and return the Grand total based on the
Item group Heading. E.g. In sheet of January column a1 has Item group
Heading. b2 to b28 has values for the items a2 to a28. and b29 is the Grand
Total. But in the Feb sheet has 32 items so the grand total is in b34 but not
in b29. If I want to summerize the group Heading monthly Grand totals in a
new sheet. Which single formula can I use so that it catches from the
location wherever the Grand total are located in the cells?
(P. S. I cant spend time for naming the groups and summerizing due to amount
of data I got is large.)
 
B

bapeltzer

If you've got the 'Grand Total' labeled in column A and want to find 'Grand
Total' and return the value in the same row from column B: =vlookup("Grand
Total",January!A:B,2,0).
Another approach might just be =sum(January!B:B)/2. This just adds up
everything from column B and cuts it in half (since having the Grand Total
would otherwise doubled the result).
 
R

Roger Govier

Hi

Insert a new row at the top of each sheet.
Place the totals in that row instead of beneath your data.
Then you are always looking at row 1 for Totals, regardless of how many rows
of data exist on each sheet.
 
N

Narnimar

Thanks Bapeltzer, but I prefer for a formula to work exactly like - look at
Heading text then return the Grand total. If I can do this I will save lot of
time in summerizing a long list with multiple Headings down in the columns..
 
N

Narnimar

Thanks Roger Govier, my problem is the spread sheet is large and take long
time to do any edit work on it.
 

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