Help Countinting the number of sheets that have a value in a cell

tmo31

New Member
Joined
Sep 1, 2019
Messages
3
Hello,
I have a workbook with 25 sheets (tabs), each representing a different project with data reporting the number of people reached by that project for a range of different activities. Not all projects did all activities.
I have a consolidated sheet in which I need to count the total number of people reached per activity (which I can do easily enough), but I also want to know how many projects did that activity (i.e. reached more than 0 people for that specific activity). I tried to COUNTIF('ProjectSheet1'!D17,">0")+COUNTIF('ProjectSheet2'!D17,">0")+etc. but as I have 25 sheets it was too long and wouldn't work.
Does anybody know how I can do this?

Additionally (and probably not possible but I thought I'd try), I also want a separate formula that names the projects that did that activity and lists all these in a cell. Each sheet/tab is the project name (it's a code, no spaces, all the same length (3 letters then 4 numbers).
Any help would be greatly appreciated!
Thanks
Tim
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
For your first question, assuming your sheets are named ProjectSheet1 through ProjectSheet25 and that no other sheet with a different name is located within them (in other words, the sheets must be contiguous), then give this a try...

=COUNTA(ProjectSheet1:ProjectSheet25!D17)

As for your second question, the approach may depend on what version of Excel is being used.
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,227
Office Version
  1. 365
Platform
  1. Windows
=COUNTA(ProjectSheet1:ProjectSheet25!D17)
Rick, wouldn't this include zero value in the count even though the OP's long formula is trying to exclude zero value?

@ tmo31
If the values in D17 of the various sheets contains a formula, is it feasible to change that formula along these lines?
=IF(your existing formula =0,"Zero",your existing formula)
If you were able to do that then to get your count you could use

COUNT(ProjectSheet1:ProjectSheet25!D17)
 

tmo31

New Member
Joined
Sep 1, 2019
Messages
3
Hi,

Thanks for your reply.

The sheet names aren't contiguous, each sheet is named using the project code (e.g. PHI1001, DCR1806, CAM1404).

The values in D17 (through to D141 - there are a lot of different activities I'm measuring) on each project sheet are entered manually. They're either blank (if that project didn't do that activity) or have a number in it.

I'm using Excel 2019.

Thanks!
Tim
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

The sheet names aren't contiguous, each sheet is named using the project code (e.g. PHI1001, DCR1806, CAM1404).The

I did not say the sheet names had to be contiguous, only the sheets (their location with respect to each other)... the two sheet names in the formula are the first sheet and the last sheet names (all sheets between them will be included).
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
**** CROSS POST ALERT ****

This same question was asked on another forum but received no answers there before I posted the Cross-Post Alert there.
 

tmo31

New Member
Joined
Sep 1, 2019
Messages
3

ADVERTISEMENT

Hi Rick,

Sorry I misunderstood! Thank you very much, the formula you gave works.

I'm working in Excel 2019, do you know if it's possible to return the names of the sheets that have a value, as well as counting the number of sheets too?

Thanks!
Tim
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
I'm working in Excel 2019, do you know if it's possible to return the names of the sheets that have a value, as well as counting the number of sheets too?
Peter's formula should also work. As for the above, I do not have that version (so I do not have access to the new sheet functions it has), but I believe a formula solution is possible with it. I am pretty sure Peter has a version of Excel that does have access to the new functions, so I am sure he'll post back when he next comes back online.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,227
Office Version
  1. 365
Platform
  1. Windows
No, as far as I am aware there is no worksheet function that would return the sheet names that contain values in a particular cell. The functions that do 'something' with 3-D references are listed here

You would need to use vba and clarify how and where the result(s) should be displayed. That is, on what sheet, whether all in one cell with commas or down a column, across a row etc.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,950
Messages
5,525,827
Members
409,665
Latest member
Jessyi

This Week's Hot Topics

Top