Comments on: Excel COUNT and COUNTA functions to count cells

This short tutorial explains the basics of the Excel COUNT and COUNTA functions and shows a few examples of using a count formula in Excel. You will also learn how to use the COUNTIF and COUNTIFS functions to count cells that meet one or more criteria. Continue reading →

Comments page 4. Total comments: 115

  1. Hello
    I havew in my column A the fellowing values
    B-COM-TES-001
    V-MAN-ITF-005
    S-COM-TES-020
    V-COM-TES-008

    if I want to count the combine (COM-TES)In all the column
    How can I do

    Merci

    1. I have date in cell like below:
      TC1D 2
      TC1-D 5
      I want to count the date cell for both at a time and sum of the same.
      In that way like my count will come 2 and sum 7.
      Please help.

    2. =COUNTIF(J4:J7,"*com*")
      j4:j7 is data coloumn.

  2. Hello,

    Do you also have a tutorial on COUNT and COUNTA in Google Sheets?

    Yours,

    Robert

  3. Hi there,

    I want to count the number of blank cells and non-blank cells in column B,
    if column A is X and column C is X, Y and Z.

    Thank you

  4. I have data with family includes Self, spouse, children, parents. i want separate count of how many single child family and how may 2 children family are there in data.

    kindly help

  5. Hi,

    I would love to have some help on this:

    I have B5:B66 range and I want count ONLY if a certain value occurs 3 times or more (values 8,0

    Example: 8599962555
    I need to count: 999 = 1
    and: 555 = 1
    Then : 1+1 = 2

    How can I do this???
    thanks in advance!

  6. Extremely Helpful website.

  7. Hi, i have a certain name (wilium) in column A and against this name i have 2 option, 1 Done and 2 Inprogress. i want that i count done again wilium and count inprogress against wilium separately. which formula will work for it??

  8. how can I make formulas appear in all cells instead of the values

    1. ctrl+`

  9. I have query divide this no as an following way in different column if 4,50,00, 000 given then we will get
    2,00,00,000
    1,00,00,000
    1,00,00,000
    50,00,000

  10. Ok guys/girls i'm pulling my hair out with this formula, i'm fairly confident the problem is in the two cells with times in. cells h50:h5000 have two times combined in a cell with - as a division, all i need is the second part but using 'right' function doesn't appear to work as part of a larger countifs. these cells are imported and can be 1000's of rows long so creating another column to separate is awkward.

    f50:f5000=13:59 h50:h5000=12:33-17:00

    =COUNTIFS(C$50:C$5000,A6, X$50:X$5000, ">0", Y$50:Y$5000, ">0",F$50:F$5000, ">" & RIGHT(H$50:H$5000,5))

    The formula by itself (ctrl, shift, enter) appears to work fine but I require the other criteria.

    =COUNTIF(F$50:F$5000, ">" & RIGHT(H$50:H$5000,5))

    Any help would be greatly appreciated.

    Andy

  11. Hi,
    I am trying to use countifs to count no of cell which meet multiple criteria where each criteria has got different no fo rows and column compare to other.
    for example:
    i want to count no of cell from column D,E and F if value in column A is equal to X.
    Thanks in advance

  12. Hi Mam
    This list is % or marks
    100
    93
    91.4
    90.8
    90.6
    90
    88.6
    86.4
    85.2
    80.8
    79.2
    78.4
    75.8
    75
    73.6
    73.6
    73.6
    73.6
    73.6
    73.6
    73.6
    73.6
    71.6
    71.6
    71.4
    70
    69.6
    68.6
    66.2
    65.2
    I want result between 91 to 100 how many student
    81 to 90
    71 to 80
    61 to 70
    51 to 60
    41 to 50
    33 to 40
    below 33
    what formula use in it.

    1. =countifs(range of mark,">=71",range of mark,"<=80")

  13. Hello I am trying to find a formula which allows me to add 2 different words in 2 different columns with drop down menus in each. Here are an example of the words:

    "A" for Column E

    For Column G
    ACV, AMV, FIRE, PER INJ, OTHER, PMV2, PMV4, PMVO, PRO DMG, SUPLMT, WATER, WPN/ND, AV GND

    A certain combination will only work for a specific column. Example:
    Accident Class:
    A
    B
    C
    D
    E
    F
    OHR
    NR
    Each of these only work with one word at a time, but all of the combinations for "A" need to be tracked in a column (A3) for example.
    "B" would tracked in A4 and "C" would be tracked in A5 and so on. If B and ACV were in their respective columns (Say E5 and G5)on one sheet, then those 2 numbers would be tracked on another sheet on A3 as a number.

    I hope this explains it well enough, thank you.

    1. I found the solution, disregard, thank you.

  14. I am using this formula =IF(MAX($A$4:A263)=SUMPRODUCT(1/COUNTIF($B$5:B264,$B$5:B264)),"",(SUMPRODUCT(1/COUNTIF($B$5:B264,$B$5:B264)))) For Serial Number but it shows the sr no after missed some columns. whereas the sr. number column should be nill until the next particular not comes.

  15. I want to count enteries in a column and then multiple the number of entries by a set number. In other words, I have a spreadsheet, where different columns have different values. IF an entry is made in column A, it should be counted as 1 entry. But the value of second column is double and that of third is x 5.

    So I want to be able to count the number of entries in a particular column and then multiply it by 2/5/10. Whats the best formula for this?

    Thanks for your help.

  16. Write a Formula to count the number of Active Employees in Each City

    Employees Location Status
    Employee1 Manila Active
    Employee2 Manila Active
    Employee3 Mumbai Active
    Employee4 Surrey Active
    Employee5 Mumbai Inactive
    Employee6 Mumbai Active
    Employee7 Mumbai Active
    Employee8 Manila Inactive
    Employee9 Manila Active
    Employee10 Surrey Inactive
    Employee11 Surrey Active
    Employee12 Surrey Active
    Employee13 Manila Active
    Employee14 Manila Active
    Employee15 Manila Active
    Employee16 Manila Active
    Employee17 Manila Inactive
    Employee18 Surrey Active
    Employee19 Surrey Active
    Employee20 Mumbai Active

  17. HOW TO COUNT, I HAVE DATA OF STUDENTS FROM VARIOUS STATES, I WANT TO COUNT HOW MANY STUDENTS ARE FROM DELHI AND THERE SURNAME IS "REDDY" ? PLZ HELP

    1. =COUNTIFS(G2:G11,"****reddy",H2:H11,"delhi")

  18. I have want to count a range of cells. The =COUNTA('Employee Edu-Skills History'!A31:A55,"*")count the formulas. I only want to count the text in the cells

  19. Hello

    i want to count Yes and No in the rows

    That was no issue .
    But i want to stop the you after the first the NO

    has sombody a idea how to handle it ?

  20. I was Searching this topic since 3 weeks Now i Found in this articular Thanks a lot. its working in Drop Down list active Words.
    =COUNTIF(A2:A15, "apples")

  21. I have three worksheets two with the years dates in B3:NB3. the a1 column has text in it. I am trying to calculate how many time in a rolling 90 days the product in a1 sell mp I'm using this
    =COUNTIFS(A1,"<=today()-90",'2016'!B4:NB4,"MP")

    It count the MP but
    ie if today is 5/24/16 it should not count any mp before 2/27/16

    can you tell me what I'm missing. I'm getting "Value" in the output field

    thanks

  22. can u send me a drop down list examples in excel where there is huge data for e.g

    Divison Zone Region Area HQ
    Divan West Mumbai Mumbai Mumbai
    Divan West Mumbai Mumbai Goa
    Divan West Pune Pune Pune
    Divan West Pune Pune Pimpri
    Divan West Ahmdabd Rajkot Rajkot
    Siman North Delhi dELHI Delhi
    Siman North Delhi jAIPUR Kota
    Siman North Delhi Jaipur Jodhpur

    This list is huge. Please help how to prepare drop down list

    if we click on Division the Divison will show then after clicking on Zone the zone and clicking on the specific zone the zones area will reflect with HQ automatically.

    Please help.

  23. Pretty good ma'm, bundle of thanks :)

  24. how can we use "if" function when there is either text or number in cell..

    if the cell contain text then answer should be "Yes" or else the cell contain number then answer shold come "No"

    1. Please reply

      CUSTOMER1(CONTAINER1) AVAILABLE
      CUSTOMER2(CONTAINER1) AVAILABLE
      CUSTOMER1(CONTAINER2) AVAILABLE
      CUSTOMER2(CONTAINER2) AVAILABEL

      ANSWER SHOULD BE CUSTOMER1 2

    2. Hello Rinju,

      You can use a formula similar to this:

      =IF(ISTEXT(A1), "Yes", IF(ISNUMBER(A1), "No", ""))

      Additionally, you can check for blank cells:

      =IF(ISTEXT(A1),"yes", IF(ISNUMBER(A1),"no", IF(ISBLANK(A1),"blank","")))

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)