+ Reply to Thread
Results 1 to 13 of 13

COUNTIFS not counting all numbers in one cell

  1. #1
    Forum Contributor
    Join Date
    05-04-2008
    Posts
    103

    COUNTIFS not counting all numbers in one cell

    I have the following formula in column "P". This formula as you can see looks at each cell that has "ETT" and counts the cell in column "P". The problem I am having is that it counts one number. Some cells in Column "P" can have more than one number separated by a comma. How do I get it to count each number before going to the next cell so I have a true number at the bottom? Thank you.


    =COUNTIFS(N2:N73,"=ETT")

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: COUNTIFS not counting all numbers in one cell

    taichi56,

    Uhhh.... what??
    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.
    To attach a file, click the "Go Advanced" button and then click the paperclip icon to open the Manage Attachments dialog.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Contributor
    Join Date
    05-04-2008
    Posts
    103

    Re: COUNTIFS not counting all numbers in one cell

    Here is my attachment. Hope this makes sense. Thank you for your help.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    04-08-2013
    Location
    Fort Worth, Texas
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: COUNTIFS not counting all numbers in one cell

    Can you simply put a "*ETT" or "*ETT*" in the formula... i guess it depends upon what other info may be in the same cell that contains the ETT

  5. #5
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: COUNTIFS not counting all numbers in one cell

    In P6 you have the number 12999768453 which is formatted to show commas so it displays as 12,999,768,453. Should it be counted as one number or 4? If it is supposed to be counted as 4 numbers, why was it entered as a single number and then formatted to show commas instead of being entered as distinct numbers separated by commas like in cell P3?

  6. #6
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: COUNTIFS not counting all numbers in one cell

    either way, this formula should work for you, it provides the desired answer of 10 given your sample data:
    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    05-04-2008
    Posts
    103

    Re: COUNTIFS not counting all numbers in one cell

    This formula counted 100 but there are 54 occurrences of ETT. How do I keep it from counting the blank cells in cloumn "P" that is associated with column "N"? Thank you.

  8. #8
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: COUNTIFS not counting all numbers in one cell

    taichi56,

    You would add an extra condition in the formula to ignore blank cells in column P:
    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    05-04-2008
    Posts
    103

    Re: COUNTIFS not counting all numbers in one cell

    Something is not coming out right. I went and put the data in the attached EXCEL sheet so you can see what it is doing. It should count out "63" ETT, but it is coming out as "92".
    Attached Files Attached Files

  10. #10
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: COUNTIFS not counting all numbers in one cell

    Because all of your numbers are 6 digits, there is no need for the Text portion of the formula. I also put in a check to ignore cells containing N/A:
    Please Login or Register  to view this content.

    That returns the correct result of 63.

  11. #11
    Forum Contributor
    Join Date
    05-04-2008
    Posts
    103

    Re: COUNTIFS not counting all numbers in one cell

    That worked great! One more question if I want to call those rows from another worksheet can i just put it in this way? I did it with COUNTA but it doews not seem to work with this. Or should I start a new thread? Thank you for the excellent help.

    Please Login or Register  to view this content.

  12. #12
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: COUNTIFS not counting all numbers in one cell

    You'll need to qualify each range reference with the sheet name, not just the first one:
    Please Login or Register  to view this content.

  13. #13
    Forum Contributor
    Join Date
    05-04-2008
    Posts
    103

    Re: COUNTIFS not counting all numbers in one cell

    You are awesome. Thank you very much for your help!

+ 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] Using cell numbers to replace numerals in COUNTIFS criteria
    By tsig in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-30-2013, 02:03 AM
  2. COUNTIFS OR...multiple countifs without duplication in the numbers
    By HooligaD in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-21-2012, 09:53 AM
  3. Replies: 0
    Last Post: 12-16-2011, 09:01 AM
  4. Countifs not counting, returns #value
    By Qualo_Jinn in forum Excel General
    Replies: 3
    Last Post: 12-09-2011, 04:13 PM
  5. Array or countifs for counting survey responses
    By friendlylad in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-04-2009, 10:01 AM

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