Do I need a sumif or sum of a vlookup formula?

[Pauliec]

Hi,

I am trying to sum up a different cell on each sheet in a workbook where the sheets are named "AR1" through to "V01". On each sheet there is a cell with "Grand Total" (in column A, but in a different row on each sheet) and the next cell (in column B, again in a different row on each sheet) is the figure I want to sum.

I have tried the following formulae.

=SUMIF('AR1:V01'!A6:A250,"Grand Total",'AR1:V01'!B6:B250) - this returns #VALUE

and
=SUM(VLOOKUP(A2,'AR1:V01'!A7:B250,2)) - which again returns #VALUE.

Any help would be great!

Thanks.

[PeterB]

I know this is not going to actually solve your problem, but you are trying to use 3D-references in functions that do not support them (take a look at the Excel Help file and search for "3D-reference" then look for "Refer to the same cell or range on multiple sheets".

Have started to look at solving your problem, but have not got a solution yet. If anyone else gets there before me. Please post.

[PeterB]

Try this macro :

Sub FindGrandTotals()

Dim CurrentSheet As Integer
Dim FirstSheet As Integer
Dim GrandTotal
Dim LastSheet As String
GrandTotal = 0

Set NewSheet = Worksheets.Add 'Since you have not stated where you want the result,
With NewSheet 'This adds a new worksheet called "Totalise", where
.Name = "Totalise"
End With 'I am going to put the result.

FirstSheet = Worksheets("AR1").Index 'I'm using referencing of Worksheets by index number
LastSheet = Worksheets("VO1").Index 'because I don't know how else to cycle thru' the
CurrentSheet = FirstSheet 'worksheets. If you change sheet names, the FirstSheet
'and Lastsheet statements will need to be changed accordingly.

For CurrentSheet = FirstSheet To LastSheet

Set c = Worksheets(CurrentSheet).Range("a:a"). _
Find("Grand Total", LookIn:=xlValues) 'Find the row that has
'"Grand Total" in column A.

GrandTotal = GrandTotal + Worksheets(CurrentSheet).Cells(c.Row, 2) 'This statement sums the contents of
'the column B cells next to the column
'A cells containing "Grand Total".

Next

Worksheets("Totalise").Cells(1, 1) = "Summed Grand Totals" 'This just puts a 'comment' in Cell A1 of the
'"Totalise" worksheet
Worksheets("Totalise").Cells(1, 2) = GrandTotal 'This places the sum in Cell B1 of the
'"Totalise" worksheet

End Sub

[PeterB]

Sub FindGrandTotals()

'Sorry I have not replied any earlier, - I haven't been on ExcelForum for a while.
'Next, please don't be offended if I am explaining too much, but you said you are new to VBA,
'so I will explain each line as best as I can. BTW, I've not been using VBA for too long
'either, so I hope the explanation is up to standard. Come back if it's not.
Apologies for not sending a Private message, but this exceeds the 1000 character limit that seems to be in place on PMs.

'OK, Here we are declaring variables that we will be using in the macro. I don't think that this
'is absolutely necessary (I may be wrong), However, it is a 'clean' and 'structured' way of working.
Dim CurrentSheet As Integer 'We will use the Currentsheet variable to keep track of which
'worksheet we are working in at any particular point in time.
Dim FirstSheet As Integer 'We will use the FirstSheet variable to hold the index number
'of the first of your worksheets.
Dim LastSheet As Integer 'There you go, proof that I'm fairly new to vba - originally, I
'declared LastSheet as a String. (I don't know why I did that,
'or why it worked when I was trying it out.)
'Anyway, this variable will hold the index number for the last
'of your worksheets.
Dim GrandTotal 'We will use this variable to hold the result that you are after.
'Dim LastSheet As String 'I have 'commented' this line out so you could see the error I
'said about above. You can delete this declaration, if you want
'to.

GrandTotal = 0 'Here we are making sure that the GrandTotal variable is set to
'zero, so we don't add up garbage that might be sitting in the
'memory location that the variable uses. Again, good practice,
'even if not always necessary.

'This next statement adds a new worksheet to you workbook, so we can work in the new worksheet,
'without disturbing or corrupting your existing worksheets.
Set NewSheet = Worksheets.Add

'The next three statements name the new worksheet as "Totalise". This is so we know how to refer to
'the worksheet when we want to do anything in it.
With NewSheet 'The "With" statement lets us specify an object or user-defined
'type once for a series of statements. "With" statements make
'procedures run faster and help us avoid repetitive typing.
'Actually, here it is probably overkill because we only have one
'following statement, but if you use it regularly, it becomes
'habit and will likely benefit you in the long run.
.Name = "Totalise" 'The fact that we used the "With" statement means that we can
'define the new worksheet's .Name property as "Totalise", using
'this cut-down statement, rather than using the statement -
' NewSheet.Name = "Totalise"
'(I'm also not sure if the full statement would be work,
'anyway.)
End With 'A "With" statement has to have a matching "End" statement.

'The next statement assigns the index number of your "AR1" sheet to the FirstSheet variable. I must
'admit that it has just struck me that I have assumed that the index numbers of your worksheets
'actually increment from "AR1" through to "VO1". You may need to verify this. I get the impression
'from the VB Help file that the index number depends on the ordering of your worksheets' tabs across
'the bottom of the worksheet window. So I would expect that you would need to have them ordered "AR1"
'through to "VO1" with no interceding worksheets that you do not want taken account of in the
'GrandTotal result. I would also expect that "AR1" does NOT need to be the left-most worksheet.
FirstSheet = Worksheets("AR1").Index

'The next statement assigns the index number of your "VO1" sheet to the LastSheet variable. This way,
'we can check when we have got to the last sheet in your series.
LastSheet = Worksheets("VO1").Index

'If you change the order of your worksheets or their names you will ned to adjust the code in this
'macro. Either way, you would have to change the two statements above to refer to your new first and
'last sheets.

'The next statement sets the value of the CurrentSheet variable to the index number of your first
'sheet.
CurrentSheet = FirstSheet


'The following For-Next loop works through each of your worksheets, according to their index numbers.
'It starts with the index number for your first sheet and ends with the index number of your last
'sheet.
For CurrentSheet = FirstSheet To LastSheet


'The next statement finds the row that has "Grand Total" in column A of the worksheet whose index
'number is currently held in the CurrentSheet variable. We do this, using the "Find Method".
'The "Find Method" finds specific information in a range (in this case "a:a" - which really means
'column a), and returns a Range object that represents the first cell where that information is
'found.
'The statement assigns the returned Range to the variable c, so that we can then use it later on.
Set c = Worksheets(CurrentSheet).Range("a:a").Find("Grand Total", LookIn:=xlValues)


'The next statement performa a running summation of the contents of the column B cells next to the
'column A cells containing "Grand Total". What that means is that each time the loop repeats, the
'GrandTotal variable is updated by adding the total from the worksheet that is now being looked at.
'The total on each sheet is located at the row that we get from the variable c and the column is 2,
'because 1 is equivalent to A, 2 is equivalent to B, etc.
GrandTotal = GrandTotal + Worksheets(CurrentSheet).Cells(c.Row, 2)

'The next statement makes the loop repeat until we have worked our way through to your last sheet.
Next

'The next statement just puts a 'comment' in Cell A1 of the "Totalise" worksheet. You can change
'this to put it in any cell you wish on any worksheet you wish. Of course you may not want to use
'this statement at all. Also, if you don't use the "Totalise" worksheet, you can remove the
'statements that add and name it.
Worksheets("Totalise").Cells(1, 1) = "Summed Grand Totals"

'The next statement places the sum that you want in Cell B1 of the "Totalise" worksheet. Again, you
'can change this to put it in any cell you wish on any worksheet you wish, or just delete this
'statement entirely.
Worksheets("Totalise").Cells(1, 2) = GrandTotal

End Sub