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

[Lucaspj35]

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!

 

[JLGWhiz]

Hi Lucaspj35, welcome to MrExcel Forum.
Don't know about a formula but this code would do it.

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.

 

[Lucaspj35]

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]

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]

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]

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]

Ok looks like i had a line out of place, delete the old code and try this one.

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