# COUNTIFS not counting all numbers in one cell

1. ## 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. ## 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.

3. ## Re: COUNTIFS not counting all numbers in one cell

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

4. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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".

10. ## 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. ## 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. ## 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. ## Re: COUNTIFS not counting all numbers in one cell

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

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

#### 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