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 3. Total comments: 391

  1. Hey Guys,
    I wanted to achieve maybe something that nobody has tried before.

    Our office timings changed the mid-month and I have used this (A1:A733,">09:10") formula to calculate the late comings for all the staff members but now I have to change the condition of the late coming but by keeping the old late comings in the count. What I meant is with this (A1:A733,">09:10") formula the total number of a persons late comings were 10 but now I want to keep the old late comings count & also have the new count added for updated time condition in the same cell. How do I do that?

  2. I am wanting a formula that will do the following:
    In a row I have 8 cells A1:H1
    In those cells I have 0 0 0 21 23 25 24 0
    I want I1 to be the total number =SUM(A1:H1) answer is 97
    I want J1 to give me the answer of 100 - Why? ... I need only the cells that have a number greater than 1 to be read as 25 and then added together - So I need the 0s to be read as 0s and not calculated, but the 21, 23, 25, and 24 to be read as 25 and totaled.

      1. You are amazing!!! THANK YOU!

  3. I am wanting to count the higher of two scores only if it is above 59. Is there a way to enter two criteria such as this? I tried =COUNTIFS(B2>C2,B2>59)+COUNTIFS(B259)+COUNTIFS(D2>E2,D2>59)+COUNTIFS(D259) but it isn’t following all of the conditions. For example, if I have the following

    B2: 100
    C2: 90
    D2: 0
    E2: 89

    It is counting 3, but I only want it to count 2, the 100 because it is higher than 90 and above a 59, and the 89 because it is higher than 0 and above 59. Is there a way to do this?

    Thank you in advance for you help.

    1. Hello Nicole,

      Use formulas like this to count the highest value over 59 in each pair:
      =COUNTIF(B2:C2,IF(MAX(B2:C2)>59,MAX(B2:C2),0))

  4. I am working on a lotto checklist. I have my own numbers in 6 columns, A2:F2. I have typed the winning numbers in H2:M2, 6 columns also, with 1000 rows. Now I want to check if my numbers in A2:F2 have any one or more of the numbers present in the winning numbers in H2:M2. And if there is a match, highlight the matched cell in red background and white text.

    Any help is highly appreciated. Thank you.

  5. what if i wanted to use countif with vlookup if for example on another sheet i had customers in row c and in column 3 i had the different product names
    could i use countif vlookup to say find how many times red and dark chocolate both showed up i na row in the orignal data?
    thanks

    1. Hello Brian,

      Could you please elaborate on your task? You see, COUNTIF does a very similar job to VLOOKUP. It also scans a column/row looking for a certain data. So I'm not sure I understand how you'd like to combine them. If there are a few names you'd like to count, you'd better use COUNTIFS.

  6. I have a countif summing across 3 columns...into a single cell say A1. Now I need it to go down the rows and add the counts into the same single cell say A1.

    sample countif:

    =COUNTIF('AMER Exec Summary'!O3:O30, "*complete*")

    I know I could keep manually expanding the formula to include all the rows (28 and expanding but that will be a very long formula...

    Suspect it may need some soft of array?

    1. Hello Marc,

      If your goal is to change the range in one and the same cell (A1), you're right, you need to do it manually. Or, if that's ok for your task, you could use an extra column, add absolute cell references to your formula so certain parts don't change and copy the formula down the column. Then reference different cells from this column in your A1. Furthermore, you could even use COUNTIFS to test some additional criteria and return a certain count based on that.

  7. Hi!

    I'm trying to highlight rows using conditional formatting for students based on how well they're currently doing in all their classes.
    If they have less than 70% in one class, I want to highlight that row in yellow.
    If they have less than 70% in 2 classes, I want to highlight both rows in orange.
    If they have less than 70% in 3 or more classes, I want to highlight all those classes in red. (don't highlight the classes they're passing)

    Each student has five rows for five classes. Their name and ID are in all the rows.
    Column A is their name
    Column B is their unique student IDs
    Column C is the course name
    Column D is their grade out of 100%

    I setup conditional formatting to highlight red in A2:D26 for this:
    =COUNTIFS($D23)
    but it's invalid. :(

    All I have so far is conditional formatting to highlight yellow in A2:D26 for this:
    =$D2<70
    But this isn't helpful because we have 2000 students at our school.
    We want to reach out to the students highlighted red that are failing 3 or more classes first.

    1. Hi Emma,

      I've just found your spreadsheet :)

      I've created all three conditional formatting rules on the 'source data' sheet, please take a look :) The correct COUNTIFS formulas look like this:
      =AND(COUNTIFS($A$2:$A$26,$A2,$D$2:$D$26,"<70")=1,$D2<70)

      Feel free to read more about COUNTIFS for Google Sheets in this part of the article.

    2. Hi Emma,

      I'm sorry but the COUNTIFS formula you provided here looks incomplete. Please consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com) and let me know where your conditional formatting rules are set up. If you have confidential information there, you can replace it with some irrelevant data, just keep the format.
      Also, please include a second sheet with the result you expect to get. I will look into it and try to help.

      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.

  8. Hello,

    I'm wanting to make the cells blank using the countif formulas instead of having a page full of "0". I am curranty using the following formula =COUNTIF(JAN!B:B,A3) I have tried putting (,"") at the end of the formula, =COUNTIF(JAN!B:B,A3,"") or =COUNTIFS(JAN!B:B,A3,"") but it is giving me a error. Not sure if I'm using the correct formula for what I am wanting to do.

  9. How can I use COUNTIF with a query? I'm wanting to count the number of things within a certain range of a database.

    1. Hello Kate,

      Sorry, it's hard to suggest anything in particular as your task is not quite clear. Please describe it in more detail, I'll try to help.

      In the meantime, feel free to look through this article devoted to QUERY, you may find it helpful. The select, order by, label & format clauses support the count() function.

  10. Hi,

    I want to make count for multiple factors from different tabs.
    For example this is working formula for one tab: =COUNTIFS('TEST MASTER TRANSFER'!F:F,"=Adriatic",'TEST MASTER TRANSFER'!A:A, "=06/05/23",'TEST MASTER TRANSFER'!B:B, "=08:20") , and I want to add all same criteria from 5 more tabs? I do not know is it possible.

    1. Hi Goran,

      If you need the total of the counts from several sheets, simply add two different COUNTIFS functions in SUM:
      =SUM(COUNTIFS(...),COUNTIFS(...))

  11. Hello everyone,
    How to count Males and Females with unique names?

  12. Hi there,
    My spreadsheet has multiple rows that I would like to count, but they are separated by other text. In that situation, how would I format the =countif command?

    For example, I'd like to count rows B20:C22, B43:C45, and B67:C69, what would I put in?

    I tried this but to no avail =countif(B20:C22, B43:C45,B67:C69, "give and receive meaningful feedback")

    Thank you in advance.
    Bronson

      1. Hi Natalia,
        I tested out the sum function to include multiple ranges but it doesn't seem to count the numbers properly.

        =countif(sum('Grade 7'!B20:S22,'Grade 7'!B43:O45,'Grade 7'!B67:O69),"give and receive meaningful feedback")

        I get "0" when there should be "3"

        If I just do one range: =countif('Grade 7'!B20:S22,"give and receive meaningful feedback")

        I get the correct number of "3"

        Am I writing the formula incorrectly?

        1. Hi Bronson,

          Sorry if my suggestion wasn't clear. I actually meant using COUNTIF inside SUM, like this:
          =SUM(COUNTIF('Grade 7'!B20:S22,"give and receive meaningful feedback"), COUNTIF('Grade 7'!B43:O45,...), COUNTIF(...))

  13. In these above example, If I was to want to find the total quantities for Milk choc and Dark chocolate how would I program that?

  14. Hello- How can I count the number of times a value appears in a range but only the last n rows? I have a range A1:E1000 that data gets added to periodically. I want to find out how many times a number appears in the range, but only in the last 10 rows. Thank you for any info.

    1. Hello Jason,

      Supposing you're trying to count cells with A in the last 10 rows of the column D, here's the formula:
      =COUNTIF(OFFSET(D1:D,COUNTA(D:D)-10,0,10),"A")

  15. What if cells could have text that's identical to something longer (e.g., "Chocolate" by itself) that should be treated separately? For example, let's say I don't want a countif of Chocolate to include cells with anything before or after it, meaning it would show a count of 0 using the data in the example.

    Also, could it be set up (maybe using a countifs with "or" logic) to look for desired text in one of two possible columns per row without counting it twice if it's in both? Let's say there's a second column that could also have Chocolate or maybe something else, and I want to know how many rows it's true for rather than how many total occurrences.

    1. Hello Roger,

      To count cells with the exact text (e.g. only Chocolate) you simply put it to the formula as shown here. "Milk Chocolate" counts milk chocolates. Use "Chocolate" to count cells that have nothing but Chocolate.

      As for 2 possible columns, you can try something like this:
      =COUNTIF(A1:A10,"Chocolate") + COUNTIF(B1:B10,"Chocolate") - COUNTIFS(A1:A10,"Chocolate",B1:B10,"Chocolate")

  16. Hi

    I want to know the total value of column C, but only where the corresponding cell in column B says 'coffee'. Can you help?

  17. this worked for me =COUNTIF(D6:D16,"Milk Chocolate")
    But google changed from , to ;

    1. Hello Jeff,

      This is perfectly fine. Google Sheets uses different delimiters based on your spreadsheet locale.

  18. This is based off a True of False Checkbox Statement.

    I've figured out how to get a number value out of the system.

    =sumif(H13:H1000,True,M13:M1000) which gets me the value.

    I originally have an item, I put the value in the OnHold Column.

    I go back to the same location and discover that the item is now missing.

    I move that value over to the Can't Find Column.

    I've figured out how to count the number of instances I've made these movements.

    COUNTUNIQE replace SUMIF for the number of instances, if I separate everything

    =COUNTUNIQE(H13:H1000,True,M13:M1000) , counts the number of instances it's found in this move.

    Now, What I'm Trying to do is CountUniqe with a third variable.

    =COUNTUNIQUE(H13:H1000,TRUE,O13:O1000,"ITEMA",M13:M1000) : ITEMB, ITEMC

    No matter the Item, it returns the same value.

    What am I doing wrong?

    1. Hello Mamoru,

      Please consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com) highlighting cells with formulas and adding the expected result if any.
      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.

      I'll look into your task and try to help.

  19. Hi! I am trying to count the # of cells that have data in column F "" for a specific range determined by data in column A. Specifically- how many unique books in a certain category sent to a specific location. Column A is the category of book (picture book, graphic novel, fiction) and column F is a school name. Column F contains data for number of copies sent. I do not care about the value of column F (ie number of copies)- just how many entries there are in column F for range of column A. How many unique fiction titles were sent to Central High. Number of items in the category is constantly changing, so rather than determining range (A2:A45) I would rather specify the range based in content of column A "fiction".

    1. Hi Sunny,

      I'm sorry, I'm a bit confused by the description. For me to be able to help you better, please share a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data (2) the result you expect to get. The result sheet is of great importance as it gives 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 and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.

      I'll look into it. Thanks!

  20. Hi,
    I am trying to count how many cases per staff member against certain statuses. e.g. how many cases does person A have with the status App Taken, Approved, Submitted etc. what formula will I use? Names are in column C and the statuses in Column J. I tried countifs but it does not seem to work.

    Any help will be much appreciated.

    1. Hi Dhaval,

      If you need to count cases based on multiple criteria, COUNTIFS is exactly the function you need. Please provide the formula you tried that doesn't work for you, I'll try to help.

      1. Hi Natalia,

        The formula is =arrayformula(sum(countifs(C8:C396,">=C436",J8:J396,B436,B437,B438,B439,B440)))

        where column C has the name of the employee and column J has the criteria like approved, submitted etc. I need the formula to give me a count of how many active cases are there in that range for each empolyee.

        Thanks

        1. Hi Dhaval,

          Thank you for the formula.

          If I understand it correctly and those cells in column B contain statuses, here's how the correct formula should look like:
          =ArrayFormula(SUM(COUNTIFS(C8:C396,">=C436",J8:J396,OR(B436,B437,B438,B439,B440))))

          If, however, this doesn't work as well, please consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com) and specify where the formula is. I'll look into it.
          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.

  21. 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! :)

  22. 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?

  23. 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*")

  24. 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!

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

  26. 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.

  27. 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"})))

  28. 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

  30. 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")

  31. 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)

  32. 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?

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

  34. 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)...

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

  36. 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.

  37. 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.

  38. 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))

  39. 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))

  40. 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,"<>*---*"

  41. 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.

  42. 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

  43. 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" )

  44. 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

  45. 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

  46. 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.

  47. 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?

  48. 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.

  49. 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))`

  50. Thanks! It works!

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