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

[namreh]

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]

You could adapt this

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]

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]

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]

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]

Sheets("Sheet2").Range("A1") = myvar

will return the answer to cell A1.



Try changing to this to sum the range

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

 

[namreh]

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]

I'm not sure I am following.

I see I missed a ")" however.

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

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

 

[namreh]

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]

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.