Count the word "Present" on multiple spreadsheets

[Manny74]

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...

[TABLE="width: 405"]
<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>[TR]
[TD="width: 125, bgcolor: transparent"][/TD]
[TD="width: 62, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]B[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]C[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 96, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]MEETING DATE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1/7/2019[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]2/4/2019[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]ROW 2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]ATTENDANCE[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]PRESENT[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]PRESENT[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]ROW 3[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

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]

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]

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