Excel COUNTIF and COUNTIFS with OR logic

The tutorial explains how to use Excel's COUNTIF and COUNTIFS functions to count cells with multiple OR conditions, e.g. if a cell contains X, Y or Z.

As everyone knows, Excel COUNTIF function is designed to count cells based on just one criterion while COUNTIFS evaluates multiple criteria with AND logic. But what if your task requires OR logic - when several conditions are provided, any one can match to be included in the count?

There are a few possible solutions to this task, and this tutorial will cover them all in full detail. The examples imply that you have a sound knowledge of the syntax and general uses of both functions. If not, you may want to begin with revising the basics:

Excel COUNTIF function - counts cells with one criteria.

Excel COUNTIFS function - counts cells with multiple AND criteria.

Now that everyone is on the same page, let's dive in:

Count cells with OR conditions in Excel

This section covers the simplest scenario - counting cells that meet any (at least one) of the specified conditions.

Formula 1. COUNTIF + COUNTIF

The easiest way to count cells that have one value or another (Countif a or b) is to write a regular COUNTIF formula to count each item individually, and then add the results:

COUNTIF(range, criterion1) + COUNTIF(range, criterion2)

As an example, let's find out how many cells in column A contain either "apples" or "bananas":

=COUNTIF(A:A, "apples") + COUNTIF(A:A, "bananas")

In real-life worksheets, it is a good practice to operate on ranges rather than entire columns for the formula to work faster. To spare the trouble of updating your formula every time the conditions change, type the items of interest in predefined cells, say F1 and G1, and reference those cells. For example:

=COUNTIF(A2:A10, F1) + COUNTIF(A2:A10, G1)
Count cells that have one value or another.

This technique works fine for a couple of criteria, but adding three or more COUNTIF functions together would make the formula too cumbersome. In this case, you'd better stick with one of the following alternatives.

Formula 2. COUNTIF with array constant

Here's a more compact version of the SUMIF with OR conditions formula in Excel:

SUM(COUNTIF(range, {criterion1, criterion2, criterion3, …}))

The formula is constructed in this way:

First, you package all the conditions in an array constant - individual items separated by commas and the array enclosed in curly braces like {"apples", "bananas', "lemons"}.

Then, you include the array constant in the criteria argument of a normal COUNTIF formula: COUNTIF(A2:A10, {"apples","bananas","lemons"})

Finally, warp the COUNTIF formula in the SUM function. It is necessary because COUNTIF will return 3 individual counts for "apples", "bananas" and "lemons", and you need to add those counts together.

Our complete formula goes as follows:

=SUM(COUNTIF(A2:A10,{"apples","bananas","lemons"}))
COUNTIF with an array constant to count cells with OR logic

If you'd rather supply your criteria as range references, you'll need to enter the formula with Ctrl + Shift + Enter to make it an array formula. For example:

=SUM(COUNTIF(A2:A10,F1:H1))

Please notice the curly braces in the screenshot below - it is the most evident indication of an array formula in Excel:
Array formula to sum cells based on criteria as range references.

Formula 3. SUMPRODUCT

Another way to count cells with OR logic in Excel is to use the SUMPRODUCT function in this way:

SUMPRODUCT(1*(range ={criterion1, criterion2, criterion3, …}))

To better visualize the logic, this could also be written as:

SUMPRODUCT((range=criterion1) + (range=criterion2) + …)

The formula tests each cell in the range against each criterion and returns TRUE if the criterion is met, FALSE otherwise. As an intermediate result, you get a few arrays of TRUE and FALSE values (the number of arrays equals the number of your criteria). Then, the array elements in the same position are added together, i.e. the first elements in all the arrays, the second elements, and so on. The addition operation converts the logical values to numbers, so you end up with one array of 1's (one of the criteria matches) and 0's (none of the criteria matches). Because all the criteria are tested against the same cells, there is no way any other number could appear in the resulting array - only one initial array can have TRUE in a specific position, others will have FALSE. Finally, SUMPRODUCT adds up the elements of the resulting array, and you get the desired count.

The first formula works in a similar manner, with the difference that it returns one 2-dimentional array of TRUE and FALSE values, which you multiply by 1 to convert the logical values to 1 and 0, respectively.

Applied to our sample data set, the formulas take the following shape:

=SUMPRODUCT(1*(A2:A10={"apples","bananas","lemons"}))

Or

=SUMPRODUCT((A2:A10="apples") + (A2:A10="bananas") + (A2:A10="lemons"))

Replace the hardcoded array constant with a range reference, and you will get even a more elegant solution:

=SUMPRODUCT(1*( A2:A10=F1:H1))
SUMPRODUCT formula to count cells with OR logic

Note. The SUMPRODUCT function is slower than COUNTIF, which is why this formula is best to be used on relatively small data sets.

Count cells with OR as well as AND logic

When working with large data sets that have multi-level and cross-level relations between elements, chances are that you will need to count cells with OR and AND conditions at a time.

As an example, let's get a count of "apples", "bananas" and "lemons" that are "delivered". How do we do that? For starters, let's translate our conditions into Excel's language:

  • Column A: "apples" or "bananas" or "lemons"
  • Column C: "delivered"

Looking from another angle, we need to count rows with "apples and delivered" OR "bananas and delivered" OR "lemons and delivered". Put this way, the task boils down to counting cells with 3 OR conditions - exactly what we did in the previous section! The only difference is that you'll utilize COUNTIFS instead of COUNTIF to evaluate the AND criterion within each OR condition.

Formula 1. COUNTIFS + COUNTIFS

It is the longest formula, which is the easiest to write :)

