+ Reply to Thread
Results 1 to 3 of 3

How do i create macro to count number of "Y" or "N" in different worksheets

  1. #1
    Registered User
    Join Date
    09-21-2009
    Location
    Austria
    MS-Off Ver
    Excel 2003
    Posts
    1

    How do i create macro to count number of "Y" or "N" in different worksheets

    Hi,
    i have a many worksheets in which each cell contain either "Yes" or "No". I want to write a macro which counts number of "Yes" in each corresponding cell (for example in A1 cell ) in all worsheets and put the answer to a new worksheet(for example NumberOfYes) in the corresponding cell (that is A1). A template excel file is attached for clarification of query.

    Regards,
    Tehseen
    Attached Files Attached Files

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,255

    Re: How do i create macro to count number of "Y" or "N" in different worksheets

    Hello tehseen,

    I downloaded your file and received the message that the "file is in unrecognizable format". Perhaps you should reload your attachment.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: How do i create macro to count number of "Y" or "N" in different worksheets

    You could do this with formulae though conditional summation across sheets is generally a poor performer as it requires volatile Arrays / Sumproducts.

    It would be easier (based on your sample file & question) to convert the underlying source values from non-numerics (Yes/No) to numeric (1/0) and use Custom Format to display Yes / No accordingly...

    To illustrate using your file:

    Group Sheets Sample1 & Sample2
    Highlight B2:E5
    Run Edit -> Replace: replacing Yes with 1
    Repeat Replacing No with 0
    Apply a Custom Format of: "Yes";;"No"
    UnGroup the sheets...

    You should now find you have the same display as before in terms of Yes/No but you can now do a simply 3D non-conditional Summation,eg:

    NumberofYes!B2: =SUM(Sample1:Sample2!B2)
    applied across matrix

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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