Solved How do I count cells across multiple worksheets (50)

December 12, 2017 at 10:31:49
Specs: Windows 8
I need to count the Yeses in specific cells across my workbook. I have 6 workbooks to do. Some workbooks will have 3 sheets but most will have close to 50.

My problem is:
I have multiple sheets in a workbook. I have a master sheet that will hold all the calculations. I am clicking in the Master sheet (in cell D3) to put the results there. I need to calculate all the Yes (from a drop down list) in cell D3 - across the worksheets. This needs to be done for every cell on the worksheet (I have 15 columns and 46 rows in each.)

Can anyone help? I am trying to learn VBA but it is so confusing... I am even having a hard time understanding some answers on this site when you give VBA answers.


message edited by Daniej

See More: How do I count cells across multiple worksheets (50)

Report •

December 12, 2017 at 10:40:16
If this is a one time only process, then I would suggest you simply combine all 6 workbooks into one
very large temp workbook, then you could simply do a COUNTIF or COUNTIFS across all temp sheets.

If it's going to be an ongoing process then a macro would probably be the way to go. Unfortunately
my macro skills are just above nil.


Report •

December 12, 2017 at 10:55:09
✔ Best Answer
Here is a COUNTIF solution that uses Named Ranges:

You will need to have all the workbooks open at the same time and your named range should NOT have
any blank spaces.


Report •

December 12, 2017 at 11:05:46
Before going across workbooks I have to go across worksheets. What I ended up doing was creating a drop down list where people can select Yes. Then in the master sheet I put the following: =COUNTA('FirstWorksheet:Lastworksheet'!E5).

The formula counted any cells that were not blank.

Report •

Related Solutions

December 12, 2017 at 11:13:02

It's not clear to me if your COUNTA solution meets your needs. Have you solved your own issue or do you still need help?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Report •

December 14, 2017 at 11:20:10
This is good DerbyDad03 - I solved the issue. CountA will work since I put a drop down list that only has Yes. If they select that, it will count it.

Thank you everyone for your help!

Report •

Ask Question