Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-1364497%22%20slang%3D%22en-US%22%3EExcel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1364497%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20do%20a%20%22COUNTIF%22%20%22yes%22%20in%20a%20cell%20between%20a%20large%20number%20of%20sheets.%26nbsp%3B%20I%20followed%20an%20example%20from%20the%20internet%20which%20was%20only%20based%20on%20one%20sheet%3A%26nbsp%3B%20%3DCOUNTIF(B2%3AB15%2C%22Yes%22)%2FCOUNTA(B2%3AB15)%20to%20get%20the%20percentage%20of%20%22yes%22%20from%20the%20range%20of%20cells.%26nbsp%3B%20In%20my%20spreadsheet%20because%20there%20are%20multiple%20sheets%20(first%20one%20labeled%20%22Ackerman%22%20and%20last%20one%20labeled%20%22Williams%2C%20S%22)%20so%20to%20calculate%20the%20percentages%20of%20%22yes%22%20in%20cell%20%22C5%22%20to%20%3DCOUNTIF('Ackerman%3AWilliams%2C%20S.'!C5%2C%22yes%22)%2FCOUNTA('Ackerman%3AWilliams%2C%20S.'!C5).%26nbsp%3B%20However%2C%20I%20am%20getting%20an%20%22error%20in%20value%22%20message%2C%20so%20clearly%20this%20is%20not%20the%20right%20format%20to%20use.%26nbsp%3B%20Suggestions%20on%20how%20to%20correct%20this%2C%20please%2C%20are%20most%20welcome!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1364497%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1364556%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1364556%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F658222%22%20target%3D%22_blank%22%3E%40RobynMedcalf%3C%2FA%3E%26nbsp%3Buse%20below%20formula%20in%20C5%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3D(COUNTIF(Ackerman!B2%3AB15%2C%22yes%22)%2BCOUNTIF('Williams%2C%20S.'!B2%3AB15%2C%22yes%22))%2F(COUNTA(Ackerman!B2%3AB15)%2BCOUNTA('Williams%2C%20S.'!B2%3AB15))%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1365294%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1365294%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F658222%22%20target%3D%22_blank%22%3E%40RobynMedcalf%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20idea%20is%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fformula%2F3d-sumif-for-multiple-worksheets%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fformula%2F3d-sumif-for-multiple-worksheets%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20put%20names%20of%20all%20your%20sheets%20into%20the%20range%20and%20name%20this%20range%20as%20%22sheets%22%2C%20formula%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFERROR(%20%0A%20%20SUMPRODUCT(COUNTIF(INDIRECT(%22'%22%26amp%3BSheets%26amp%3B%22'!%22%26amp%3B%22C5%22)%2C%22yes%22))%2F%0A%20%20SUMPRODUCT(COUNTIF(INDIRECT(%22'%22%26amp%3BSheets%26amp%3B%22'!%22%26amp%3B%22C5%22)%2C%22%26lt%3B%26gt%3B%22))%2C%0A0)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

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!

 

2 Replies
Highlighted

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

Highlighted

@RobynMedcalf 

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)