=COUNTIFS(A2:A10, "apples", C2:C10, "delivered") + COUNTIFS(A2:A10, "bananas", C2:C10, "delivered")) + COUNTIFS(A2:A10, "lemons", C2:C10, "delivered"))

The screenshot below shows the same formula with cells references:

=COUNTIFS(A2:A10, K1, C2:C10, K2) + COUNTIFS(A2:A10, L1, C2:C10, K2) + COUNTIFS(A2:A10, M1,C2:C10, K2)
Add two or more COUNTIFS to count cells with OR as well as AND logic.

Formula 2. COUNTIFS with array constant

A more compact COUNTIFS formula with AND/OR logic can be created by packaging OR criteria in an array constant:

=SUM(COUNTIFS(A2:A10, {"apples","bananas","lemons"}, C2:C10, "delivered"))

When using a range reference for the criteria, you need an array formula, completed by pressing Ctrl + Shift + Enter:

=SUM(COUNTIFS(A2:A10,F1:H1,C2:C10,F2))
COUNTIFS with an array constant to count cells with AND/OR logic

Tip. If needed, you are free to use wildcards in the criteria of any formulas discussed above. For example, to count all sorts of bananas such as "green bananas" or "goldfinger bananas" you can use this formula:

=SUM(COUNTIFS(A2:A10, {"apples","*bananas*","lemons"}, C2:C10, "delivered"))

In a similar manner, you can build a formula to count cells based on other criteria types. For example, to get a count of "apples" or "bananas" or "lemons" that are "delivered" and the amount is greater than 200, add one more criteria range/criteria pair to COUNTIFS:

=SUM(COUNTIFS(A2:A10, {"apples","*bananas*","lemons"}, C2:C10, "delivered", B2:B10, ">200"))

Or, use this array formula (entered via Ctrl + Shift + Enter):

=SUM(COUNTIFS(A2:A10,F1:H1,C2:C10,F2, B2:B10, ">"&F3))
Count cells based on three AND/OR criteria of different types.

Count cells with multiple OR conditions

In the previous example, you have learned how to test one set of OR conditions. But what if you have two or more sets and you are looking to get a total of all possible OR relations?

