Count the word "Present" on multiple spreadsheets

Manny74

Board Regular
Joined
May 6, 2016
Messages
95
Hello,

I have 3 worksheets called Doe, Jones and Smith...I am keeping track of the number of meetings each person attends...

I'd like to count the number of "Presents" across 3 worksheets for January's meeting...

So I tried COUNTIF(Doe:Smith!B3,"PRESENT") But I got a value error.....Where is the error?

I am having trouble finding the error...

BC
MEETING DATE1/7/20192/4/2019ROW 2
ATTENDANCEPRESENTPRESENTROW 3
<colgroup><col width="125" style="width: 94pt; mso-width-source: userset; mso-width-alt: 4571;"> <col width="62" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;"> <col width="64" style="width: 48pt;" span="4"> <col width="96" style="width: 72pt; mso-width-source: userset; mso-width-alt: 3510;"> <tbody> </tbody>
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,219
COUNTIF is not one the the functions that handles 3-D references. Instead, you can do something like this:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&{"Doe","Jones","Smith"}&"'!B3"),"present"))

If you put the names in (for example) A1:A3, you can make the formula more dynamic like this:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&A1:A3&"'!B3"),"present"))
 

Manny74

Board Regular
Joined
May 6, 2016
Messages
95
Hi Eric, Thanks!!

I understand your first recommendation, which worked!! But it makes for a long formula.

I'm not sure if I fully understand your second recommendation on making it more dynamic? (which sounds great!)
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,219
For the second version, you save the names in a range on your sheet, not as part of the formula. So put Doe in A1, put Jones in A2, and put Smith in A3. Then you can use the second formula. If you ever need to add Parker to the list, just put Parker in A4 and change the formula to:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&A1:A4&"'!B3"),"present"))
 

Watch MrExcel Video

Forum statistics

Threads
1,108,911
Messages
5,525,593
Members
409,653
Latest member
rishir

This Week's Hot Topics

Top