Comments on: Google Spreadsheet COUNTIF function with formula examples

We continue exploring the possibilities of Google Spreadsheets with COUNTIF function. Learn why you may need it and how it can be applied to your daily work with the tables. Continue reading →

Comments page 4. Total comments: 391

  1. Hello!

    I am trying to track attendance call offs within a 90-day period prior to today.
    Row 8 lists dates from 12/1/2021 - 12/31-2022.
    Row 9 lists Call Offs ("CO").

    I am playing with the formula: =COUNTIFS($BW8:$RB8, "=TODAY()-90", $BW9:$RB9, "CO")

    However, it is returning "0" even though I have tested "CO" within the 90-day range.

    Am I on the right track? I'm not sure what I'm missing!

      1. Ha! Thank you so much! It's working perfectly! :)

  2. Can you use COUNTIF to rule out options based on data in another column of the text. For example, lets say column a shares the type of chocolate purchased (dark, milk, white, etc) and column b has the state it was purchased in.

    Can COUNTIF tell me how much milk chocolate was purchased in Ohio?

  3. Hi! What COUNT formula should be used to count cells that have different texts or values? For example, there are 15 cells and most common word/value is "Order" but I want to count the other cells that have no value "Order".

    1. Hi Mark,

      Use the following formula to count cells without the word 'order' in them:
      =COUNTIF(A2:A50,"<>*Order*")

  4. Hello,

    I am trying to use a COUNTIF formula concatenated with text and the percentage is formatting as a 15 digit number. How to I adjust the formula to make the percentage show as '0%'?

    My formula: ="Seen "&COUNTIF(B2:B,True)/COUNTA(B2:B)
    The answer: Seen 0.219178082191781

    Thank you!

      1. Thank you for your response Natalia, however, I had already tried adjusting the decimal that way with no success. I'm assuming it's because my percentage formula has text with it?

        Thanks!

  5. Can you COUNTIF by format, like bold and italics? Like count all cells that are bolded/italicized?

  6. Hi!

    Thank you very much for this resource and the amazing job you are doing!

    I need your help. I would like to count a number of words in a cell (or a range of cells) but I need to exclude the counting of certain words that are irrelevant (say "a", "the", "and", "an"). The range is the same (say B2:B16), but the criteria of exclusion are multiple. How can I do it without repeating the range every time (in fact, there are a lot more the criteria of exclusion than the four in the example above).

    Thank you in advance.

    1. Hi Helen,

      Thank you for your lovely words!

      As for your task, please share an editable copy of your spreadsheet with us: support@apps4gs.com

      I'll check the data you have and see where your criteria are and how many of them you may have. This will help me find the best solution.

      1. Dear Natalia,

        Thank you for your reply. I have sent you an example of a neutral text in a Google Spreadsheet and a little comment. Looking forward to reading your comment. Thank you in advance,
        Helen

        1. Dear Helen,

          Thank you for sharing your spreadsheet. Here's a formula to count all those special words you mentioned:
          =COUNT(ArrayFormula(FIND("#", REGEXREPLACE(SPLIT(B3, {" ", ",", ";"}), "^(and|the|a|at|in)$", "#"))))

          Subtract this from your total word count and you're good to go :)

          I entered the resulting formulas to column E in your spreadsheet, have a look.

  7. In the example below, how would I count the number of shirts of each style, size and color?

    Style Size Color
    Women's XL Black
    Women's XL Black
    Men's XL White
    Men's 2XL White
    Women's XL Green
    Men's L Green
    Men's 3XL Black
    Mens XL Green
    Men's XL Black
    Women's M Yellow
    Women's M Black
    Women's XL White
    Women's XL Green
    Men's XL Black
    Women's L Green
    Women's XL Green
    Men's 3XL Green

    1. So trying to add the agent from Column A (9 agents total) to this formula: =ARRAYFORMULA(sum(COUNTIFS(AS2:AS11,{"Yes","VM","No"})))

  8. Hi,

    I have calculated how many completed tasks are done in the current day or current month. See below:

    =COUNTIFS('Completed Cases'!K2:K5000,">"&TODAY()-1,'Completed Cases'!K2:K5000,"="&EOMONTH(TODAY(),-1)+1,'Completed Cases'!K2:K5000,"<"&EOMONTH(TODAY(),0)+1)

    I now need to work out how to break that down and count if the outcome was option A or option B from a different column. What is the best way to do this please?

    1. Hi Ben,

      Am I getting it right that some results from K belong to option A from another column and some results belong to B? If so and you'd like to get two different results based on that another column as well, you just neet to have two COUNTIFS formulas where one of the conditions will be for that another column:
      =COUNTIFS('Completed Cases'K2:K5000, ">"&TODAY()-1, 'Completed Cases'!K2:K5000, "="&EOMONTH(TODAY(),-1)+1, 'Completed Cases'!K2:K5000, "<"&EOMONTH(TODAY(),0)+1, 'Completed Cases'!ANOTHER_COLUMN, "option A")

      If that's not really what you need, please try to describe the desired outcome in more detail.

    1. Hi Jack,

      you should use COUNTIFS for your task. I've put an example formula in E2 since it's the only row from your task I understand clearly. Please adjust this formula to use in other rows as you need.

      1. Hi, I had managed it just before your replies and had sent my solution but it appears it did not go through?

        Thanks anyway

  10. Hi, I have a sheet with actions on.

    On another sheet, I want the number of actions where, on the actions sheet:
    - value in column C is the value in column C of the count sheet (e.g. where C9 = C9)
    - value in column H of the action sheet is "Strike"
    - value in column J of the action sheet is "Active"

    How would I achieve this?
    Thanks

    1. Did it now, thanks anyway.

      =COUNTIFS('Log Sheet'!C9:C,C9,'Log Sheet'!H9:H,"Strike",'Log Sheet'!J9:J,"Active")

  11. Hi,
    I'm trying to do something similar. Let's pretend on your sample sheet that you had in Column E another list of products. Some said Dark Chocolate, Milk Chocolate, Extra Dark Chocolate, but you also had just plain Chocolate. etc.
    I want to be able to count all of the occurrences of Dark Chocolate where (plain) Chocolate would not be counted.

    (In my sheet, I need to count all of the occurrences of English 1 separately from all of the occurrences of Honors English 1.
    I can get an exact count of all of the "Honors English 1", but anytime I try to count "English 1", because those words also appear in "Honors English 1", it counts both "English 1" and "Honors English 1".

    I've linked to a sample sheet: https://docs.google.com/spreadsheets/d/10x13hlqSthvxByNrAqbMSOIM_-VLU6KZ4MdVHkSWa_k/edit?usp=sharing

    1. Hi Elizabeth,

      Thank you for sharing the sample right away. I entered the formulas into column J, please take a look. Here's a sample formula:
      =COUNTIF($B$2:$E$11,G2)

  12. Hi, is there any possiblitiy for me to use COUNTIFS to count specific text from cell that has Data Validation : drop down list.

    1. Hi Firdauz,

      Do you want to take cells with data validation into account or do you want to count text in all items within one data validation in a cell?

  13. How to make a countif to find out amount more than 0:03:00 but lower than 0:04:00

  14. Hi! I created a self-assessment in Google Forms that I'm trying to score with a simple formula. All questions on the assessment have checkbox options for each item, so multiple options can be selected. For example...

    For question 1, I choose Options A, B, and D. Option A is worth 1 point, Option B is worth 2 points, and option D is worth 4 points. What formula will take a cell that says 'A, B, D' and calculate 7?

    Thanks in advance!

    1. I figured it out this morning. I was overthinking it.

      I used =(COUNTIF(cell,"*A*")*1)+(COUNTIF(cell,"*B*")*2)+(COUNTIF(cell,"*C*")*3)+(COUNTIF(cell,"*D*")*4)...

  15. How do i do countif with the power formula?

  16. Hi there,
    I am struggling with conditional formatting using custom formula. I want to do a simple formatting into red for all cells in one column that are duplicates (excluding the first occurrence). Following some instructions on the Internet, I select the column (it's formatted as plain text and it only contains numbers) - the range would be A3:A500 (A1 nad A2 are headers) and then Format cells if -> custom formula -> =countif($A$3:$A$500,A3)>1. I also try without $ sign. I also try with (A:A, A3)>1. The problem is that after I have written =countif, no matter what I put there - be it the bracket or whatever - the system highlight this in red and always shows "Invalid formula". Literally, into the custom formula field I can put only: =countif .
    I am puzzled and desperate. I have tried on different data in different sheets - same issue everywhere... I would appreciate any help on this matter.

    1. Hi Ela,

      First please try to format your data as numbers, not text, before trying to apply the conditional formatting.

      If this doesn't help, for me to be able to help you, please share an editable copy of your spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a shortened copy of your source data (2) the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows. If you have confidential information there, you can replace it with some irrelevant data, just keep the format.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

  17. I have multiple conditional formatting in a google spreadsheet. An example is: =AND($B2="In",COUNTIFS($D:$D,$D2,$B:$B,"In")=6)
    This will look at multiple cells and highlight a row based upon the criteria. Each conditional formatting is the same just the number at the end changes which, in turn, highlights the cell a different color. I need to add in another factor to this formatting that if something is column E is indicated as either MEDICAL APPOINTMENT or ILLNESS that is does not count as one of the quantities.
    For example is someone has 5 that are marked "In" generally it would highlight all that person's lines purple; however, if 1 of those "INs" are due to a medical appointment or illness it needs to not be counted as 5 INs". but 4 "INs" and highlighted a different color

    1. Hello Shawna,

      For me to be able to help you, please share an editable copy of your spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a shortened copy of your source data (2) the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows. If you have confidential information there, you can replace it with some irrelevant data, just keep the format.

      Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

  18. Hello.

    I have a series of cells each containing a 0 or a 1. I would like to count the number of cells containing a 1, but would the count to stop when the first 0 is encountered.

    For example, this row:
    1 1 1 1 0 1 1 0 0 1 0 0 0
    should produce the count 4.

    And this one:
    1 1 0 1 1 1 1 1 0 1 1 0
    should produce the count 2.

    Can this be done with COUNTIF (or perhaps with some other function)?

    Best wishes

    1. Hello DB,

      Assuming your numbers are in A1:M1, here's a formula to try:
      =IFERROR(LEN(LEFT(JOIN("",A1:M1),FIND("0",JOIN("",A1:M1))-1)),COUNT(A1:M1))

  19. Hello!

    I am trying to count how many values in a column are bigger than the corresponding values in another. Specifically, I have one column that lists the scores of a pre-test that students take at the beginning of class and a list of the student's scores on the test they take at the end of the class. I want to count how many of those scores improved, how would I do that?

    Thank you!

    1. Hello Robin,

      Assuming your scores are in columns A and B, this formula will do:
      =SUMPRODUCT(--(B2:B20>A2:A20))

  20. Hi! Can you help in writing a countif function where the cell does not contain "---"

    1. Hi Abraham,

      If I understand you correctly, this formula will do the trick:
      =COUNTIF(A2:A10,"<>*---*"

  21. I wonder whether the following is possible.

    In TAB1, column A has a number of people's names. Column B onwards is then made up of dates and each column records attendance the attendance of the names in column A

    In TAB2, column A has the same names. I want Column B to then repeat whatever is in the cell of today's column in TAB1, so if H1 has today's date, then it will give me the attendance of the named person (in A9) in B9 in TAB2.

    How do I get column B in Tab2 to pick up the relevant cell from today's column? Is there an appropriate formula?

    Thanks in advance for anyone who is able to help!

    1. Hello Jon,

      If I understand you correctly, you can try to use IF along with the VLOOKUP function.

      In case you're not sure how to use them exactly, please share a small sample spreadsheet with us (support@apps4gs.com) with your sheets and the example you want to get (the result sheet is of great importance and often gives us a better understanding than any text description). I kindly ask you to shorten the tables to 10-20 rows.

      Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying to this comment.

      I'll look into your task and do my best to help.

  22. I was wondering given your example sheet above.

    If I want to count the amount of Dark Chocolate sold fx.

    so sum up all numbers in column total sales where column Product = "Dark Chocolate"

    1. Figured it out ..
      =SUMIF(A2:A500,D1,C2:C500)

      Where content of D1 would be the value to look for

  23. Thank you for monitoring this channel and helping us out.

    How can I use COUNTIFS and OR

    =COUNTIFS(E35:T35, "Z" OR COUNTIFS(E35:T35, "1" )

  24. Hi I want to count only if two criteria are met.
    EG Cells in Range A14:A35 = A and Cells in range C:14:C35 = W

    I only want a count of 1 if both conditions are met

    Thanks

    James

  25. Hi there,

    I was wondering if there was a way to count if one cell is greater than another range. For example I have a set of scores for students in P column (say range P3:P80) and a second set of score in Q column (Q3:Q80). I want to count the scores in Q column IF they are greater than the initial scores, e.g. I want to count the number of students who got a higher score in the second set of results.

    Cheers,
    Emma

  26. Hi,

    I have this Array Formula in my Google sheet:

    ={{unique(ArrayFormula(text(K4:K2001,"MMMM")))},
    {ArrayFormula(sumif(ArrayFormula(text($K$4:$K$2001,"MMMM")),
    unique(ArrayFormula(text(K4:K2001,"MMMM"))),$I$4:$I$2001))}}

    The dates it is counting starts from August 2020 through to April 2021.

    My problem is for any dates not completed in any given month, it is counted in December, this giving an incorrect count.

    Any idea how to fix this?

    1. Hi Colin,

      We tried to recreate the formula on our side and it works correctly. Please go to File > Spreadsheet settings and specify what locale you have selected there. Also, provide a couple of examples of your dates in cells.

  27. I have a COUNTIF formula but after the count I want it to divide the count by 2

    =COUNTIF(E9:E755,"Couple")

    How do I add to the formula above to then divide the count by 2?

  28. Hi,

    Workbook 1, Sheet "ABCSCHOOL" itemizes a constantly changing number of students per section, per grade.
    Workbook 2, Sheet "ABCSTAFF" totals the numbers of students per section for all grades, in order to determine the amount of staff needed per section for the coming year.

    ABCSTAFF currently has cells that contain the current formula:
    =IMPORTRANGE("https://docs.google.com/spreadsheets/d/WORKBOOK1","ABCSCHOOL!E90")

    I am using the COUNTIF function in ABCSTAFF to count the number of cells that have a number greater than 0. I am not trying to obtain the sum of the numbers in the cells, but rather the number of cells that contain a number. That formula looks like this: =countif(D4:D10,">0")

    What I am trying to figure out is:
    1. Is there a formula I can use to count a cell twice if the number in it is greater than 8? For example, Assuming range D4:D10 has 3 cells containing numbers 2, 4 and 5 and another cell containing the number 12, I would like the total count to equal 5 cells, as opposed to 4.

    2. How would I add that additional formula to the cell that already contains =IMPORTRANGE("https://docs.google.com/spreadsheets/d/WORKBOOK1","ABCSCHOOL!E90")

    Thank you.

    1. Hello Denise,

      I'm sorry but your task is quite confusing. I just don't see why the count should return 5 if only 1 number is greater than 8, so I'm not sure what to add to the formula. I also don't understand what the cell with the importrange and the count should contain as a result.

      For me to be able to help you, please share a small sample spreadsheet with us (support@apps4gs.com) with 3 sheets: (1-2) a shortened copy of your source data (3) the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows. If you have confidential information there, you can replace it with some irrelevant data, just keep the format.

      Once you share the file, please reply to this message. I'll look into your task and try to help.

      1. Thank you Natalia.

        I shared two workbooks with you (they were sent under a different email address). I greatly appreciate any help you can provide!

        1. Hi Denise,

          Thank you for sharing the files. I entered the correct formulas right under your current ones – in row #13 – and colored cells green. Hope this will help :)

          1. Thank you! This is exactly what I was looking to do. You are amazing!

  29. Is there a way to conditionally average values? Say there are text values in column A. If cell is not blank in the row I want to know the value of cell B divided by cell C. And then find the average.

    Would this be:
    `=AVERAGE(if(A1:A100,B1:B100/C1:C100,null))`

    1. Hi David,

      I believe the following formula would work better:
      =ARRAYFORMULA(IF(A1:A100<>"",AVERAGE(B1:B100/C1:C100)))

      We have an entire blog post devoted to the IF function, feel free to take a look.

  30. Thanks! It works!

  31. Hi, I need help with Google sheets conditional formatting and formulas. I want to know how to highlight rows in Tab 1 with matching specific and partial keywords from Tab 2? And I need to know how to get the total amount of the highlighted rows in Tab 1, Column G. Please see this sample workbook: https://drive.google.com/file/d/1LtZqlrrBoiDbmSZm-5eA26QXngKsIS0w/view?usp=sharing

    I hope you can help me. Thank you.

    1. Hi,

      We have a special blog post devoted to conditional formatting in Google Sheets: it describes how to format cells by the text they contain, how to format entire rows and how to create conditional formatting based on another cell.

      Also, there are no standard Google Sheets functions that process only highlighted cells. For such purposes, we have a special add-on – Function by Color. It calculates data based on the font or/and fill color. There's a fully-functional 30-day trial period available so you could try it out and see if if suits your needs. You will find the detailed instructions on how to work with the add-on on its help page.

  32. I have two columns of data and I need a to count the number of rows where column A is greater than column B. Can I do that with countif?

  33. hello,
    I am trying to use this code,,,, but to cell A21 then A22 and so on,,,, please how to that
    with this code i am using entire A column as a range

    If Application.CountIf(Sheets("Sheet1").[A:A], Sheets("Sheet2").Cells(RowNum, "A")) = 0 Then

    Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Sheets("Sheet2").Cells(RowNum, "A").Value

    1. Hello Reda,

      It looks like these are parts of the VBA code for Excel while this blog post is about Google Sheets.
      Anyway, we don't help with macros so, for Excel users, my best advice would be to try and look for the solution in VBA sections on mrexcel.com or excelforum.com

  34. Hi Natalia,

    So it is a long record of incidents in which the time is recorded, I want to create a line graph for month by the most common time for example 14:00-14:59 and 15:00-15:59.

    My formula is based on all the times for that month (January) on a separate tab, I am then trying to put a formula as follows (using the examples above). >=14:00 but =C107",
    'Claims Log'!K2354:K2455,"<D107")

    Thanks

    1. Hi Brian,

      Thank you for the description.

      I'm afraid I will need to look into your data since this time you introduced another criteria '=C107' and didn't specify what lies in D107.

      Please share an editable copy of your spreadsheet with us (support@apps4gs.com) with 2 sheets: 1) a copy of your source data with a formula, 2) the result you expect to get.

      Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here.

      Thank you.

  35. If I have loads of times how can I count up per hour

    1. =countif('Claims Log'!K2354:K2455,"D107")

      This is the formula I tried, greater than 00:00 but less than 01:00

      1. Sorry, this is what I tried

        =countif('Claims Log'!K2354:K2455,"D107")

        1. Hello Brian,

          For me to be able to help you, please describe your task in detail:

          1. what do you mean by 'count up per hour'?
          2. how do your time units look like?
          3. what do you have in D107 exactly?

          If you provide examples of a few records you have and what you're trying to achieve, it'll help a lot.

  36. Hi, want to know the formula . If a student scores 7/7 , then he will be graded A+, if student scores 6/7, then he will get A. Like this. Please help

  37. Hello.
    If a Cell is "YES", then these cells will be added together. If "NO" or empty cell, then do not add. Here is what I have.
    =COUNTIFS(Y10:Y11,"YES")+COUNTIFS(AND(B2="BRAND",B3="Shirt"),ArrayFormula(SUM(COUNTIFS(I10:J293,{"X","Y","Z"}))))

    Thank you in advance.

  38. Hello,
    Very much appreciate this forum, and the help. G Suite is new to me, especially Sheets. This should be a simple one:

    Column B has a list customer names:

    company a
    company a
    company b
    company c
    company a
    company d
    company e, etc.

    Here's what I'm trying to accomplish:

    1. This list is ongoing, it doesn't start in B2 and end in B10 for example, this is a running list. B1 is the title of this column, though.

    2. I want to know how many projects we do, via a percentage pie chart, for company a, company b, company c, etc. The pie chart would ultimately show 50% company a, 20% company b, and so on.

    3. The formula and chart should allow for new customers to be added; there could be a company f, company g, company h, infinitely.

    Any help would be greatly appreciated!

    1. Hello!
      To automatically add new customers to the chart, you can specify a very large data range for the chart. For example, your data is in B1: C10 and the range for the chart is B1: C100. Blank cells are not used in the chart. As soon as you write data into B11 and C11, it will automatically be shown in the chart.

      1. Thanks Alexander. But I still don't know how to do the formula correctly, nor do I know how to do the pie chart correctly either.

        1. Hi Jeremy,

          Alexander described how to create a pie chart in this blog post.
          There's no need to create a formula. When creating charts, you refer to the existing tables and adjust the chart settings. Please follow the provided link for details about charts.

  39. Hello
    I am trying to do two things:
    1. Count data from a different google sheet and populate it in my data sheet. I did the Countif(importrange("html or url key", "sheet and column"), "input")

    It returns a 0 although the column has 215 input. I also tried copying the data into a newly created sheet to avoid authority issues. Still not working

    2. On a single row i can have 7 entries. Users can enter any of 5 choices from the drop down. At the end of the day i need to count the occurrences of each entry and populate the last cell on the row with the most occurring value. Example

    1 2 3 4 5 6 7 Majority
    red blue yellow yellow red yellow white yellow

    Here the last cell should populate as Yellow as it occurred the most

    I tried creating another set of tables at the bottom with just the count of the colors. I am missing the logic that says take the highest count and return the value of that entry. So i want a logic that realize yellow is highest count and it should populate the word yellow as a result. And then I am just referencing that Majority cell back to the original location at the top of the sheet. Example

    Red Blue Yellow White Majority
    2 1 3 1 Yellow

    1. Hello!
      To find the most common value in a string, use the array formula:

      =ARRAY_CONSTRAIN(ARRAYFORMULA(INDEX($A$2:$S$2,, MATCH(MAX(COUNTIF($A$2:$S$2,$A$2:$S$2)), COUNTIF($A$2:$S$2,$A$2:$S$2),0))), 1, 1)

      I hope it’ll be helpful.

      1. I am sharing the spreadsheet. So different people populate the data. Some sections are divided into 2 and some are not. I need Column L to populate automatically depending on the entries. How do you handle instances where 2 values are entered equally? Or if you have one Fully (100%) and Two (75%) and a three (less than 50%). In this case I want it to default to somewhat. Can I change the text values to percentages so it can calculate the average and give me a text value back?

        https://docs.google.com/spreadsheets/d/1ItaYhyXifEXAn2jQNh-gHwgOr6gEBbZCfP9Mnk6eU0o/edit?usp=sharing

        1. Hello!
          If two values are entered the same number of times, the one at the beginning of the list will be shown.
          If cells in a row are merged, this does not affect the calculations.
          To calculate the percentage of occurrence of the most frequent value, you can use the formula

          =countif(B3:K3,L3)/counta(B3:K3)

          or

          =countif(B3:K3,ARRAY_CONSTRAIN(ARRAYFORMULA(INDEX($B$3:$K$3,, MATCH(MAX(COUNTIF($B$3:$K$3,$B$3:$K$3)), COUNTIF($B$3:$K$3,$B$3:$K$3),0))), 1, 1)) / counta(B3:K3)

          Hope this is what you need.

          1. Thank you, this helped alot. I still need help with the following

            1. Count data from a different google sheet and populate it in my data sheet. I did the Countif(importrange("html or url key", "sheet and column"), "input")

            It returns a 0 although the column has 215 input. I also tried copying the data into a newly created sheet to avoid authority issues. Still not working

            Or just copy the data as it is entered into another sheet. Not tab, a new sheet

  40. I feel I am missing something in my code. perhaps you can help! My intention is that when 2 criteria (column n which holds a tag type and Column m that holds a tag number) both have duplicates it would change the color on column n on the duplicates. I have done this before with one column =countif(B:B,B1)>1 and that works, but trying to do 2 columns it's not working. what am i missing? Two codes below are ones I have used to try to get to work.

    =((countif(N:N,N1)>1)+(countif(M:M,M1)>1))

    =COUNTifS($N$2:$N, ">1",$M$2:$M ,">1")

    1. Hello April,

      If you're trying to color complete row duplicates, a formula like this should help you:
      =COUNTIFS($M$2:$M;M2;$N$2:$N;N2)>1

  41. Hi,
    We're using sheets as a weekly time table for project work management. We write down which project we worked on at what time. I'm trying to summarise the amount of time worked on each project at the end of the month.
    we have 2 products that use the same project names, with the product acronym ahead of the project name (IE-AB Cars\CD Cars).
    Each sheet represents a week.
    Using =COUNTIFS('2-8.8.2020'!A3:G22,"*AB*",'2-8.8.2020'!A3:G22,"*Cars*")
    This works fine when I refer to one sheet\week at a time. but trying to add all sheets\weeks doesn't work

    thanks

    1. Hi Eli,

      Could you please let us know what your formula returns exactly when it doesn't work? Is it '0' or some specific error?

      1. Hi Natalia,
        It returns'0'

        1. Thank you for replying, Eli.

          Since COUNTIFS "returns the count of a range depending on multiple criteria", '0' usually means that there are no records matching all your criteria.

          However, if you see matches without the formula and are sure the result should be different, please share an editable copy of your file along with the formula that doesn't work with us (support@apps4gs.com).
          Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying to this comment. We'll look into it and see what may be going wrong.

  42. I need some help.
    I have a calender with an allocation of 2HC per day to go on leave
    I would like the first 2 names that are input in the day to be left in its original format but with every additional names added into the particular date range will be highlighted.

    I'm currently using =countif($F$8:$F$11,"*")>2 but this will highlight every column in my range for as long as the input is more than 2.

    Is there any way to go about highlighting only the names that comes after the 2nd entry?

    Thanks ^^

    1. Hello Farah,

      Please consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com) with 2 sheets: 1) a small example of your source data with the rule you're trying to apply 2) and the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows.
      Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here.
      Thank you.

  43. can we count how many operators does a cell have in spreadsheet

  44. Little help? countifs two arguments don't work for me

  45. Good afternoon, I have a question.
    I have several texts in different cells. The texts are similar. How do I know if they are the same? And if they are not the same, how do I know what percentage of words are repeated? And based on that percentage, can I declare a color range?

    Then, I need to import from one sheet to another according to that percentage. If the percentage is high, it is not copied, if it is low, it is copied.

    thank you... ♥

    1. Good afternoon, Martin,

      I'm afraid your task is rather complex so I can't suggest anything in particular. However, you can share a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: a copy of your source data and the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows.
      Note. We keep that Google account for file sharing only, please do not email there. Once you share the file, just confirm by replying here. We'll look into your task and see if we can help.

  46. Sheet 1
    Col A1 58+1 BOXN
    A2 42+1 BCN
    A3 57+1 BCNHL
    A4 58+1 BOXNHL
    Sheet2
    Col A1=( Count of BOX/BOXNHL)[=2]
    Col B1=(count of BCN/BCNHL)[=2]
    HOW TO WRITE FORMULA

  47. Is it possible to use countif with ValuesbyColor. IE I'd want to know how many times "John" appears in either red or blue cells

    1. Hello Tomi,

      Yes, you can use the valuesByColor formula in the COUNTIF. However, one formula can process only one color at a time, so COUNTIFS won't work here. Please do the following:

      1. Count each color separately.
      2. Add each valuesByColor formula for each color into individual COUNTIF formula to count 'John' only:
        =COUNTIF(valuesByColor("#f4cccc", "#000000", Sheet1!A1:A10),"John")
      3. Sum the results.

      1. Thanks that worked! I also have a follow up, does it work with sumif?
        I've tried
        =SUMIF(valuesByColor("#e06666", "#000000",Sheet1!$B$2:$B$999"*John*",Sheet1!$D$2:$D$1000))
        The second range ($D$2:$D$1000) counts time so it should create how long time John has accumulated in all the cells with that color. But the error it gives me that there is only 1 argument

        1. Tomi,

          The SUMIF function requires different arguments, so placing them in the same order as for COUNTIF won't work. Also, in your formula, you put the condition for John and the range with time inside the valuesByColor, this also shouldn't happen.

          If I understand it correctly and you need to sum times based on two conditions: 1) if there's 'John' and 2) if a cell is coloured specifically, it is SUMIFS that you need (sum on multiple criteria). But at the moment valuesByColor doesn't work within SUMIFS and COUNTIFS.

  48. Thank you all for this content. I have one column of data where each cell is assigned a number between 1 and 13. I'm trying to find how to know the probability that any one number follows another. For example, what is the probability that a 7 is followed by another 7. Or what is the probability of the sequence 4, 9, 13. If anyone can help with this formula I would be more than grateful.

  49. I need help for Google sheets formula.

    if the percentage value of F4 > C4 = "good"
    if the percentage value of F4 < C4 = "bad"
    if the percentage value of F4 = C4 = "same"

  50. Hi,
    I have an 'attendance' list each week. I have names indicated by row, and dates indicated by column. I mark present with an 'x'. I would like to select each week (5 days so 5 columns) for a group of students (31 students, so 31 rows) and see if they showed up at least once that week. I do not want to count if they were there multiple days, but just know how many students out of the 31 showed up at least once that week. Is there a formula for this?

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