Depending on how many conditions you need to handle, you can use either COUNTIFS with an array constant or SUMPRODUCT with ISNUMBER MATCH. The former is relatively easy to build, but it is limited to only 2 sets of OR conditions. The latter can evaluate any number of conditions (a reasonable number, of course, given Excel's limit to 255 arguments and 8192 characters to the total formula length), but it may take some effort to grasp the formula's logic.

Count cells with 2 sets of OR conditions

When dealing with only two sets of OR criteria, just add one more array constant to the COUNTIFS formula discussed above.

For the formula to work, one minute but critical change is needed: use a horizontal array (elements separated by commas) for one criteria set and vertical array (elements separated by semicolons) for the other. This tells Excel to "pair" or "cross-calculate" the elements in the two arrays, and return a two-dimensional array of the results.

As an example, let's count "apples", "bananas" or "lemons" that are either "delivered" or "in transit":

=SUM(COUNTIFS(A2:A10, {"apples", "bananas", "lemons"}, B2:B10, {"delivered"; "in transit"}))

Please note the semicolon in the second array constant:
Count cells with 2 sets of OR conditions.

Because Excel is a 2-dimentional program, it is not possible to construct a 3-dimentional or 4-dimentuional array, and therefore this formula only works for two sets of OR criteria. To count with more criteria, you will have to switch to a more complex SUMPRODUCT formula explained in the next example.

Count cells with multiple sets of OR conditions

To count cells with more than two sets of OR criteria, use the SUMPRODUCT function together with ISNUMBER MATCH.

For example, let's get a count of "apples", "bananas" or "lemons" that are either "delivered" or "in transit" and are packaged in either "bag" or "tray":

=SUMPRODUCT(ISNUMBER(MATCH(A2:A10,{"apples","bananas","lemons"},0))*
ISNUMBER(MATCH(B2:B10,{"bag","tray"},0))*
ISNUMBER(MATCH(C2:C10,{"delivered","in transit"},0)))

In the heart of the formula, the MATCH function checks the criteria by comparing each cell in the specified range with the corresponding array constant. If the match is found, it returns a relative position of the value if the array, N/A otherwise. ISNUMBER converts these values to TRUE and FALSE, which equate to 1 and 0, respectively. SUMPRODUCT takes it from there, and multiplies the arrays' elements. Because multiplying by zero gives zero, only the cells that have 1 in all the arrays survive and get summed.

Th screenshot below shows the result:
Count cells with multiple sets of OR conditions.

This is how you use the COUNTIF and COUNTIFS functions in Excel to count cells with multiple AND as well as OR conditions. To have a closer look at the formulas discussed in this tutorial, you are welcome to download our sample workbook below. I thank you for reading and hope to see you on our blog next week!

Practice workbook

Excel COUNTIF with OR conditions - examples (.xlsx file)

Table of contents

170 comments

  1. Hi ,

    Let's say I have 2 columns Column A - 1-20 and Column B - 31 - 50. How can I count the unique entries when column A is > 10 or Column B is > 40. Kindly help.

  2. Hi Sir,

    I am creating schedules for our staff. Using below data and trying to count the actual working employees total and ignore employees those are on training and days off. I am using this formula but its not working . Please help me
    =SUMIF($D$6:$D$14,"TRAINING",F6:F14) + SUMIF(F6:F14,"D/O",F6:F14)
    AM Leads 05:45 - 14:15
    D E F G H
    LEADS EMPL # Sun Mon Tue Wed Thu Fri Sat
    1 TRAINING D/O D/O 05:45 - 14:15 05:45 - 14:15 05:45 - 14:15 05:45 - 14:15 05:45 - 14:15
    2 ANNY 05:45 - 14:15 05:45 - 14:15 D/O D/O 05:45 - 14:15 05:45 - 14:15 05:45 - 14:15
    3 PERRI 05:45 - 14:15 05:45 - 14:15 05:45 - 14:15 05:45 - 14:15 D/O D/O 05:45 - 14:15
    4 JAM D/O 05:45 - 14:15 05:45 - 14:15 05:45 - 14:15 05:45 - 14:15 05:45 - 14:15 D/O
    5 KLM 05:45 - 14:15 D/O D/O 05:45 - 14:15 05:45 - 14:15 05:45 - 14:15 05:45 - 14:15
    6 BOBBY 05:45 - 14:15 05:45 - 14:15 05:45 - 14:15 D/O D/O 05:45 - 14:15 05:45 - 14:15
    7 TRAINING 05:45 - 14:15 05:45 - 14:15 05:45 - 14:15 05:45 - 14:15 05:45 - 14:15 D/O D/O
    8 TRAINING D/O D/O 05:45 - 14:15 05:45 - 14:15 05:45 - 14:15 05:45 - 14:15 05:45 - 14:15
    9 TRAINING 05:45 - 14:15 05:45 - 14:15 D/O D/O 05:45 - 14:15 05:45 - 14:15 05:45 - 14:15
    Daily Total 6 6 6 6 7 7 7
    Actual Daily 4 4 0 0 0 0 0
    Variance +(-) 2

  3. Hi
    My current issue is i have to sheets lets say
    Sheet1 is where the formula is so
    Countif(sheet2A:A;$C$3) this formula will be in cell C5 and the C6,C7, C8

    What i need is to move the auto fill series to move ranges on sheet2 to be filled as the formula on C6 to query B:B, C7 to query C:C and so on

    Can you help me with that please

  4. What do I do if I need to do count cells with multiple sets of OR conditions, but one of or conditions is not match, but rather a countif (ie >0)? Thanks!

    • Hi!
      Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail, I’ll try to suggest a solution.

  5. Hi, I'm trying to assemble multiple AND/OR logic. The first group I can solve with SUM(COUNTIFS, but getting stuck after the OR logic

    Count cells if:
    column F is one of: "*Scope*","*Component*","*Vendor*" AND column E = A2 (let's call this group1)
    OR
    column F = "*Decommission*" AND column I "*Production*" AND column E = A2 (let's call this group2)

    Final result should be total cell count of group1 + group2
    (wildcards intentional)
    Thank you

    • note in the second group column I doesn't equal "*Production*" - looks like my symbols were removed

  6. I have excel sheet for result analysis range H7:ET600. I have to count "AB" from some columns {e.g. J, W, AJ, AW, BJ, BV, CC, CO, DB, DO, EB, EP columns } only. I can't select range because columns other than {J, W, AJ, AW, BJ, BV, CC, CO, DB, DO, EB, EP} these one also have "AB" entry. how I can count it?

    • Hi!
      If I understand your task correctly, the example following formula should work for you:

      =SUM(--(A1:A10="AB"),--(C1:C10="AB"),--(E1:E10="AB"))

      Specify each column individually.

  7. How to count the occurrence of each number (range from 1 to 14) in a cell with a number pattern 1/12/5/1/13/10 or 10/11/9 or 8/5 ?

  8. Good day
    I'm trying to automate a count on a per class basis and a age.

    my current formula is this:

    =SUM(COUNTIFS('PC Spec CPT'!$A$1:$L$171, "*CLASS1*", 'PC Spec CPT'!$A$1:$L$171, {"5 years","6 years","7 years","8 years","9 years","10 years"}))

    This is returning a value of 0
    but if I do the counts separately I get the correct values. so "count class1", gives 39 and count (the different years) gives a value of 151 (as its searching the whole data set, but I only need it to count for class1 (Which is actually from A3 to L41) but I want to future proof for if I add or remove from the set

    So the count cant be 0

    • I think i may have figured it out...
      =SUM(COUNTIFS('PC Spec CPT'!$B$3:$B$171, "*CLASS1*",'PC Spec CPT'!$J$3:$J$171,{"5 years","6 years","7 years","8 years","9 years","10 years"}))

      Appears to give the correct result... I just needed to isolate the columns for the 2 criteria instead of doing the whole data set

    • Hi!
      The COUNTIFS formula counts the rows where all the criteria are met once. But at the same time, it cannot work with a range ($A$1:$L$171), but only with a data column ($A$1:$A$171).

  9. Awesome! Thanks a lot for all clarifications in here!

  10. Can you help....?

    I have a spreadsheet with data collected from an electronically completed survey. Some of the columns are free text entry from the form. I want to summarise the submissions and categorise according to job role. However, there are a huge number of terms used for example for doctors....."registrar", "SHO", "consultant" "consultant anaesthetist", "anaesthetist", "FY2", "clinical fellow" etc. I am trying to use SUM and COUNTIFS to give me the number of forms that were submitted for a group of staff in one month. However, I am getting entries double counted as if the free text includes "consultant anaesthetist" then it is being counted twice as I want to ensure that entries that have only described the doctor as a consultant or anaesthetist are counted....

    This is my formula I have named ranges Date and Recipient, H5 and I5 are cell references to specific dates.

    =SUM(COUNTIFS(Date,">="&H$5,Date,"<"&I$5,Recipient,{"*Doctor*","*anaesthetist*","*surgeon*","*consultant*","*SAS*","*fellow*","*sho*","*reg*","*CT1*","*CT2*","*CT3*","*ST1*","*ST2*","*ST3*","*ST4*","*ST5*","*ST6*","*ST7*","*FY1*","*FY2*","associate specialist"}))

    • Hello!
      To avoid double counting, in a separate column, write something like this SEARCH formula with all the words you need. Then use that column to count in the COUNTIFS formula.

      =--(SUM(--ISNUMBER(SEARCH({"Doctor","anaesthetist"},A2)))>0)

  11. Good morning! You all are so incredibly helpful, I just hope I can describe my issue well enough for you to understand! I have a table that looks like this:
    Virtual Virtual Virtual
    Onsite Onsite
    OTT OTT
    Jon X X X
    Dan X X X
    Rob X X X
    Tim X X X

    I need to count the number of times each person is either Onsite or OTT. The results I should get are:
    Jon 2
    Dan 1
    Rob 0
    Tim 3

    However, when I try to use normal COUNTIFS it counts Jon and Tim twice because column C has both Onsite AND OTT. I need it to count Onsite OR OTT. I tried to use the OR logic you outlined... =SUM(COUNTIFS($B$1:$G$3,{"Onsite","OTT"},B4:G4,"X")) but that doesn't work with multiple rows. How can I get this to calculate correctly? I feel like I'm so close...

    • Okay, so multiple spaces get stripped out... Let's try with dots...
      ..........Virtual................Virtual................Virtual
      ........................Onsite...........................................Onsite
      ........................OTT....................OTT
      Jon......................X.........................X.............X
      Dan.........X.......................X......................................X
      Rob.........X.......................X...........................X
      Tim.....................X..........................X........................X

  12. I am working on a spreadsheet with Start Date, Resolve Date, and Status. I want to look at the amount of work being done in the current month. For example, if I'd like to look at the number of things completed in August, I could count everything initiated in August that has a complete status PLUS everything resolved in August with a complete status. BUT, the problem is that some things can be initiated in August and Resolved in August, which would cause the same issue to be double counted.

    My current formula for counting total complete based on start date is
    =COUNTIFS(StartDate,">="&EOMONTH(TODAY(),-1)+1,StartDate,"="&EOMONTH(TODAY(),-1)+1,Resolved,"<"&EOMONTH(TODAY(),0)+1,Status,"Completed")

    I'm not quite sure how to combine these into one formula and prevent the double counting of items that are completed with a start and resolve date in the same month. Any help would be greatly appreciated!!!

    • I typed in two formulas, but it looks like it somehow got combined into two?? They should be as follows:

      Based on start date:
      =COUNTIFS(StartDate,">="&EOMONTH(TODAY(),-1)+1,StartDate,"="&EOMONTH(TODAY(),-1)+1,Resolved,"<"&EOMONTH(TODAY(),0)+1,Status,"Completed")

      • I don't know why this keeps happening... There are two formulas that I'm typing in, with a new paragraph between them. My paragraph keeps getting deleted and the formulas partially combined.

    • Hello!
      I can't check the formula that contains unique references to your workbook worksheets. Count the records that meet the first condition, add the records that meet the second condition, and subtract the records that meet both conditions.

      =COUNTIFS(StartDate,”>= ”&EOMONTH(TODAY(),-1)+1,Status,"Completed") + COUNTIFS(Resolved,”<"&EOMONTH(TODAY(),0)+1,Status,"Completed") - COUNTIFS(StartDate,”>=”&EOMONTH(TODAY(),-1)+1, Resolved,”<"&EOMONTH(TODAY(),0)+1,Status,"Completed")

  13. Hi,

    I am trying to create a column that will provide me a subset of orders that need to be reconciled between two ERP systems. However, there are three different types of ways that one system will recognize an order.

    I am hoping that this column will catch IF it exists in one system under one reference, but right now there are cases where an order can be recognized by all three references, just depends on user input.

    Trying to acheive a formula that will count Order Ref 1 OR Order Ref 2 or Order Ref 3. If the system recognizes it as Order Ref 1 / Order Ref 2 I only want it to count once towards the total subset.

    Current formula: =COUNTIF('TMS DATA'!A:A, Table2[[#All],[Order '#]])+COUNTIF('TMS DATA'!A:A,Table2[[#All],[Shipment'#]])+COUNTIF('TMS DATA'!A:A,Table2[[#All],[Order ID]])

    Can you help me with the OR part that I am missing? I feel like I am just counting them all.

    • Hi!
      It is very difficult to understand a formula that contains unique references to your workbook worksheets. Unfortunately, without seeing your data it is difficult to give you any advice. Please provide me with an example of the source data and the expected result.

  14. Can anyone help with the formula?

    I have 10 drivers and 10 vehicles, Each day wages of driving individual vehicle is different. I.e Vehicle A wages 500, vehicle B wages 1000 and Vehicle C Wages 1500. Now Driver A had drove those vehicles in term of 15 days. How would I calculate this with the formula.

  15. Hello and thank you for you dedication to helping people!

    I believe there is an issue with the AND/OR logic part of the article in that the formula generates "false positives" which must be subtracted to get a correct answer.

    LIST A LIST B LIST C LIST D
    TRUE TRUE TRUE TRUE
    TRUE TRUE TRUE FALSE
    TRUE TRUE FALSE TRUE
    TRUE TRUE FALSE FALSE
    TRUE TRUE TRUE TRUE
    TRUE TRUE TRUE FALSE
    TRUE FALSE FALSE TRUE
    TRUE FALSE FALSE FALSE
    TRUE FALSE TRUE TRUE
    TRUE FALSE TRUE FALSE
    TRUE FALSE FALSE TRUE
    TRUE FALSE FALSE FALSE

    For instance, if (A and B) or C or D are true in the above data the result should be 5 (not 7) as below:

    =COUNTIFS(LIST_A,TRUE,LIST_B,TRUE,LIST_C,TRUE)+COUNTIFS(LIST_A,TRUE,LIST_B,TRUE,LIST_D,TRUE)-COUNTIFS(LIST_A,TRUE,LIST_B,TRUE,LIST_C,TRUE,LIST_D,TRUE)

    Here's the rub. If I have additional columns (not shown and also not actual data) and need to find (A and B) or C or D or E or F or G or H or I or J or K or L in a list of 100,000 items (peak season), is an elegant solution possible?

    Thanks and I do appreciate your work!

    • Hi!
      Your formula returns 5. But if I understood your conditions correctly, the result should be 3.
      If I understand your task correctly, the following formula should work for you:

      =SUM(--((A2:A13+B2:B13+(C2:C13+D2:D13=1)=3)))

      I hope I answered your question. If something is still unclear, please feel free to ask.

      • Thank you for the prompt response. Unfortunately I did not clearly state the full nature of the problem.

        Data is not "true" or "false" but could be text, currency, value, date, blank, etc.
        Each line is counted only once based on conditions further explained as follows:
        List A and List B must both be "true" for a line to be counted.
        Lists E through L must have at least one condition "true" for a line to be counted.
        Each line may only be counted as one instance.
        So lines 1, 2, 3, 5, 6 should be the only lines counted for a total of 5 lines meeting the conditions.

        Helper columns discouraged because of the size of the data.

        I perceive this as Wizardry at this point.

        • Hi!
          If I understand your task correctly, try the following formula:

          =SUM(--(((A2:A13=TRUE)*(B2:B13=TRUE)*100+(C2:C13=TRUE)+(D2:D13=TRUE)) > 100))

          • Sir, that is a brilliant and imaginative solution! This is a truly remarkable method for implementing complex AND/OR conditions in Excel. Thank you and may the formulas be always in your favor.

  16. Hello. The "count cells with multiple sets of OR conditions" formula described above was extremely helpful for me. Thank you!

    Let's say there's a "Date_Shipped" column in D:D.

    How can I find {apples, bananas, lemons}, {bag,tray}, {delivered, in transit} ***where Date_Shipped > mm/dd/yyyy and Date_Shipped < mm/dd/yyyy***

    This is the formula from the example above I'm using and just need to figure out how to incorporate the date part:

    =SUMPRODUCT(ISNUMBER(MATCH(A2:A10,{"apples","bananas","lemons"},0))*

    ISNUMBER(MATCH(B2:B10,{"bag","tray"},0))*

    ISNUMBER(MATCH(C2:C10,{"delivered","in transit"},0)))

    Thanks for any help you can offer. Cheers!

    • Hello!
      If you write dates in column B, then you can compare them with the desired date using the DATE function.

      =SUMPRODUCT(ISNUMBER(MATCH($A$2:$A$10, {"apples","bananas","lemons"},0))*ISNUMBER(MATCH($B$2:$B$10, {"bag","tray"},0))*ISNUMBER(MATCH($C$2:$C$10, {"delivered","in transit"},0))*(--($D$2:$D$10 > DATE(2022,2,1))*(--($D$2:$D$10 < DATE(2022,3,1)))))

      Hope this is what you need.

      • Thank you! I was not familiar with the double minus (--). This is exactly what I needed and the formula works like a charm. CHEERS!

  17. I have a worksheet that I am working for work. Basically, I would like to count how many people visit the park (column A) in both year 2020 and 2021(column B). Column B only shows either 2020 or 2021.

    This formula: =SUM(COUNTIFS(Table2[List YR], {"2020","2021"}, Table2[COUNTY], A2))
    - provides number of people that visit on 2020 and number of people that visit on 2021, not the number of people that visit both year.

    This formula: =COUNTIFS(SPEC!A:A,A2,SPEC!B:B,2020,SPEC!B:B,2021)
    - provides 0

    Please help

    • Hello!
      Add column C to the table with the formula

      =IF(COUNTIFS($A$2:$A$10,A2,$B$2:$B$10,2021)>0,2021,"")

      Count the number of people using COUNTIFS:

      =COUNTIFS(B2:B10,2020,C2:C10,2021)

      I hope my advice will help you solve your task.

  18. If a student gets mark less than 16 or his average be less than 50 he must be in group D. Please write a formula for it

  19. Hello!
    My husband is trying to create a spreadsheet for work and cannot seem to find a formula that works.
    He is trying to use the COUNTIFS function to count all cells in a range that are within separate number ranges.

    He needs the cells to be counted once if they are below 517, or between 525 and 896, or above 905. Every formula we have tried has not worked. It either does not count the cells, or counts them more than once.

    This is an example of one of the formulas we have tried:

    =COUNTIFS(C3:I4,">=0","525","905")

    I know this is not right, but I have scoured the internet but have not found a solution.

    Thank you!

  20. Hey, can you help me with a google sheet formula?

    =SUM(COUNTIF('Signed Case'!C:C,{"Barry","Maya"}))

    It seems that Google Sheet can only count the total number of "Barry" but NOT BOTH "Barry" and "Maya", is there a way to fix 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 :)