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

[tmo31]

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

 

[Rick Rothstein]

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.

 

[Peter_SSs]

=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]

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]

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]

**** 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]

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]

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]

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.