Free Microsoft Excel 2013 Quick Reference

# sum of all the sheets

Hi All,

I have 6 sheets in a workbook. 5 have results at A51. In 6th sheet, I want to write a sum formula adding A51 in all those sheets. I can write =sum('Sheet1'!A51,'Sheet2'!A51,'Sheet3'!A51, ........).

But I know there is something much better way.

Raj

## Related Results

### Sum of all sheets not hidden

Hi,

I currently have a total sum in the frontpage of all the sheets in the workbook. It's something like:
=SUM(total:end!F68)
total being the frontpage and end the (hidden) page at the end which stays always behind newly entered sheets.
But I use a filter (vba) to hide/show sheets depending on their name.
Now is there a way to make a (dynamic) sum of all the sheets shown/not hidden?

### Can i have a list of all the Sheet names in one Total Tab

Lets say i have three Sheets , Sheet A, Sheet B, Sheet C and one Total Sheet..
I want to have the names of all the sheets in the Total Sheet in the cell B2
B2 A
B3 B
B4 C

Now if i add any sheet between Sheet A and Sheet B it should immediately show in the list similarly if i delete any sheet then also it should ommediately show , it should also maintain the order in which the sheets are added.

Is this possible.
Please try to explain as well as try to work out in Vb as well as Excel Programming. and attach an example.

### Sumproduct-give the sum of all the instances

Hi,

I Have this formula (kindy donated by this forum):

=(SUMPRODUCT((ISNUMBER(MATCH(\$J\$5:\$J\$21,'Sheet1'!\$A\$5:\$A\$21)))*(F36='sheet1'!\$B\$5:\$B\$21)*('sheet1'!\$ C\$5:\$M\$21)))

the problem is that it only gives the sum of the array for \$C\$5:\$M\$21 which matches the first value of \$J\$5:\$J\$21, and there is often more than one match for the values in \$J\$5:\$J\$21 against \$B\$5:\$B\$21.
Is it possible to re write it so that it would give the sum of all the instances in \$B\$5:\$B\$21?

Thanks

### Find the sum of all the cells in a column which have a certain value to the left

I'm trying to calculate the sum of all the numbers in the Hours column which have the same date, work order, and employee and consolidate the data with just one value for the date, wo, and employee with the sum of all hours he worked on that particular day on that work order. If anyone can help me I'd be eternally grateful!

For instance:

9/2 | 123 | Ralph | 1.02
9/2 | 123 | Ralph | 0.34
9/2 | 123 | Ralph | 2.30
9/2 | 123 | Fred | 6.70
9/2 | 123 | Fred | 1.34

----->>>

9/2 | 123 | Ralph | 3.66
9/2 | 123 | Fred | 8.04

### Freeze only the first row of all the sheets.

Hi,
I have around 200-230 sheets in an excel file.
I want to freeze only the first row of all the sheets.
i want a vba code to do the same.
Thanks in advance for any help.
Best regards,
Me.

### =sum of all cells of a certain color??

Is there a way to create a formula that would enter in a certain cell the sum of all the numbers in cells of a certain color?

For example, have the sum of all Red cells in sheet 1 in cell A1 of sheet 2, sum off all Blue cells in sheet 1 in cell A2 of sheet 2, etc...

### Sum up range of cells through all the sheets

Hello,
I found a cool way to sum up range of cells (e.g. A2:E2) through all the sheets (except the first sheet were the function is).
I write:

```
VB:
[/COLOR]

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```
The problem is that as soon as I hit the ENTER the symbol '*' become the actual range of the current sheets. e.g. sheet2:sheet4.
```
VB:
=SUM(Sheet2:Sheet4!A2:D2)

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```
If I add a sheet it does not dynamically included.
How can I make the symbol '*' stay?
I thought to use the indirect function but I could not get the right syntax.
I wrote '*' in cell A9 and then wrote:
```
VB:
=SUM(INDIRECT("A9")!A2:D2)

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```
Thank you,
Tamir

### Macro to do the sum of values from two different workbook for all the sheets

Hi,

I am new to this forum...& to macros n VBA as well

