count the amount of times a word appears in the same column in multiple sheets

**nicko54** · 12-09-2009, 06:16 AM

Hi all,

I am trying to come up with a formula to count the amount of times "Yes"' appears in a column (column D), across 15 sheets in my excel file.

It seems like an easy task, but im struggling!

can anyone help?

**DonkeyOte** · 12-09-2009, 06:19 AM

Conditional calcs in 3D are actually quite complex - they are also inefficient.

My advice would be - group your fifteen sheets together - then in a blank cell common to all enter (for arg sake say Z1) enter:

=COUNTIF(D:D,"Yes")

Ungroup your sheets

Then, assuming then your 15 sheets are listed one after the other, to get total count:

=SUM('First Sheet Name:Last Sheet Name'!Z1)

change sheet names as appopriate