+ Reply to Thread
Results 1 to 6 of 6

CountA & CountIF using specific cells selection

  1. #1
    Forum Contributor
    Join Date
    08-16-2012
    Location
    Sefton
    MS-Off Ver
    MS Office 365 ProPlus
    Posts
    131

    CountA & CountIF using specific cells selection

    I'm working on a sensitive document, so I'll try and explain my issue as best I can. I didn't create this document myself - the company I work for has asked for a modification, and if I can figure this out it would go a long way to completing that modification.

    There's 2 columns;
    A - will contain a 1, 0 or x depending on responses elsewhere
    B - will contain a comment based on responses elsewhere

    It uses this formula to create a score for the section;
    =COUNTA(B2:B20)-COUNTIF(A2:A20,"x")

    What I need is a modification to the formula if, for example, rows 4-8, 11, 13 and 18-20 were not required for a specific customer. So the formula would only contain (from both columns A+B) rows 1,2,3,9,10,12,14,15,16,17.
    I can't simply delete or reorder the questions for a number of reasons - namely how they relate to other sections of the document and how it gets imported into our software - and that the same template is used for multiple customers and it's only specific ones where this difference is required.

    Our software can hide the questions from view - however it will still take the score based on all questions.

    I've tried just selecting the relevant cells, but I get the "too many arguments" error.
    I've also tried the formula like this;
    =COUNTA(Range2)-COUNTIF(Range1,"x")
    Where range 1 & 2 are the required cells from each column. It worked with just Range 2 - i.e. =COUNTA(Range2)-COUNTIF(A2:A20,"x") although I don't think the score was accurate. When I put both Ranges in I get "#VALUE!"

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: CountA & CountIF using specific cells selection

    Assuming you will be hiding the rows for irrelevant questions,
    This regular formula returns the count of visible non-blank cells minus the count of visible cells containing "x"
    Please Login or Register  to view this content.
    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Contributor
    Join Date
    08-16-2012
    Location
    Sefton
    MS-Off Ver
    MS Office 365 ProPlus
    Posts
    131

    Re: CountA & CountIF using specific cells selection

    Your formula does not give the required result.

    There are no completely irrelevant questions, only occasional ones. These will not been hidden within excel but within our system - the excel document is run through our software and a report is created for the customer. As this is the main template I cannot edit the positioning of any of the questions because they will all be needed for other customers. I can not rearrange the order of the questions so I can simply select the range required, as this will affect the other pages of the document (over 70 pages within the document with multiple formulas back and forth between them all)

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: CountA & CountIF using specific cells selection

    Is there another column that can indicate which rows relate to the particular customer?
    Look at it this way.
    In your mind, how do you determine which rows should be included?
    The ones with the customer's name in column D ?

    Whatever logic you use to determine in your mind which rows should be used, can probably be used in a countifs formula.
    Something like
    =COUNTA(B2:B20)-COUNTIFS(A2:A20,"x",B2:B20,"customername")

  5. #5
    Forum Contributor
    Join Date
    08-16-2012
    Location
    Sefton
    MS-Off Ver
    MS Office 365 ProPlus
    Posts
    131

    Re: CountA & CountIF using specific cells selection

    Never mind I've figured it out - it's so simple, I was trying to make it too complex.
    Just need to create a list of required responses from columns A and B in an empty part of the page, where they all line up and I can use the original formula.

    Thanks for trying at least.

  6. #6
    Forum Contributor
    Join Date
    08-16-2012
    Location
    Sefton
    MS-Off Ver
    MS Office 365 ProPlus
    Posts
    131

    Re: CountA & CountIF using specific cells selection

    @ Jonmo1
    There isn't another column - the document is approx 10 years old, and this amendment has only just become required from a customer who has had this document for the past 10 years.
    The questions to ignore have been determined by both them and us in meetings - for the moment they're just highlighted to show which ones they are.

+ 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 2007 : CountIF or CountA
    By bjohnsonac in forum Excel General
    Replies: 3
    Last Post: 01-24-2011, 12:46 PM
  2. Excel 2007 : CountA and CountIF
    By DentonHTHS in forum Excel General
    Replies: 3
    Last Post: 04-05-2010, 12:44 AM
  3. [SOLVED] Errors in COUNT, COUNTA, COUNTIF when counting merged cells
    By Outback in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-07-2006, 12:35 PM
  4. [SOLVED] COUNTA, COUNTIF?
    By Newbie in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-18-2005, 08:06 AM
  5. 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