Comments on: How to use Excel COUNTIFS and COUNTIF with multiple criteria

The tutorial explains how to use COUNTIFS and COUNTIF formulas with multiple criteria in Excel. You will find a number of examples for different data types – numbers, dates, text, wildcard characters, non-blank cells and more. Continue reading →

Comments page 17. Total comments: 2071

  1. ok, so im trying to figure out how to add 2nd variable onto my formula to count them both. but i cant get anything to work.

    =COUNTIFS(DUE!A:A,"DLA",DUE!C:C,"250",DUE!N:N,"V")

    now this currently works, but i also want to add Y that is also in the same column as "V"

    any help would be greatly appreciated

  2. Create a formula that displays the coupon amount for each customer based on department. Remember! The standards of professionalism require that you DO NOT include numbers in formulas if the numbers might change in the future. These coupons will change in the future.

    Electronics—$25 coupon.
    Toys—$20 coupon.
    Sports—$15 coupon.
    Shoes—$10 coupon.
    Every other department—$5 coupon.

    1. Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get? Give an example of the source data and the expected result.
      Thank you!

  3. Status Group Name
    A B C
    1 Solved Everglow Mike

    2 Closed GreenDot Jess

    3 Pending Pledis Mike

    4 Closed Pledis Gus

    5 Solved Greendot Jess

    6 Solved Greendot Charlie

    7 Pending Everglow Gus

    I wanna count how many Gus, Jess, Mike has a Solved or Closed Status from Greendot in to one cell. when i have the formula below the error #spill! shows..

    =COUNTIFS(C:C, {"Gus","Jess,"Mike"}, B:B, "Greendot", A:A, "Solved")+COUNTIFS(C:C, {"Gus","Jess","Mike"}, B:B, "Greendot", A:A, "Closed")

    please help i'm very confused

    1. i figured out the formula

      =COUNTIFS(C:C, "Gus", B:B, Greendot, A:A, "Solved")+COUNTIFS(C:C, "Gus", B:B, Greendot, A:A, "Closed")+COUNTIFS(C:C, "Jess", B:B, Greendot, A:A, "Solved")+COUNTIFS(C:C, "Jess", B:B, Greendot, A:A, "Closed")+COUNTIFS(C:C, "Mike", B:B, Greendot, A:A, "Solved")+COUNTIFS(C:C, "Mike", B:B, Greendot, A:A, "Closed")

      but honestly is there no way to shorten this formula?

      1. Hi!

        The formula below will do the trick for you:

        =SUM(COUNTIFS(C:C, {"Gus","Mike","Jess"}, B:B, "Greendot", A:A, {"Solved";"Closed"}))

        I hope I answered your question.

  4. I need a formula to count if columns A, B, C, OR D have "*apples*" AND column E is not blank.

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =SUM((ISNUMBER(SEARCH("apples",A1:A10,1))+ISNUMBER(SEARCH("apples",B1:B10,1))+ISNUMBER(SEARCH("apples",C1:C10,1))+ISNUMBER(SEARCH("apples",D1:D10,1)))*NOT(ISBLANK(E1:E10)))

      I hope my advice will help you solve your task.

  5. Hi, I doing a sale excel were I need to count all the same product multiply by the amount of order.

    Order product
    2. Apple
    3. Orange
    1 apple

    Were apple should be 3 and orange will be 3.

  6. Hi, I doing a sale excel were I need to count all the same product multiply by the amount of order.

    Order product
    2. Apple
    3. Orange
    1 apple

    Were apple should be 3

  7. Hi,

    Thanks for this article. My problem now is it doesn’t count when one cell has the same text, especially two names of the same person.

    The text goes like this:

    G2:G10 = James
    G11:G20 = James Pattinson

    Formula:

    =(COUNTIFS(F:F, $K$5, G:G,{"James", "James Pattinson"},D:D,"Done"))

    The formula refuses to count cells that have the same first keyword. Please advise on how to solve this.

    Paul

    1. Hello!
      I believe the following formula will help you solve your task:

      =SUM(COUNTIFS(F:F, $K$5, G:G,{“James”, “James Pattinson”},D:D,”Done”))

      or

      =COUNTIFS(F:F, $K$5, G:G,“James”&"*",D:D,”Done”)

      I hope my advice will help you solve your task.

  8. Hello Alexander,

    Works like a charm :D
    Thank you very much for your help.

    Kind regards,
    Mitchell

  9. Hello,

    In the example "How to use COUNTIFS with wildcard characters"

    How would I count the number of "Mike" in column B when the date in C equals the date in D?

    Kind regards,
    Mitchell

    1. Hello!
      Please try the following formula:

      =SUMPRODUCT((B2:B10="Mike")*(--(C2:C10=D2:D10)))

      Hope this is what you need.

      1. Hello Alexander,

        Thanks for your feedback. Unfortunately I don't get the desired result, but a "value not available error".

        So what I'm trying to do:
        On sheet 1 I have information on deliveries. (suppliers (column N), promise dates(column G), actual delivery dates(column H), and many more)

        On sheet 2 I have listed all suppliers(column A) and added a column(B) with a formula which counts the number of times a supplier made a delivery =COUNTIF(Sheet1!$A$2:$P$268;A2)
        Now I want to add a column(C) which only counts the times this supplier actually delivered on the promise date.(Sheet 1, G=H)

        With your help I tried below formula, which returns a "value not available error".

        =SUMPRODUCT((Sheet1!$A$2:$N$268="A2")*(--((Sheet1!$G$2:$G$268:Sheet1!$H$2:$H$268))))

        Based on above information, is there a formula which can calculate this without adding columns to sheet 1?

        Kind regards,
        Mitchell

        1. Hello!
          I wrote this formula based on the description you provided in your original comment. Please note that if you’d provided me with the precise and detailed description of your task from the very beginning, I’d have created a different formula that would have worked for your case.

          =SUMPRODUCT((Sheet1!$N$2:$N$268=A2)*(--(Sheet1!$G$2:$G$268=Sheet1!$H$2:$H$268)))

          I hope this will help

  10. Thanks, Svetlana!
    A lifesaver, as usual.

  11. Hi! Very useful article. I'm struggling with figuring out a formula.

    I've got two sheets: the first with raw data, the second with a summary table I'm trying to configure.

    Here's a screenshot of the summary table sheet: https://www.dropbox.com/s/2xpyrccqrgmpqe8/Capture%20d%E2%80%99%C3%A9cran%2C%20le%202021-02-05%20%C3%A0%2016.43.41.png?dl=0

    Here's a screenshot of the raw data sheet: https://www.dropbox.com/s/yb85pb8d759z4k0/Capture%20d%E2%80%99%C3%A9cran%2C%20le%202021-02-05%20%C3%A0%2016.44.08.png?dl=0

    I'm trying to use COUNTIF to count the number of entries that match the following:

    Data!A:A must match Summary!A7 (Grade
    Data!B:D must match Summary!B7 (Grade)
    Data!J must be between C3 and C4 (Date Range)

    I can't seem to figure it out for the life of me. I've tried SUM COUNTIFS with multiple criteria and data ranges to no avail. Any help/insight would be much appreciated!

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =COUNTIFS(J:J,">"&$C$2,J:J," < "&$C$3,A:A,A7,B:B,B7) + COUNTIFS(J:J,">"&$C$2,J:J," < "&$C$3,A:A,A7,C:C,B7) + COUNTIFS(J:J,">"&$C$2,J:J," < "&$C$3,A:A,A7,D:D,B7)

      In this formula, correct the cell references to match your data and worksheet names. I cannot verify its work, since I do not have your data. To understand this formula, I recommend reading the article above.
      I hope I have answered your question. If something is still unclear, please feel free to ask.

      1. It worked! Thanks Alexander!

        I was stumped with trying to match data across 3 columns, but your solution nailed it by simply adding them up. Thanks so much.

  12. Hi, How can I count the OR# per per loan type and status using countifs, below the sample

    STATUS LOAN TYPE OR#
    2 HP 123
    1 HP
    3 HP 456

    1. STATUS -LOAN TYPE-OR#
      1 - HP -123
      2 -HP
      3 -HP -456

      1. Hello!
        I hope you have studied the recommendations in the tutorial above. It contains answers to your question: COUNTIFS formula with multiple criteria
        Please check the formula below, it should work for you:

        =COUNTIFS(A2:A4,1,B2:B4,"HP")

        I hope it’ll be helpful.

  13. Hi, I have a cell range with a start date and a cell range with an end date and I'm trying to use the TODAY function to return the amount of people within the two date ranges when I load the sheet - any idea?

  14. Hi, I am trying to set up a spreadsheet to check whether colleagues are completing specific tasks (that are recorded on a different spreadsheet), at least once within each 28 day period and to count how many of the task they did within the period. The best way I can think of it to to pull the count for each date and then have a separate tab that then works off that count to be certain the colleague is competent over the year. But I'm struggling to get the correct formula. What I need is: the count if column M on spreadsheet A is equal to cell A2 on spreadsheet B AND the date within column Q on spreadsheet A is equal to B1 on spreadsheet B - spreadsheet A being the one that contains the original data and spreadsheet B being the new one I am setting up. Is there any way to do this?

    1. Hello!
      Without seeing your data it is difficult to give you any advice. You have not specified what data you want to retrieve into the new table.
      Please check out the following article on our blog, it’ll be sure to help you with your task:
      Vlookup multiple matches based on multiple conditions
      You can also solve your problem without formulas using a pivot table.
      I hope I answered your question. If something is still unclear, please feel free to ask.

  15. IF I PREPARED A ATTENDANCE SHEET I WANT TO COUNT P,D,C,O IN ONE CAN U TELL ME HOW

    1. easiest way to count this is by using formula Countif or Countifs. See the examples in the article.

    2. Hi,
      I’m sorry but your task is not entirely clear to me.
      Please describe your problem in more detail. What are P, D, C, O? Letters? Columns? Values? What do you want to calculate? Include an example of the source data and the result you want to get. It’ll help me understand your request better and find a solution for you.

  16. Hi,

    I am trying to use countifs using OR logic to find the number of bacterial strains with my gene of interest.

    The formula I'm using is:

    =SUM(COUNTIFS(BB:BB,("*"&BO3&"*", "*"&BP3&"*", "*"&BQ3&"*") BO3, BP3 and BQ3 are all text cells

    The data range is filled with lines of text corresponding to all genes that are present for that sample, I am interested in knowing how many samples contain gene x, or gene y, or gene z.

    My issue here is that only the cell BO3 is highlighted as an actual reference cell in the formula, any of the other cells I try to reference after the first one (Ie BP3 and BQ3) does not show up as a referenced cell.

    It would appear that the formula only lets you have one wildcard, while all other criteria have to be exact matches? I hope this isn't the case and I'm just confused.

    Thank you in advance.

    1. Hello!
      Without seeing your data it is difficult to give you any advice. You spelled the COUNTIFS formula incorrectly. For each criteria, you need to specify criteria_range:

      COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

      I think you should use the COUNTIF function as described in this section above.
      I hope I answered your question. If something is still unclear, please feel free to ask.

  17. *executed Contract per month
    MONTHS ANN VAL GLENN BELLA JO SEB
    January
    February
    March
    April
    May
    June
    July
    August
    September
    October
    November
    December

    1. Hi,
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred.

  18. =COUNTIFS('C.O MONITORING'!X8:X992,"MOIA",'C.O MONITORING'!AE8:AE992,"BELLA")
    this is my old formula. counting the number of contracts executed by a certain person "Bella"

    for new formula
    I want to count the number of the contract executed by "BELLA" per months (with a specific date)

  19. how to use countif for one date range (mont and year only) and specific text

    1. Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get? Give an example of the source data and the expected result.

  20. Hi,

    I have data that shows me how many times different projects pass or fail, but I want to count how may times a project fails on different days, i.e. not to count it twice if it failed twice on the same day. See simplified data below; I want to return Project 101 as 'Failing' 3 times, i.e. on 3 days, not 4 individual occasions.

    A B C
    Date Project Result
    01 Jan 101 Fail
    01 Jan 101 Fail
    02 Jan 102 Fail
    02 Jan 101 Fail
    03 Jan 101 Pass
    03 Jan 101 Fail
    04 Jan 103 Pass

    Thanks in advance
    Paul

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IFERROR(ROWS(UNIQUE(FILTER($A$1:$A$8, $B$1:$B$8=$D$1))), 0)

      where $D$1=101.
      You can learn more about count unique values with criteria in this article.
      I hope it’ll be helpful.

  21. I'd appreciate any help or insight.

    Use Case: Get count of multiple criteria from different columns

    Criteria1: =COUNTIFS('Sales Report'!$G:$G,"Damy Dams",'Sales Report'!$H:$H,"Nov",'Sales Report'!$I:$I,{"Movies","Music"}),

    Criteria 2: 'Sales Report'!$E:$E, {"Offline","Onsite","Podcast"})

    My formula returned the count for Criteria 1 BUT does not return the count for Criteria 2

    How can I combine all into one statement?
    For example: =SUM(Criteria1,Criteria2)

    AND NOT

    =SUM(Criteria1+ SUM(COUNTIFS(Criteria2)…. this gives a much higher count value and is not the requirement.

    1. Hello!
      Use the formula for Criteria2

      =SUM(COUNTIFS('Sales Report'!$E:$E, {“Offline”;”Onsite”;”Podcast”}))

      I hope it’ll be helpful.

  22. Hi,

    I need following assistance from you guys.

    In my data table column A = code number and column B = name.

    If code number in column A is same, that has to be count as one only.

    Count result should be Reza = 2, Jonny = 1, Rony = 2 & Bony = 1.

    Code Name
    438 Reza
    438 Reza
    438 Reza
    438 Reza
    438 Reza
    438 Reza
    438 Reza
    438 Reza
    438 Reza
    438 Reza
    439 Jonny
    440 Reza
    441 Rony
    442 Bony
    443 Rony

    Thanks in advance for you assistance .

    Warm regards,
    Hamid

  23. Hi, I am trying to count non text items listed in one column. These items belong to each patients who can have a cardiovascular risk that could be "<10%", "10 - <20%", "20 - <30%", "30 - 40%". How do I use countif formular for these to create how many patients for each CVR category?

  24. Hi, I am trying to set up a spreadsheet to check whether colleagues are completing specific tasks (that are recorded on a different spreadsheet), at least once within each 28 day period and to count how many of the task they did within the period. The best way I can think of it to to pull the count for each date and then have a separate tab that then works off that count to be certain the colleague is competent over the year. But I'm struggling to get the correct formula. What I need is: the count if column M on spreadsheet A is equal to cell A2 on spreadsheet B AND the date within column Q on spreadsheet A is equal to B1 on spreadsheet B - spreadsheet A being the one that contains the original data and spreadsheet B being the new one I am setting up

  25. How to combine following two formula..?

    if(and(f2>=d25,i2<=e25),b25,"")

    if(countifs(ar3:ar6,i24),"",b25)

  26. I have a file with some numerical grading,i need the lowest grading ...
    how can i calculate the same
    Row Labels- 2 3 4 5 1 6

  27. Hi,

    May be this is a silly question, but i cant figure it out the solution. Here's the data:
    A1 Korea
    A2 Indonesia
    A3 Indonesia
    A4 Singapore
    A5 Malaysia
    A6 Indonesia
    A7 Malaysia
    A8 Indonesia

    I want to calculate what is the most showed up country. Do you have any solutions? Thank you in advance

  28. Hey I want to concatenate names in excel ..but the conditon is

    In coloum A
    A1 is having an I'd and then second I'd number is present in another cell suppose A6 .. so I want excel to concatenate name from A1 to A5 ..and excel can do the same ..for entire sheet .with varying range of cells with I'd number

  29. I would like to find out how to group values using COUNTIFs in excel. For example, i have a grade sheet of students with the student name, subject and grade.

    Student Subject Grade
    Ann English A
    Ann Math A
    Joe English B
    Ann Science B
    Joe English A

    I want to group by the Student and the grade

    Student Grade Total
    Ann A 2
    Ann B 1
    JOE B 1
    Joe A 1

  30. A1 09:00:10 , A2 Medical , A3 11:03:25 , A4 blank
    what can I show B1 is 1 , B2 is 0 , B3 1 , B4 0 .

    1. Hi,
      Please describe the logic behind your calculations. What do you want to count? Why B1 = 1? Please specify what you were trying to find, what formula you used and what problem or error occurred.

  31. this has been helpful

    Is it possible to take this a step further and incorporate date range to this?

    Each sheet contains a column with a date that something was completed...column R
    The criteria in each sheet needed to be counted is column T

    In a new "summary" sheet is where calculations would occur using "SUMPRODUCT(COUNTIF(INDIRECT"

    In this "summary" sheet, column "A" contains the dates representing a week where the specific criteria occurred.
    Columns Row 1 in column B, C and D have the criteria needing to be counted based during the week listed in column A.

    Unfortunately limited in formatting in the forum
    Column A
    Row 1 Week Of
    Row 2 11/15/2020
    Row 3 11/22/2020
    Row 4 11/29/2020

    Column B
    Row 1 "Missed"
    Row 2 5
    Row 3 12
    Row 4 11

    Column C
    Row 1 "Caught"
    Row 2 7
    Row 3 11
    Row 4 13

    Column D
    Row 1 "Disconnect"
    Row 2 11
    Row 3 1
    Row 4 5

    I hope this makes sense. I have been unsuccessful in getting anything to work.
    Thanks in advance.

  32. Assuming I have a table with two columns J and K with entries of rows either, row1 ,(J=No,K= Yes), row2(J=yes,K=No), row 3 (J=Yes,K=Yes) ROW 4, (J=No,K=No), row5 ,(J=No,K= Yes), row6 (J=yes,K=No), row 7 (J=Yes,K=Yes) ROW 8, (J=No,K=No),...onwards for different documents.
    Whenever a No appears on either column J or K or both columns it should be termed error. What formula can I use to help me analyze a large data with the above scenarios. Analysis can be done on another sheet.

    1. Hello!
      If I got you right, the formula below will help you with your task:

      =IF((COUNTIF(J:J,"No") + COUNTIF(K:K,"No"))>0,"Error","Ok")

      I hope my advice will help you solve your task.

  33. I'm trying to do countif to get results from different cells not ranges.

    A1 table
    A2 chair
    A3 table
    A4 leg

    I basically need countif (A1,A3, "table") but that doesn't work.
    I've tried this indirect formula but it still doesn't work =SUM(COUNTIF(INDIRECT({"a1:a1","a3:a3"}),"table"))

    Any ideas?

    1. Hello!
      The COUNTIF function can only work with one range. Therefore your formula is not possible. Try

      =SUM(--(A1="table"),--(A3="table"))

      I hope it’ll be helpful.

  34. I want know how is count last 2 days continuous absent

    1. Hello!
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

    2. I want to know about . the person who absent last 2 days continuously. if he continuously 2 days did not come to the job his salary should deduct. i want know how is count in excel his continuously absent day ….please help me....

  35. The response that you sent to me earlier in the day was =IF(COUNTIFS(A:A,A2,B:B,"received")>0,1,""). I do know just how to specify the ranges in this formula. When I attempted it various ways =IF(COUNTIFS(F2:F309,F2,J2:J309,"received")>0,1,"") no error resulted ,but then no count appeared. The count should be 1.

  36. I am trying to develop an Excel formula to obtain a count.
    If an ID number appears multiple times in one column and the result “received” appears in a different column, what is the formula to count only 1 instance of the occurrence of the ID and “received”?
    For example:

    Column Column
    F J
    5596326 attempted
    5596326 delivered
    5596326 received
    5596326 received
    5596326 sent
    5596326 Wrong Number

    The ID number appears 6 times in column F, and “received” appears twice in column J. With a correct formula, the result should be 1.

    What is the formula? And please specify the row ranges in the formula.

    Both columns may contain 1,000 or more rows.

    Thank you for any assistance.

  37. I have three sheets
    Sheet 1
    Sheet 2
    Sheet 3

    I need to count values from Sheet 2 while matching criteria's from Sheet 1 & 2 both.
    I want to get these results on sheet 3.
    Since this record is for entire month for around 900 names.
    Please help & suggest why am I not able to get the results if using countifs formula.

    1. Hello!
      The COUNTIFS function works with data from different sheets. Unfortunately, without seeing your data it is difficult to give you any advice. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. It’ll help me understand it better and find a solution for you.

    2. SUMPRODUCT(COUNTIFS(Roster!J5:J444,Sheet1!A1,Roster!K5:K444,Sheet1!B1,Attendance!S5:S444,"P"))

      Why am I not able to get the count on sheet 1 with this formula.
      Please help me out with this

  38. Hi, I have this data
    S/N YEAR MONTH DAY DATE FULL NAME
    1 2020 SEPTEMBER TUESDAY 09/01/2020 STEPHEN TAYO
    2 2020 SEPTEMBER TUESDAY 09/01/2020 FADE OGUNRO
    3 2020 SEPTEMBER TUESDAY 09/01/2020 FADE OGUNRO
    4 2020 SEPTEMBER TUESDAY 09/01/2020 FADE OGUNRO
    5 2020 SEPTEMBER TUESDAY 09/01/2020 MRS WUNMI OGUNBIYI
    6 2020 SEPTEMBER TUESDAY 09/01/2020 MRS WUNMI OGUNBIYI
    7 2020 SEPTEMBER TUESDAY 09/01/2020 MRS WUNMI OGUNBIYI
    8 2020 SEPTEMBER TUESDAY 09/01/2020 LANRE KUYE
    9 2020 SEPTEMBER TUESDAY 09/01/2020 LANRE KUYE
    10 2020 SEPTEMBER TUESDAY 09/01/2020 KOME AJEGBO
    11 2020 SEPTEMBER TUESDAY 09/01/2020 TIWA SAVAGE
    12 2020 SEPTEMBER TUESDAY 09/01/2020 MS ASMAH WILLIAMS
    13 2020 SEPTEMBER TUESDAY 09/01/2020 AISHA
    14 2020 SEPTEMBER WEDNESDAY 09/02/2020 LANU AGUSTO
    15 2020 SEPTEMBER WEDNESDAY 09/02/2020 MS FATIMA
    16 2020 SEPTEMBER WEDNESDAY 09/02/2020 TIWA SAVAGE
    17 2020 SEPTEMBER WEDNESDAY 09/02/2020 TIWA SAVAGE
    18 2020 SEPTEMBER WEDNESDAY 09/02/2020 MR JIDE
    19 2020 SEPTEMBER WEDNESDAY 09/02/2020 MR JIDE
    20 2020 SEPTEMBER WEDNESDAY 09/02/2020 MR KEVIN JOHNSON-AZUARA
    21 2020 SEPTEMBER WEDNESDAY 09/02/2020 MR KEVIN JOHNSON-AZUARA
    22 2020 SEPTEMBER THURSDAY 09/03/2020 MRS HADIZA
    23 2020 SEPTEMBER THURSDAY 09/03/2020 TOYIN SUBAIR
    24 2020 SEPTEMBER THURSDAY 09/03/2020 MR YINKA
    25 2020 SEPTEMBER THURSDAY 09/03/2020 MR YINKA
    26 2020 SEPTEMBER THURSDAY 09/03/2020 FADE OGUNRO
    27 2020 SEPTEMBER THURSDAY 09/03/2020 FADE OGUNRO
    28 2020 SEPTEMBER FRIDAY 09/04/2020 MR SAM
    29 2020 SEPTEMBER FRIDAY 09/04/2020 EMMANUEL
    30 2020 SEPTEMBER FRIDAY 09/04/2020 MR IFEOLUWA BAKARE
    31 2020 SEPTEMBER FRIDAY 09/04/2020 MR IFEOLUWA BAKARE
    32 2020 SEPTEMBER FRIDAY 09/04/2020 MRS EBI WILLIAMS
    33 2020 SEPTEMBER FRIDAY 09/04/2020 ETHEL OKOSUN
    34 2020 SEPTEMBER SATURDAY 09/05/2020 SEGUN AWOLOWO

    and i want to identify the number of transaction each customer does in a month, a customer can only do one transaction in a day, and multiple transactions in subsequent days whenever they come into the store. Please how do I go about this?

  39. I have to calculate attendance for 5 periods in different sheets. Using countif how can i calculate attendance

  40. Excellent Example

  41. I am trying to count the new number of impacted individuals for today (prefer, workdays), I also have a number of other criteria. This is the current formula I use to use to count the total number =COUNTIFS(AN2,"=*?*",P2,"=No",D2,"=Reserve"). I would like a way to count only the new ones added today. I have a entry date column in J2.

    Thanks in advance

    1. Hello!
      Unfortunately, without seeing your data it hard to give you advice.
      If I understand your task correctly, the following formula should work for you:

      =COUNTIFS(AN:AN,”=*?*”,P:P,”=No”,D:D,”=Reserve”,J:J,TODAY())

  42. In a range, is there any formula where i can pick

    how many 1s, how many 2s, how many 3s etc... in one step

  43. Hello,
    I am in need of assistance locating the right formula for my workbook.
    in sheet1-I have a list of names, with a current fourmula " =COUNTIF('Element Core'!A2:A500,'Agent Assignment'!D41)" to capture how much data from each tab they are assigned too.
    In sheet2-Shows the actual amount of cells each agent has been assigned to in column A, i would like to somehow divide the completed task in column B of sheet 2 to the total number they have been assigned to in sheet 1.
    example
    agent Name - # of cells Assigned too(from sheet 2)
    Tom Jerry - 40
    I would like to divide 40 by what the agent has already completed in sheet2 on column B.

    Thank you.

    1. Hello!
      If I understand correctly, then you just need to divide the values from column A by the values from column B. I suppose that the number of completed tasks can be calculated with approximately the same formula as the number of assigned tasks. But there is no information in your question about this.
      Please describe your problem in more detail. It’ll help me understand it better and find a solution for you.

  44. Hi Alexander
    Thanks - that works perfectly!
    Adam

  45. Hello
    I have a table of data, on column of which is either ***, ** or * depending on the prioritisation of that row. If I now want to do a countifs function, how would I insert a condition that a row has *** for that specific criteria? I tried using a tilde, " " but cant get it right
    Thanks!

    Adam

    1. Hello Adam!
      To count the number of "***" values, add a tilde before each *

      =COUNTIFS(K1:K40,"~*~*~*")

      I hope it’ll be helpful.

  46. Hi,
    Can you provide your help regarding a specific formula:
    I want to do a calculation in which I want to exclude character which lies in a sequence.
    For example. I have 10 numbers: 1, 3, 5, 10, 2000, 2001, 2002, 3052, 3053, 3054.
    Now, I want to count 1 and ignore 3, 5, 10 mean to ignore the up to 10 number in consective orders. Similiartly I want to count just 2001 and 3052 and want to ignore other number from counting. I have to do this calculation in up to 100K.
    If you provide your help I will be very thankful.
    Best
    Abdullah

  47. Hi
    I have a data set where in row 2 I have headings of "Squad", "Mins", "Goals", "Assists" and these repeat several times across the row. I'm trying to find a formula which counts the number cells under any of the "Mins" columns which are greater than 0, but only when the number in the "Squad" column is greater than 11.
    I can do a simple count of the first part (=COUNTIFS($N$2:$KG$2,"Mins",N41:KG41,">0") however I am unsure how to add the second part to only count this IF $N$2:$KG$2,"Squad",N41:KG41,">11".

    Have you got any thoughts about how I can add this IF element? I have tried a few combinations but the best i get is a #VALUE! error. Any help much appreciated.

  48. Hi,

    I have two columns with huge number of data in it. Coulmn A has the list of applications and Column has the list of servers. Each application hits different servers . Want to know on the count of application per server using a formula.

  49. My queson is to display the last entered number position and that will automatically change the value in another cell.
    If suppose, in a column Sr.No. 1,2,3,4,5,6,7 now the last no. is 7 if I enter 8,9,10,...the another which is having the reference will catch the latest entry inthis case 10 and update his cell value.

  50. Hi,

    I want to count how many times the value = "yes" in column F on data sheet.

    this is fine, however, I want this to be date dependent criteria.

    Count how many times "yes" appears within a date range specified on reporting sheet, G8 (start date) & J8 (end date).

    The corresponding date for "Yes" column is in "g3" on the data sheet.

    I currently have:
    COUNTIFS(Data!F3:F68, "Yes") + COUNTIFS(Data!G3:G61,">="&Reporting!G8,Data!G3:G61,"<="&Reporting!J8)

    But this is counting how many times "yes" appears AND how many times the date appears which is duplicating my data.

    Thank you, Hope that makes sense!

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 :)