Sum largest number in a column on all sheets in a workbook

[Harryf]

Hi there!

I have a workbook with multiple tabs with varying names.

I would like to sum the largest values contained in column A of each sheet on a separate sheet. I can use MAX and LARGE on each sheet, but have no idea how to sum all these MAX or LARGE values in a single formula or VBA and not using the repetitive Sheet1'large(A:A)+Sheet2'large(A:A)+Sheet3'large(A:A)... etc.

Hope someone can help me with a simple and quick solution!

Thanks!

Harry

 

[RobP]

Hi, would this work for you ?

Sub sumsheets()

Dim running_tot, sheet_num As Integer

For sheetnum = 1 To ThisWorkbook.Sheets.Count

    running_tot = running_tot + Application.WorksheetFunction.Large(Sheets(sheetnum).Range("A:A"), 1)

Next sheetnum

'define what you want to do with running_tot next ?
Sheets(1).Range("B1") = running_tot

End Sub

 

[RoryA]

If you can put the MAX formula in the same cell on each sheet, then a simple SUM formula on the separate sheet will add them all up.

 

[Harryf]

Hi Rob!

Thank you very much! Is it possible to help me get the sum of the values placed in the active cell on the then active sheet when I run the macro?, So, if the active cell is B1 on sheet "Jan 2023" when I run the macro, then the summed values should be placed there, i.e. B1 on Jan 2023? I suppose this is what you mean with 'define what you want to do with running_tot next ?, but I run into all sorts of syntax errors with my virtually non-existent VBA knowledge...

Thanks Rob!
h

 

[Harryf]

Thanks Rory, but entering a new function on each sheet and then summing the values in all of those cells is even more work than just summing those values by going to each sheet and clicking on the biggest value in each column A...

I should have planned ahead, it seems, because had I done it as you suggest from the start, then I'd have been on the same page as you... I'll certainly know for "next time"...

Cheers!

 

[RoryA]

entering a new function on each sheet and then summing the values in all of those cells is even more work than just summing those values by going to each sheet and clicking on the biggest value in each column A

You must have a very odd layout then.

 

[RobP]

Sure, when you posted your question originally, you asked for a total of the Max numbers in col A of each page - so Ive stored that in "running_tot". But Ive no idea what you want to do with it now you have it, so I put it in Cell C1 of the first sheet.

Then added another line to give you the sum of all values on each sheet as just requested.
cheers
Rob

Sub sumsheets()

Dim running_tot, sheet_num As Integer

For sheetnum = 1 To ThisWorkbook.Sheets.Count

    Sheets(sheetnum).Range("B1") = Application.WorksheetFunction.Sum(Sheets(sheetnum).Range("A:A"))
    
    running_tot = running_tot + Application.WorksheetFunction.Large(Sheets(sheetnum).Range("A:A"), 1)

Next sheetnum

'define what you want to do with running_tot next ?
Sheets(1).Range("C1") = running_tot

End Sub

 

[Harryf]

Hehe - it probably is odd... In the sense that each sheet consists of rows and rows of sales data entered across many columns, some of which require inputs and others which contain functions. And then, depending on the number of sales, the number of rows where data is entered into could end at, say, row 412 in January and 377 in Feb. Just an example. The largest value in the column that I'd like to total across all months will then be contained in A412 in January and A377 in Feb. Etcetera...

So, for your suggestion to work best, the MAX function should preferably be placed at the top (row 1) of any column, e.g. A1, for otherwise I need to place it down far enough in a column that any month will, in all probability, not exceed that row number in sales, say row 1000. And this is part of what made me decide against it - I'll have to insert a row above the current row 1 to use that row for the max function because using row 1 is certainly better (in my case) than scrolling down to row 1000 - but all this additional steps on each and every sheet makes it more cumbersome than just simply adding the largest values that I find in each sheet together by using "a+b+c+d etc....

Does it make sense?

 

[RoryA]

Well, having a separate sheet for each month is not a great reporting setup to begin with, but that's another story.

Select all the sheets with data, and insert a new row 1. That will do it to all sheets. Then type =MAX(A2:A5000) which, again, will enter the formula on all selected sheets (the 5000 is just a row number large enough to cover the likely data in every sheet). Then add your sum formula to the front sheet. Job done.

 

[Harryf]

That's brilliant, Rory... The lights came on, I am ashamed to say...

Great solution. Thanks!