Sum of cells across multiple workbooks containing the letter "N"

Lucaspj35

New Member
Joined
Sep 30, 2014
Messages
3
Forgive me please for I am new! I am setting up a set of excel workbooks to track errors. There will be a workbook for every day of the month, and a workbook for a summary of the month. All of these will live in the same folder. I am using Microsoft Office Professional Plus 2010.

So on every day of the month, there is a column for the operator to put a "Y" if they have checked and filled out the excel workbook. The default has a "N" in the cell. In my summary workbook, I want it to search across all the workbooks in the folder and tally the number of workbooks that have a "N" in the cell, which is cell F7. Could someone please help me with a formula for this? I have been trying to make +SUMIF work, but I just can't wrap my head around it. Thanks for your help!
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Hi Lucaspj35, welcome to MrExcel Forum.
Don't know about a formula but this code would do it.
Code:
Sub getNTot()
Dim wb As Workbook, sh As Worksheet
fPath = ThisWorkbook.Path
If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
fName = Dir(fPath & "*.xl*")
    Do
        If fName <> ThisWorkbook.Name Then
            Set wb = Workbooks.Open(fPath & fName)
                If wb.Sheets(1).Range("F7") = "N" Then
                    nbr = nbr + 1
                End If
        End If
        wb.Close False
        fName = Dir
    Loop While fName <> ""
MsgBox "The total count of N is " & nbr
End Sub
The code should be copied to the standard code module.
 
Last edited:

Lucaspj35

New Member
Joined
Sep 30, 2014
Messages
3
Hey JLGWhiz, thanks for the quick reply! Again, because I'm a noob, after I copy that formula into the standard code module, how do I see the results on my spreadsheet? I have used Visual Basic sparingly, mostly to create a print button that runs a macro, but I'm unsure on how to proceed from here...Thanks!
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Hey JLGWhiz, thanks for the quick reply! Again, because I'm a noob, after I copy that formula into the standard code module, how do I see the results on my spreadsheet? I have used Visual Basic sparingly, mostly to create a print button that runs a macro, but I'm unsure on how to proceed from here...Thanks!

To install the procedure, press Alt + F11. When the VB editor opens to the standard code module1, copy the code from this thread and paste it into the code pane of the VB editor window. Close the VB editor and save the workbook as a macro enabled workbook with file extension .xlsm. This will allow you to keep the code for future use in that workbook. To use the code from Excel, you can run the code by pressing Alt + F8, then double click the macro name, or left click the macro name once and then click run on the dialog box. You can also use that dialog box to set up a keyboard shortcut by clicking the Options button on the dialog box, then fill in the blanks on the pop up that appears. It is strongly suggested that you first save a backup copy of your file, just in case. It can always be easily deleted if not needed. While this looks like a lot, it isn't once you do it.
 

Lucaspj35

New Member
Joined
Sep 30, 2014
Messages
3

ADVERTISEMENT

Great, thanks. I have done everything you listed, but when I hit Alt+F8 and double click the macro, I get an error message. It seeme to run, and then the following error pops up,

"Run-time error '-2147221080 (800401a8)':

Method 'Close' of object'_Workbook' failed
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Great, thanks. I have done everything you listed, but when I hit Alt+F8 and double click the macro, I get an error message. It seeme to run, and then the following error pops up,

"Run-time error '-2147221080 (800401a8)':

Method 'Close' of object'_Workbook' failed

Don't know off hand why that would happen. It should just close the workbook that was opened. I will have to try and duplicate the error to figure it out. What version of Excel are you running? Also, are you using a MAC?
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Ok looks like i had a line out of place, delete the old code and try this one.
Code:
Sub getNTot()
Dim wb As Workbook, sh As Worksheet
fPath = ThisWorkbook.Path
If Right(fPath, 1) <> "\" Then fPath = fPath & "\"
fName = Dir(fPath & "*.xl*")
    Do
        If fName <> ThisWorkbook.Name Then
            Set wb = Workbooks.Open(fPath & fName)
                If wb.Sheets(1).Range("F7") = "N" Then
                    nbr = nbr + 1
                End If
            wb.Close False
        End If
        fName = Dir
    Loop While fName <> ""
MsgBox "The total count of N is " & nbr
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,421
Messages
5,596,033
Members
414,039
Latest member
southike

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