May 06 2020 09:40 AM
I am trying to do a "COUNTIF" "yes" in a cell between a large number of sheets. I followed an example from the internet which was only based on one sheet: =COUNTIF(B2:B15,"Yes")/COUNTA(B2:B15) to get the percentage of "yes" from the range of cells. In my spreadsheet because there are multiple sheets (first one labeled "Ackerman" and last one labeled "Williams, S") so to calculate the percentages of "yes" in cell "C5" to =COUNTIF('Ackerman:Williams, S.'!C5,"yes")/COUNTA('Ackerman:Williams, S.'!C5). However, I am getting an "error in value" message, so clearly this is not the right format to use. Suggestions on how to correct this, please, are most welcome!
May 06 2020 10:10 AM
@RobynMedcalf use below formula in C5:
=(COUNTIF(Ackerman!B2:B15,"yes")+COUNTIF('Williams, S.'!B2:B15,"yes"))/(COUNTA(Ackerman!B2:B15)+COUNTA('Williams, S.'!B2:B15))
May 06 2020 01:13 PM
The idea is here https://exceljet.net/formula/3d-sumif-for-multiple-worksheets
If you put names of all your sheets into the range and name this range as "sheets", formula could be
=IFERROR(
SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!"&"C5"),"yes"))/
SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!"&"C5"),"<>")),
0)