I have 2 workbook with a.xlsx & b.xlsx with almost 25 sheets each...All the sheets are in same format with same name except the first one for both the workbooks. I want to add the values(P4 to BE323) from both the sheets and print the result in the respective cells of new (third) workbook named add.xlsx with exactly the same sheet name for all the sheets.

For the first sheet say 1 & 2 for both workbook a & b respectively...I want the sum for their values in add.xlsx workbook in the 1st sheet named "main".

Also I want the code as dynamic as possible.

### To do a sum function across all the sheets.

Hi,

Help me to develop a Macro in which we can do a sum function across all the sheets that i have on the work book.

But the sum function should restrict itself to the maximum no. of rows that each row has got on which the data is present.

Lets say that we start the calculation on the first sheet.

Shankar.

### Execute A Set Of Operation On All The Sheets In A Workbook

Hi,
I have a workbook with n number of worksheets namely 2, 4, 5, 6, ….. Etc. I want to perform a set of operation on all the sheets, the problem is that the sheet nos are random, and some of them maybe missing too. so pls help me to execute a Loop in such scenario.
For eg , I want to delete columns A to D in all the sheet.

### How to total of all the cells in a range beneath the merged cell

Hi there my problem is defined below
there is a simple solution to the but for the life of me i can't figure it out

PROBLEM:
set up the spreedsheet
Right from A1 to D1 select all four cells and merge them. In the merged cell goes the title Jul-05 say (it's a date at any rate)
In A2, B2, C2, and D2 goes Wekk 1, Week 2, Week 3, and Week 4 respectively
in A3, B3, C3 and D3 go 5, 10, 15, and 20 respectively
right now imagine that all along those three rows it goes from Jul-05 til the end of the year in the same format - obviously some months have 5 weeks in them (as in the attached file)

the formula (AKA PROBLEM)
what I want to do is anywhere else on the sheet, let's use cell B10 for the sake of argument to enter our forumla
In A10 ( the cell next to it) I would like to be able to enter any month in the same format say - Jul-05
and in B10 it returns the sum total of all the cells in the range beneath the merged Jul-05 cell
so ther'es gonna be a lookup in some form but I just can't get it to define the right range for each month

### Formula to get the sum of all work hours

What formula will i use to sum up all the number of work hours, ex. 8:00, 8:30, 8:25, 4:30, 8:20 i tried the auto sum but since it is in time format it could not summate ten hour items. what formul;a should i use?

### Adding values of multiple excel sheets and populating the master sheet

Dear All,
I am new to excel.
my excel version is 2003

I have 5 excel sheets sent by 5 customers, the columns are as following
ProductCode Description QtyOrdered

I need to merge the 5 sheets and produce 1 Master sheet based on the quantity ordered for different products. The products ordered by customers are not in specific order.
For example
Customer1

P001 XYZ 20
P002 BBB 90
P006 VVV 8

Customer2

P001 XYZ 10
P009 NNN 3
P006 VVV 5

My problem is if customer 1 has ordered product X and customer 2 and also ordered product P001, the Master sheet should show the product P001 as the sum of all the customer excel sheets

As per my example the Master should show product P001 as 30

Any help is much appreciated

### Sum of cell reference in multiple sheets

Apologies if this has been asked already, but I have a thrity worksheet file and on 'sheet 1', I want a sum of all the values in cell 'A1' on all the other sheets.

Is there a formula for doing this quickly?

### Summing of numbers between sheets for same column values

I am trying to figure out how to add the number of items placed in a
system. Each system has multiple items in it with different yearly
quantities.

For an example, in sheet1 I have a table with 3 columns A- is the item#
(00010001), B- is the system that Item # is placed in (BB001), C- is
the quantity of items in that given system (111000). In sheet 2 I would
like column A to be each system (BB001-BB100) and column B to be the
sum of all of the different items #'s in that system (lets say system
BB001 has 3 item #'s 00010001, 00010002 and 00010003, all three of them
have a quantity of 111000).

