countif or countifs?

[Davidcat]

This is my first time posting here.
Am Having a problem (I’m probably missing the obvious or I’m stupid).
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Essentially, I have a spreadsheet with 15 tabs. I want the 1st tab "master copy tab" to count the number of Yes, No, N/A and blank cells on each row for the other spreadsheet tabs in their F Column. The F Columns of the other tabs (i.e. Burford, Chichester etc –there are 14 of them), have conditional formatting so that an input of Yes will turn the cell to Green, No to Red etc.
<o> </o>
All I want to do is to correctly formulate the Master Copy tab so that it will count as follows:
<o> </o>
Column C on the master copy should count all tabs where the input in the F column on the other tabs is “Yes”.
Column D on the master copy should count all tabs where the input in the F column on the other tabs is “No”.
Column E on the master copy should count all tabs where the input in the F column on the other tabs is “N/A”.
Column F on the master copy should count all tabs where the input in the F column on the other tabs is blank.
<o> </o>
Can you help with this pleeeeeeeeeease?
<o> </o>
Thanks
David

 

[Andrew Poulsom]

Welcome to MrExcel.

Make a list of the names of your worksheets and name it SheetList. Then you can use a formula like:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetList&"'!C:C"),"Yes"))

 

[Davidcat]

Thanks.
I'll try this.

 

[markmzz]

I think this small modification in the formula suggested by Andrew Poulsom can help solve your problem.

Try this formula in C1:

=SUM(INDEX(COUNTIF(INDIRECT("'"&SheetList&"'!F1:F10"),{"Yes","No","N/A",""}),,COLUMN(C1)-COLUMN($B$1)))

Markmzz