# Summing over multiple identical sheets for criteria entered in same cell on each sheet

#### namreh

##### New Member
Hi.

I Have 50 worksheets all identical accept the the first and second, my summary sheets.
I want to sum C12 on sheet3 to sheet50 if the value in B6 is 1.
B6 on the sheets differ from sheet to sheet and is inserted there with Vlookup from a linked file.
Explained in English it sound so easy as:
Sum all the values in C12 over all the sheets to this cell where
I am entering this formula only for the sheets with value of 1 in B6.

Sorry if this was answered before and I missed it.

Thank you.

#### mrshl9898

##### Well-known Member
I think I follow you now, go through tabs 3:50 and sum the same cell, then return that to Sheet1?

Perhaps:

VBA Code:
``````Sub addvalues()

Dim ws As Worksheet
Dim myvar As Long
Dim colnum As Long

myvar = 0
colnum = 3

Do Until colnum = 10
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Sheet1" Or ws.Name <> "Sheet2" Then ''change to sheet names not to count
If ws.Range("B6") = 1 Then
myvar = myvar + ws.Cells(12, colnum)
End If
End If
Next ws
Sheets("Sheet1").Cells(12, colnum) = myvar
colnum = colnum + 1
Loop

End Sub``````

Are you always looking for 1? If you want to match the value in Sheet1 B6 you can use:

VBA Code:
``````Sub addvalues()

Dim ws As Worksheet
Dim myvar As Long
Dim colnum As Long

myvar = 0
colnum = 3

Do Until colnum = 10
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Sheet1" Or ws.Name <> "Sheet2" Then ''change to sheet names not to count
If ws.Range("B6") = Sheets("Sheet1").Range("B6") Then
myvar = myvar + ws.Cells(12, colnum)
End If
End If
Next ws
Sheets("Sheet1").Cells(12, colnum) = myvar
colnum = colnum + 1
Loop

End Sub``````

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### namreh

##### New Member
Go through tabs 3:50 and sum the same cell, then return that to Sheet1?
I like your second code. On sheet1 b6 enter what I want to sum for.
I am starting to understand the coding.
For testing purposes, I have entered the value of 2 in all sheets 3:50
Only 2 sheets have a 1 in B6
That means that colnum 3 to 9 should all evaluate to 4
Not so, it goes 4,8,12,16,20,24
We have only done row 12
It must go to row 17.

#### namreh

##### New Member
I got it working. During the 90s I did a lot of Dbase4 programming.
Principle is the same so I am learning.
I want to close this thread now but for one problem (sure more will emerge)
If you run the code without erasing data on sheet1, the next run will add the value on sheet1 to the sum data.
So say C12 evaluate to 10
next run 20
next run 30....... etc.
Can I just add some code? and how?
This is what I have done to your code.

Dim ws As Worksheet
Dim myvar As Long
Dim colnum As Long
Dim rownum As Long

myvar = 0
colnum = 3
rownum = 12

Do Until rownum = 18
Do Until colnum = 10
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Sheet1" Or ws.Name <> "Sheet2" Then ''change to sheet names not to count
If ws.Range("B6") = Sheets("Sheet1").Range("B6") Then
myvar = myvar + ws.Cells(rownum, colnum)
End If
End If
Next ws

Sheets("Sheet1").Cells(rownum, colnum) = myvar
myvar = 0
colnum = colnum + 1

Loop
colnum = 3 ' to go down to next row to sum
rownum = rownum + 1
Loop

End Sub

Replies
4
Views
303
Replies
3
Views
341
Replies
1
Views
756
Replies
0
Views
184
Replies
1
Views
217

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,236
Messages
5,768,955
Members
425,506
Latest member
AndreaWorkPlace

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back