What function or formula would give me the correct sum of 333000 for
system BB001? How would I make this dynamic so that if 2 more items
were added to system BB001 their quantities would be included in the
overall sum of quantities in that system?

### How do I find the sum of all digits in a number?

How do I get Excel to return the sum of all digits in a number?
For example if i have the number 1234, i want excel to do the following
calculation:
1+2+3+4 and return the value 10. I need to know how to do this for any
number of any size.

### Sum of all columns that match criteria of another column along the same row

Hi,

I would like to get the sums of all columns in B, that match criteria from column A, and then show that number. Column A is a date in the form of MM/DD. Column B is a numerical value ranging from 1-3.

So basically Sum of all Column B where the corresponding A value is the criteria e.g. 11/8

Thanks for the help I'm still somewhat new to excel and this forum has been a great benefit.

### Unhide all the sheets at once?

i have multiple hidden sheets in a workbook.
I'm looking for a macro that gives me an option of unhiding all the sheets at once or a group of them (check box for sheet names with select all option)

### Sum of cells which use a dark red font only

Hi,

I am trying to produce a spreadsheet that shows a column of figures for sales for each invidual person in a specific company. At the moment the spreadsheet sums the total sales amount for each company and lists it in dark red beneath the sales amount for each individual. What I need to do is produce a grand total at the very bottom of the list which produces the sum of all the entries which are written in the dark red font. I pretty confident that this can be done but I have no idea how to go about doing it.

I have attached an example of the type of thing I need to do to this post. The spreadsheet needs to be quite versatile because the people who will be using it will need to add/remove companies and sales figures as necessary and so I am trying to make it as easy as possible for them to add/remove stuff without fudging up the entire sheet and making all the calculations incorrect.

James

### How: SUM of all rows contained word "Pen"

Hello experts:

I need your professional help to create formula which will be:

SUM of all rows in column B which contain the world "Pen" in column A.

huhh.

Ivan

### How do you copy the names of the sheet automatically?

hi i hope you can help me make a code that will copy and paste the names of the sheets in my excel file and put it in one sheet which will serve like a table of contents or a list of all the sheets or tabs in my file.

### Summing Multiple Sheets Per Employee

Good Morning,

I am having a problem. I have 20 sheets set up exactly the same with employee payroll and another sheet that lists all of the unique names and id's. I need to get a total sum of all the sheets per employee. Please note that some employees can appear multiple times on a sheet.

******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCDEFG1last namefirst namePositionIDGenderDeptamount2JonesJohnX89891MF410\$1,830.003JohnsonMikeX89892MD167\$2,220.004AllenRayY89893MF410\$675.005ThompsonDavidY89894MG43A\$300.006JonesAlZ89895MHYJ\$195.007PhillipsTomC89896MT567\$150.008SmithBenV89897M67L\$240.00Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

### Saving all the sheets as .txt

Hi guys!
Yesterday this macro was working, today not.... I am getting crazy! :-)
Thanks in advance for all the help.

The aim of the macro is to save all the sheets in .txt files named as the original sheets.

```Sub save_as_txt()

Dim nome As String
Dim wknew As Workbook
Set wknew = ThisWorkbook
With Application
.ScreenUpdating = False
End With

Dim cartella As String
cartella = InputBox("Please insert a name for the new folder.")

MkDir cartella

For x = 1 To Worksheets.Count
nome = Sheets(x).Name
Sheets(x).Copy
ActiveWorkbook.SaveAs Filename:=wknew.Path & "" & cartella & "" & nome &
".txt", FileFormat:= _
xlUnicodeText, CreateBackup:=False
ActiveWindow.Close
Next

End Sub```

*** ok, the new folder is created in the default excel folder (documents). I think that the solution is to create the folder in the same path of the active workbook? How can i do? Again, thanks in advance and happy new year!!

### Return a list of all the worksheets?

I'm trying to think of a way that can return a list of all the worksheets into a range in excel. That is one sheet in the workbook, say from a1:a20 have all the names of the sheets in the workbook that updates automatically as another sheet is added.

The only way I can think of doing this is w/ VBA, but i'm wondering if anyone can think of a way to do it w/ just formulas.