# Count the word "Present" on multiple spreadsheets

#### Manny74

##### Board Regular
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...

 B C MEETING DATE 1/7/2019 2/4/2019 ROW 2 ATTENDANCE PRESENT PRESENT ROW 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>

### 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
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
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
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"))

Replies
3
Views
205
Replies
1
Views
255
Replies
5
Views
230
Replies
3
Views
256
Replies
1
Views
172

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