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

namreh

New Member
Joined
Feb 24, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Feb 6, 2012
Messages
1,888
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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

namreh

New Member
Joined
Feb 24, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
You do follow me now.
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.

Thanks for your help.
 

namreh

New Member
Joined
Feb 24, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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.

Sub addvalues1()

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
 

Watch MrExcel Video

Forum statistics

Threads
1,132,803
Messages
5,655,395
Members
418,194
Latest member
LabraLime

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top