+ Reply to Thread
Results 1 to 8 of 8

Counta / Countif

  1. #1
    Registered User
    Join Date
    05-30-2016
    Location
    Scotland
    MS-Off Ver
    2013
    Posts
    5

    Counta / Countif

    Hi everyone,

    I am new to this forum and beginner with VBA.

    If someone would be able to help would be great.

    I got one sheet as a “StatusSheet” and I want to count ( yes, no, N/A ) on separate sheets ( total sheets 21, Area_1, Area_2, Area_3, ….Area_21).

    The Yes, No, N/A value always will be saved in the same column, between AQ4:AQ200.

    At them moment I got

    =SUM((COUNTIF(‘Area_1’!$AQ$4:$AQ$200,"Yes"))) ‘to count YES value
    =SUM((COUNTIF(‘Area_1’!$AQ$4:$AQ$200,"No"))) ‘to count No value
    =SUM((COUNTIF(‘Area_1’!$AQ$4:$AQ$200,"N/A"))) ‘to count N/A value

    And

    =COUNTA(‘Area_1’!$AQ$4:$AQ$200) ‘ to count total in the column.

    I would like to have this in macro and put this in the loop.

    is any chance to do this ?

    Regards
    M

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,567

    Re: Counta / Countif

    Try this.

    1 In the status sheet put Area_1 in A2 and copy down to A22.

    2 In B1 put 'Yes', in C1 put 'No' and in D1 put 'N/A'.

    3 In B2 put this formula.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    4 Copy the formula across and down to B2:D22.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    05-30-2016
    Location
    Scotland
    MS-Off Ver
    2013
    Posts
    5

    Re: Counta / Countif

    Hi Norie,

    Thank you for reply.

    Sorry but I am looking for something different.
    I want to count Yes, No, N/A on each sheet (sheet Area1, Area2, Area3, Area4, Area5, Area6, Area7, Area8, Area9, Area10, Area11,etc.) and display value ( number ) on StatusSheet.

    I think I would need to use loop ( For) and specify the range, but how I can specify the sheets range ( Area1 to Area13).

    I have done small example, please see attached JPGs.

    Please let me know if would be possible to do this?

    Regards
    M
    Attached Images Attached Images

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,567

    Re: Counta / Countif

    What I posted will count Yes, No and N/A on sheets Area_1 to Area_21.

    If you want to have the results in a specific range then it would be easy to adjust the references in the formula I posted.

    You would need to adjust some other things too, for example in the original post the sheet names had an underscore but now you seem to indicate they don't but as long as you have the actual sheet names on your sheet that doesnt' matter.

    The other thing that might need adjusted is the column, in the original post it was column AQ but in the image you've posted it appears to be column S.

    By the way, even if you wanted code to do this you wouldn't need a loop.

  5. #5
    Registered User
    Join Date
    05-30-2016
    Location
    Scotland
    MS-Off Ver
    2013
    Posts
    5

    Re: Counta / Countif

    Hi Norie,

    Thank you for your help.
    Yes, you are right and the code works fine.

    The print screens what I used were just for reference.

    I got one more last ( hope ) question.

    How to count all total number of YES, NO, N/A in the columns in Area1 to Area13?

    Thank you for your help.
    M

  6. #6
    Registered User
    Join Date
    05-30-2016
    Location
    Scotland
    MS-Off Ver
    2013
    Posts
    5

    Re: Counta / Countif

    Done something like this.
    doesn't looks nice but does the job
    =COUNTIF(INDIRECT($A2 & "!$s$1:$s$200"), B$1)+COUNTIF(INDIRECT($A2 & "!$s$1:$s$200"), C$1)+COUNTIF(INDIRECT($A2 & "!$s$1:$s$200"), D$1)

  7. #7
    Registered User
    Join Date
    05-30-2016
    Location
    Scotland
    MS-Off Ver
    2013
    Posts
    5

    Re: Counta / Countif

    Looks like the problem begin when instead of name Area1 we want to put space between Area and 1 “Area 1”.
    Is there any way or the name of the spreadsheet always need to be one word?

    Regards
    M

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,567

    Re: Counta / Countif

    No you can have a space in the name, just need to adjust the formula slightly to enclose the sheet name in single quotes.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    As for the previous question about counting the total number of Yes, No and N/A, why not just sum the result of the three existing formulas for each sheet?

    For example in E2 and copied down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ 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. [SOLVED] Countif / Counta
    By joshag in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-22-2015, 10:44 AM
  2. Nesting If with Counta and Countif
    By henryBukowski in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-30-2014, 07:21 AM
  3. Excel 2007 : CountIF or CountA
    By bjohnsonac in forum Excel General
    Replies: 3
    Last Post: 01-24-2011, 12:46 PM
  4. Excel 2007 : CountA and CountIF
    By DentonHTHS in forum Excel General
    Replies: 3
    Last Post: 04-05-2010, 12:44 AM
  5. CountIF, CountA,Which one? or neither?
    By amy in forum Excel General
    Replies: 2
    Last Post: 07-20-2005, 04:05 PM
  6. COUNTA, COUNTIF?
    By Newbie in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-18-2005, 08:06 AM
  7. Counta + Countif
    By maswinney in forum Excel General
    Replies: 0
    Last Post: 02-23-2005, 04:54 PM

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