Counting across sheets

  • Thread starter Thread starter Jgorow
  • Start date Start date
J

Jgorow

Example:
Cell C6 is either "Yes" or "No" on all worksheets (say 20 sheets) in the
workbook.

I thought it would be easy to get a count of the "Yes" entries, but can't come
up with a function. I tried CountIf, but it doesn't work across multiple
sheets.

I would appreciate any ideas.

John
 
One way

=SUMPRODUCT(COUNTIF(INDIRECT("'"&F1:F20&"'!C6"),"Yes"))

where F1:F20 is a list with the names of the sheets

If the sheets are default excel sheet names like Sheet1 - Sheet20
you can use

=SUMPRODUCT(COUNTIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:20"))&"'!C6"),"Yes"))

without any help list
 
Back
Top