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.
 

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
Joined
Feb 6, 2012
Messages
1,951
You could adapt this

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
 
Solution

namreh

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

ADVERTISEMENT

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

ADVERTISEMENT

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.


1614609058108.png
 

mrshl9898

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

Forum statistics

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