# 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.

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### mrshl9898

##### Well-known Member

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

Dim ws As Worksheet
Dim myvar As Long

myvar = 0

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.Range("C12")
End If
End If
Next ws

MsgBox myvar  ''change to sheets("Sheet name").range("cell to put value in") = myvar  - eg: Sheets("Sheet1").Range("A1) = myvar

End Sub``````

#### namreh

##### New Member
Thank you very much for answering.
I probably should have mentioned that I only have basic skills in Excel.
I hoped that somehow a function could have done it.
But first I have to get my workbook macro enabled and that will have to wait until the weekend.

#### mrshl9898

##### Well-known Member
I'm pretty sure at 48 sheets the formula won't be accepted for being too long unfortunately. You can try, but might need to break it down to 2 or 3 formulas, then add them.

You want this essentially for a sheet

=IF(Sheet3!B6=1,Sheet3!C12,0)

then + every other sheet with it's own formula

=IF(Sheet3!B6=1,Sheet3!C12,0)+IF(Sheet4!B6=1,Sheet4!C12,0) etc.

#### namreh

##### New Member

Thank you.
The VBA code works.
The formula way is not worth it.

Next is how to return this answer from code into a cell on my summary sheet (ie sheet2)
Actually the C12 is one cell in the range c12 to i17.

#### mrshl9898

##### Well-known Member
VBA Code:
``Sheets("Sheet2").Range("A1") = myvar``

will return the answer to cell A1.

Try changing to this to sum the range

VBA Code:
``myvar = myvar + Application.WorksheetFunction.Sum(ws.Range("C12:I17")``

#### namreh

##### New Member

Thanks. Now I know how to return sum to a cell.
The range is a problem.
getting a compile error. "Expecting list separator or)"

Sorry. I do not want to waste your time. Let us try this way.
This is my summary page (Sheet1). On Sheet3 to Sheet50 same as in picture, data can be anywhere in range C12:C17. so all the data in that range must be summed to the same cell in summary if B6 is 1.
As you can see the code for summing C12 worked.

I have four scenarios in B6. ie there can be a 1, 2, 3 or 4.
My thinking was to copy down this sheet as in picture so that I have 4 areas and then have four macros
to sum if B6 is 1,2,3, or 4.
As you can see you solved my original thread, and I marked it as solved.
I do this for no financial gain. It is to make my job easier as deliverer.
If this is outside the scope of Mrexcel just let me know.

Thank you.

#### mrshl9898

##### Well-known Member
I'm not sure I am following.

I see I missed a ")" however.

VBA Code:
``myvar = myvar + Application.WorksheetFunction.sum(ws.Range("C12:I17"))``

Maybe showing one of the other sheets and your expected outcome would help explain.

#### namreh

##### New Member
The code works now but not what I want.
Sheet3:Sheet50 looks just like this top picture, but with headings and a 1,2,3, or 4 in B6
Sheet3:sheet50 is the orders. ie if there is a 10 in F12 on sheet30 it means that this customer wants 30 trays jumbo eggs.
On sheet1, if this is the only customer with an 1 in B6, the summary sheet will return 10 in F12.
The value in B6 has to do with what type of packaging customer wants ie cheapest etc.
Hope this helps.

#### namreh

##### New Member
Oops!! Sheet3:sheet50 is the orders. ie if there is a 10 in F12 on sheet30 it means that this customer wants 10 trays jumbo eggs.

Replies
1
Views
272
Replies
7
Views
140
Replies
1
Views
230
Replies
1
Views
41
Replies
4
Views
65

1,136,442
Messages
5,675,892
Members
419,591
Latest member
mersanko

### 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