+ Reply to Thread
Results 1 to 6 of 6

Excel COUNTIF function for 3D workbook

  1. #1
    Registered User
    Join Date
    01-20-2016
    Location
    https://t.me/pump_upp
    MS-Off Ver
    Microsoft Excel 2010
    Posts
    2

    Question Excel COUNTIF function for 3D workbook

    Is there a way to use the COUNTIF function in Excel for 3D calculation of a text entry? Specifically trying to count how many "yes" or "no" answers provided in cell A12 on Sheet1 through Sheet61 with the total appearing in separate summary sheet. If COUNTIF can't be used, what formula syntax would give me the solution?

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Excel COUNTIF function for 3D workbook

    Hi,

    You can create a shorter formula, though it requires INDIRECT and so is volatile. As such, I would prefer the longer:

    =SUMPRODUCT(N(CHOOSE({1,2,3,4,5,6},Sheet1!A12,Sheet2!A12,Sheet3!A12,Sheet4!A12,Sheet5!A12,Sheet6!A12)="Yes"))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Excel COUNTIF function for 3D workbook

    Or the service sheet with links to
    =Sheet1!A12
    =Sheet2!A12
    ......
    =Sheet61!A12

    and COUNTIFS

    @XOR LX. 61 sheets but not 6 :-)

  4. #4
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Excel COUNTIF function for 3D workbook

    ...Or use a formula in A1 or any other cell on each sheet like =N(A12="Yes"), then just use SUM

    =SUM(Sheet1:Sheet61!A1)
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  5. #5
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742
    Quote Originally Posted by BMV View Post
    @XOR LX. 61 sheets but not 6 :-)
    Oops! Then my suggestion is not really an option!

  6. #6
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Excel COUNTIF function for 3D workbook

    @XOR LX why not. CHOOSE has 254 arguments limit

    In additional to #4 . The formula could be written to every sheet at once. Select all sheets and write formula in one. Press ENTER.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel Formula Help with Countif Function
    By TJStephens in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-15-2019, 01:00 PM
  2. [SOLVED] Excel countif function not summing up
    By Lsinshirley in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-06-2011, 09:14 AM
  3. worksheet.countif function from outside workbook
    By snakeye in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-25-2006, 10:45 AM
  4. Excel COUNTIF Function - a range as criteria ?
    By joes in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-06-2006, 09:15 AM
  5. [SOLVED] Excel COUNTIF function
    By Viks in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-03-2006, 09:20 AM
  6. Excel function countif???
    By Raj7 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-26-2005, 07:05 PM
  7. [SOLVED] Excel countif function
    By bkcthecat in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-13-2005, 01:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1