by The tutorial explains how to use COUNTIFS and COUNTIF formulas with multiple criteria in Excel based on AND as well as OR logic. You will find a number of examples for different data types - numbers, dates, text, wildcard characters, non-blank cells and more.
Of all Excel functions, COUNTIFS and COUNTIF are probably most often mixed up because they look very much alike and both are purposed for counting cells based on the specified criteria.
The difference is that COUNTIF is designed for counting cells with a single condition in one range, whereas COUNTIFS can evaluate different criteria in the same or in different ranges. The aim of this tutorial is to demonstrate different approaches and help you choose the most efficient formula for each particular task.
Excel COUNTIFS function - syntax and usage
The Excel COUNTIFS function counts cells across multiple ranges based on one or several conditions. The function is available in Excel 365, 2021, 2019, 2016, 2013, Excel 2010, and Excel 2007, so you can use the below examples in any Excel version.
COUNTIFS syntax
The syntax of the COUNTIFS function is as follows:
- criteria_range1 (required) - defines the first range to which the first condition (criteria1) shall be applied.
- criteria1 (required) - sets the condition in the form of a number, cell reference, text string, expression or another Excel function. The criteria defines which cells shall be counted and can be expressed as 10, "<=32", A6, "sweets".
- [criteria_range2, criteria2]… (optional) - these are additional ranges and their associated criteria. You can specify up to 127 range/criteria pairs in your formulas.
In fact, you don't have to remember the syntax of the COUNTIF function by heart. Microsoft Excel will display the function's arguments as soon as you start typing; the argument you are entering at the moment is highlighted in bold.
Excel COUNTIFS - things to remember!
- You can use the COUNTIFS function in Excel to count cells in a single range with a single condition as well as in multiple ranges with multiple conditions. If the latter, only those cells that meet all of the specified conditions are counted.
- Each additional range must have the same number of rows and columns as the first range (criteria_range1 argument).
- Both contiguous and non-contiguous ranges are allowed.
- If the criteria is a reference to an empty cell, the COUNTIFS function treats it as a zero value (0).
- You can use the wildcard characters in criteria - asterisk (*) and question mark (?). See this example for full details.
How to use COUNTIFS and COUNTIF with multiple criteria in Excel
Below you will find a number of formula examples that demonstrate how to use the COUNTIFS and COUNTIF functions in Excel to evaluate multiple conditions.
How to count cells with multiple criteria (AND logic)
This scenario is the easiest one, since the COUNTIFS function in Excel is designed to count only those cells for which all of the specified conditions are TRUE. We call it the AND logic, because Excel's AND function works this way.
Formula 1. COUNTIFS formula with multiple criteria
Suppose you have a product list like shown in the screenshot below. You want to get a count of items that are in stock (value in column B is greater than 0) but have not been sold yet (value is column C is equal to 0).
The task can be accomplished by using this formula:
=COUNTIFS(B2:B7,">0", C2:C7,"=0")
And the count is 2 ("Cherries" and "Lemons"):
Formula 2. COUNTIFS formula with two criteria
When you want to count items with identical criteria, you still need to supply each criteria_range / criteria pair individually.
For example, here's the right formula to count items that have 0 both in column B and column C:
=COUNTIFS($B$2:$B$7,"=0", $C$2:$C$7,"=0")
This COUNTIFS formula returns 1 because only "Grapes" have "0" value in both columns.
Using a simpler formula with a single criteria_range like COUNTIFS(B2:C7,"=0") would yield a different result - the total count of cells in the range B2:C7 containing a zero (which is 4 in this example).
How to count cells with multiple criteria (OR logic)
As you have seen in the above examples, counting cells that meet all of the specified criteria is easy because the COUNTIFS function is designed to work this way.
But what if you want to count cells for which at least one of the specified conditions is TRUE, i.e. based on the OR logic? Overall, there are two ways to do this - by adding up several COUNTIF formulas or using a SUM COUNTIFS formula with an array constant.
Formula 1. Add up two or more COUNTIF or COUNITFS formulas
In the table below, supposing you want to count orders with the "Cancelled" and "Pending" status. To have it doen, you can simply write 2 regular Countif formulas and add up the results:
=COUNTIF($C$2:$C$11,"Cancelled") + COUNTIF($C$2:$C$11,"Pending")
In case each of the functions is supposed to evaluate more than one condition, use COUNTIFS instead of COUNTIF. For example, to get the count of "Cancelled" and "Pending" orders for "Apples" use this formula:
=COUNTIFS($A$2:$A$11, "Apples", $C$2:$C$11,"Cancelled") + COUNTIFS($A$2:$A$11, "Apples", $C$2:$C$11,"Pending")
Formula 2. SUM COUNTIFS with an array constant
In situations when you have to evaluate a lot of criteria, the above approach is not the best way to go because your formula would grow too big in size. To perform the same calculations in a more compact formula, list all of your criteria in an array constant, and supply that array to the criteria argument of the COUNTIFS function. To get the total count, embed COUNTIFS inside the SUM function, like this:
In our sample table, to count orders with the status "Cancelled" or "Pending" or "In transit", the formula would go as follows:
=SUM(COUNTIFS($C$2:$C$11, {"cancelled", "pending", "in transit"}))
In a similar manner, you can count cells based on two or more criteria_range / criteria pairs. For instance, to get the number of "Apples" orders that are "Cancelled" or "Pending" or "In transit", use this formula:
=SUM(COUNTIFS($A$2:$A$11,"apples",$C$2:$C$11,{"cancelled","pending","in transit"}))
You can find a few more ways to count cells with OR logic in this tutorial: Excel COUNTIF and COUNTIFS with OR conditions.
How to count numbers between 2 specified numbers
By and large, COUNTIFS formulas for numbers fall into 2 categories - based on several conditions (explained in the above examples) and between the two values you specify. The latter can be accomplished in two ways - by using the COUNTIFS function or by subtracting one COUNTIF from another.
Formula 1. COUNTIFS to count cells between two numbers
To find out how many numbers between 5 and 10 (not including 5 and 10) are contained in cells C2 through C10, use this formula:
=COUNTIFS(C2:C10,">5", C2:C10,"<10")
To include 5 and 10 in the count, use the "greater than or equal to" and "less than or equal to" operators:
=COUNTIFS(B2:B10,">=5", B2:B10,"<=10")
Formula 2. COUNTIF formulas to count numbers between X and Y
The same result can be achieved by subtracting one Countif formula from another. The first one counts how many numbers are greater than the lower bound value (5 in this example). The second formula returns the count of numbers that are greater than the upper bound value (10 in this case). The difference between the first and second number is the result you are looking for.
- =COUNTIF(C2:C10,">5")-COUNTIF(C2:C10,">=10") - counts how many numbers greater than 5 and less than 10 are in the range C2:C10. This formula will return the same count as shown in the screenshot above.
- =COUNTIF(C2:C10, ">=5")-COUNTIF(C2:C10, ">10") - the formula counts how many numbers between 5 and 10 are in the range C2:C10, including 5 and 10.
How to use cell references in COUNTIFS formulas
When using logical operators such as ">", "<", "<=" or ">=" together with cell references in your Excel COUNTIFS formulas, remember to enclose the operator in "double quotes" and
add an ampersand (&) before a cell reference to construct a text string.
In a sample dataset below, let's count "Apples" orders with amount greater than $200. With criteria_range1 in cells A2:A11 and criteria_range2 in B2:B11, you can use this formula:
=COUNTIFS($A$2:$A$11, "Apples", $B$2:$B$11, ">200")
Or, you can input your criteria values in certain cells, say F1 and F2, and reference those cells in your formula:
=COUNTIFS($A$2:$A$11, $F$1, $B$2:$B$11, ">"&$F$2)
Please notice the use of absolute cell references both in the criteria and criteria_range arguments, which prevents the formula from being broken when copied to other cells.
For more information about the use of an ampersand in COUNTIF and COUNTIFS formulas, please see Excel COUNTIF - frequently asked questions.
How to use COUNTIFS with wildcard characters
In Excel COUNTIFS formulas, you can use the following wildcard characters:
- Question mark (?) - matches any single character, use it to count cells starting and/or ending with certain characters.
- Asterisk (*) - matches any sequence of characters, you use it to count cells containing a specified word or a character(s) as part of the cell's contents.
Tip. If you want to count cells with an actual question mark or asterisk, type a tilde (~) before an asterisk or question mark.
Now let's see how you can use a wildcard char in real-life COUNTIFS formulas in Excel. Suppose, you have a list of projects in column A. You wish to know how many projects are already assigned to someone, i.e. have any name in column B. And because we are learning how to use the COUNTIFS function with multiple criteria, let's add a second condition - the End Date in column D should also be set.
Here is the formula that works a treat:
=COUNTIFS(B2:B10,"*",D2:D10,"<>"&""))
Please note, you cannot use a wildcard character in the 2nd criteria because you have dates rather that text values in column D. That is why, you use the criteria that finds non-blank cells: "<>"&""
COUNTIFS and COUNTIF with multiple criteria for dates
The COUNTIFS and COUNTIF formulas you use for dates are very much similar to the above formulas for numbers.
Example 1. Count dates in a specific date range
To count the dates that fall in a certain date range, you can also use either a COUNTIFS formula with two criteria or a combination of two COUNTIF functions.
For example, the following formulas count the number of dates in cells C2 through C10 that fall between 1-Jun-2014 and 7-Jun-2014, inclusive:
=COUNTIFS(C2:C9, ">=6/1/2014", C2:C9, "<=6/7/2014")
=COUNTIF(C2:C9, ">=6/1/2014") - COUNTIF(C2:C9, ">6/7/2014")
Example 2. Count dates with multiple conditions
In the same manner, you can use a COUNTIFS formula to count the number of dates in different columns that meet 2 or more conditions. For instance, the below formula will find out how many products were purchased after the 20th of May and delivered after the 1st of June:
=COUNTIFS(C2:C9, ">5/1/2014", D2:D9, ">6/7/2014")
Example 3. Count dates with multiple conditions based on the current date
You can use Excel's TODAY() function in combination with COUNTIF to count dates based on the current date.
For example, the following COUNTIF formula with two ranges and two criteria will tell you how many products have already been purchased but not delivered yet.
=COUNTIFS(C2:C9, "<"&TODAY(), D2:D9, ">"&TODAY())
This formula allows for many possible variations. For instance, you can tweak it to count how many products were purchased more than a week ago and are not delivered yet:
=COUNTIFS(C2:C9, "<="&TODAY()-7, D2:D9, ">"&TODAY())
This is how you count cells with multiple criteria in Excel. I hope you will find these examples helpful. Anyway, I thank you for reading and hope to see you on our blog next week!
2039 comments
Hi, thank you for explanation for the use of countif and countifs for range of dates using ">5/1/2014" for example.
But can you give explanation about how to use this if i need to use the date entered in another cell, not in the formula it self.
For example : I have entered "1/1/2024" in one cell and "31/1/2024" in another one (3 months range). How to use both cell to in the countif/countifs formula?
It will ease the effort to mess with the formula again. Thank you.
Hello Edward!
You can use logical operators such as ">", "<", "<=" or ">=" together with cell references in your Excel COUNTIFS formulas. Remember to enclose the operator in "double quotes" and add concatenation operator (&) before a cell reference.
Cells may contain dates or numbers. For example:
=COUNTIFS(C2:C9,">="&A1,C2:C9,"<="&"B1")
Very helpful answer! It already solved my problem. Thank you very much.
Hi sir, please help to solve the below formula:
=Countifs(S4:S545,"0")
Hi! I can't guess what your problem is with this formula.
Hello! Thank you so much for this information! It has proved to be so helpful.
I am trying to calculate how many items in a A3:A51 list have "true" marked in either F3:F51 OR G3:G51. Can I do this through a formula?
Hi! Please read the following paragraph in the article above very carefully: How to count cells with multiple criteria (OR logic). It covers your case completely.
In trying to calculate prorated salary based from 2 other columns "daily rate" x "# of days" between 2 date ranges, but only want them to be calculated if the Status = In Progress, On Hold or Not Started.
I am trying to calculate using =SUM([@[daily Rate calculation ]]*[@['# of days]],(COUNTIFS([Status],{"In Progress","On Hold","Not Started"}))), however it is picking up all other statuses also.
Hello Monique!
To find the sum based on multiple conditions, use SUMPRODUCT function. You can find the examples and detailed instructions here: Excel SUMPRODUCT function with multiple criteria. For example:
=SUMPRODUCT(A1:A10*B1:B10*((C1:C10="In Progress")+(C1:C10="On Hold")+(C1:C10="Not Started")))
Hi!!
I have a problem. How can I use countifs when I have to count- for example sales target achieved between 1% to 51% how many times by a particular manager from list of different managers.
Hi! Pay attention to the following paragraph of the article above: How to count numbers between 2 specified numbers. Add another condition to the proposed formula: the manager's name. For example:
=COUNTIFS(B2:B10,"Name",C2:C10,">1%", C2:C10,"<51%")
Hi, im currently counting rows tagged with "a" and "b". tags ranges from a - r. how to do itt? thank you!
Hi! To understand what you want to do, give an example of the source data and the expected result. Maybe this article will be helpful: Excel COUNTIF and COUNTIFS with OR logic.
Hi
I am trying to countifs between hours and minutes (00:00:00) but my formula is not bringing back all results when checked manually. I'm not sure where it is going wrong.
1st count between dates, 2nd count matching data in a colum, 3rd count counting between hours and minutes. It's bringing back some of the data but not all.
=COUNTIFS(Sheet1!$M:$M,">="&DATE(2024, 5, 1),Sheet1!$M:$M,"=00:00:00", Sheet3!$R:$R, "="&DATE(2024, 5, 1),Sheet1!$M:$M,"=00:15:00", Sheet3!$R:$R, "<=00:29:59")
1st row brings back all results(12), 2nd row brings back 0 results. When checked manually there should be 10 results.
Thanks
Craig
Seems like line of formula hasn't shown up properly.
=COUNTIFS(Sheet1!$M:$M,">="&DATE(2024, 5, 1),Sheet1!$M:$M,"=00:15:00", Sheet3!$R:$R, "<00:29:59")
This same line is used to count 0 - 15mins, 15 - 30mins, 30 - 45mins, 45 - 60mins, etc
Thanks,
Hello Craig!
To extract the time from the date, use the MINUTE function. Read more about it here: How to calculate time in Excel. Но вы не можете использовать это в формуле COUNTIFS. Therefore, to calculate values by condition, I recommend using the SUMPRODUCT formula with multiple criteria. For example:
=SUMPRODUCT((Sheet1!$M:$M>=DATE(2024,5,1)) * (MINUTE(Sheet1!$M:$M)=0)*(Sheet3!$R:$R=DATE(2024,5,1)) * (MINUTE(Sheet1!$M:$M)=15) * (MINUTE(Sheet3!$R:$R)<30))
shift department
Angulo, Ingrid N F2 Pick
Anon, Kobon Jean Francois S2 Pack
Badey, Spenser R F2 Pick
Baez, Jasmarie L S2 Pick
Bah, Houleymatou W2 Pick
Hi there!
Im trying to do the countif to return a value with how many people with specific shift are in certain department. For example, how many people with F2 shift are in pick department.
Hi! I hope you have studied the recommendations in the tutorial above. It contains answers to your question. Use the COUNTIFS function to calculate the number of records that meet the two conditions.
Really it was soo help ful.
May almighty God bless you all
I've been scratching my head at this for awhile now. I'm using the SUMIFS formula to add data based on 3 criteria; start date, end date, and a name. I have the formula working but im searching multiple columns for the name resulting in adding 15 identical SUMIFS formulas just to check different columns.
Example:
=COUNTIFS(Table1[[Date]:[Date]],">="&$F$2,Table1[[Date]:[Date]],"="&$F$2,Table1[[Date]:[Date]],"="&$F$2,Table1[[Date]:[Date]],"="&$F$2,Table1[[Date]:
[Date]],"="&$C$3,Table1[[Date]:[Date]],"<="&$D$3, Table1[[SL]:[SD]],$B6)
I have a similar issue for another cell which is identical with the SUMIFS function. Another challenge is that I have to stay in base excel formulas and can't go into MBA due to job security.
I've never posted on a forum before so you know I'm on my last leg trying to figure this out.
Seems some of my comment is missing. Im basically trying to make this function work to clean up code:
=COUNTIFS(Table1[[Date]:[Date]],">="&$C$3,Table1[[Date]:[Date]],"<="&$D$3, Table1[[SL]:[SD]],$B6)
I want the data in column A added if column B is within the date range and if the name is in ANY of the columns Table1[[SL]:[SD]]
Hi! If I understand your task correctly, the formula could be as follows:
=SUMPRODUCT((D2:G6=H1) * (B2:B6<I1) * (C2:C6>I1))
Column B - start date
Column C - end date
I1 - date
H1 - name
You can find the examples and detailed instructions here: Excel SUMPRODUCT function with multiple criteria.
That worked for counting how many times the name shows up between two dates. It helped shorten some other code, but not the original challenge.
I want to count the data in the blue columns
if the date(green) is within a range(pink)
and if a name(yellow) is in any of the orange columns.
.
I was able to do this with:
SUMIFS()+SUMIFS()+SUMIFS()+SUMIFS()+SUMIFS()... -- one for each of the POS columns.
.
I want to be able to write:
=SUMIFS(Table[AC], Table[Date], >date, Table[Date], date, Table[Date], date, Table[Date], date, Table[Date], date, Table[Date], date, Table[Date], <date, ***Table[POS5], Name***)
Important data in-between ***
I hope this helps. I haven't been able to find a solution for months.
Hi! If you study the SUMPRODUCT function manual carefully, you can modify the formula suggested to you. Based on the information provided, the formula could look like this:
=SUMPRODUCT((F2:I5=C10) * (B2:B5>=C8) * (B2:B5<=C9)*D2:D5)
=SUMPRODUCT((F2:I5=C10) * (B2:B5>=C8) * (B2:B5<=C9)*E2:E5)
How can are select only IF from among these criteria after selecting the range G1,G2,G3,G4, If
Sorry, I do not fully understand the task.
As it's currently written, it's hard to tell exactly what you're asking.
Hi Alexander - thank you so much for your great article and explanations!
I've successfully navigated the use of countifs for 3 criteria.... =COUNTIFS('Objectives'!$Q:$Q,$A$1,'Objectives'!$R:$R,B$1,'Objectives'!$M:$M,"Yes")
But to collect the next subset of data I'm wanting to add criteria 4 which counts if the value in a cell in one column is the same as the value in a cell in another column on the same row - is that possible? i.e. count if the value in column A = the value in column B
Hello! To count the number of values for multiple criteria, you can use the SUMPRODUCT function. For example:
=SUMPRODUCT((Q1:Q10=$A$1) * (R1:R10=$B$1) * (Q1:Q10=S1:S10))
You can find the examples and detailed instructions here: Excel SUMPRODUCT function with multiple criteria.
I want to tell excel to sum numbers from one cell if there is data but from another cell if there is no data (pulling actuals when they are known but estimates when actuals are not known). But I also want to pull it per a specific community.
Hi! If you didn't find the answer in the article above, explain the problem in more detail. Provide additional information to understand what you need.
Hi!
I want to count instances that meet two sets of requirements, and also exclude some results. I work with zoning plans, and we have a database in a separate application where we record all our incoming cases, and register some predefined variables. For example, we give remarks to zoning plans as either a "remark", "advice" or an "objection", and zoning plans follow a predefined route of "start", "hearing" and "resolution". There are different variants within each of these categories.
The variables for advice is:
advice
advice met
advice partially met
advice not met
advice met, case closed
advice partially met, case closed
advice not met, case closed
And for resolution is:
resoluted plan
resoluted plan with time limit
resoluted plan with delayed legal effect
So as an example, I want to count instances where our advice has been met or partially met in a resoluted zoning plan, but don't want to include instances where our advice has not been met.
I think I have managed to build a formula that checks a set of requirements in the source material in column A, to a set of OR requirements in column B, and returns the number of instances:
=SUM(COUNTIFS(Source!A:A;{"advice met";"advice partially met";"advice met, case closed";"advice partially met, case closed"};Source!B:B;{"resoluted plan"\"resoluted plan with time limit"\"resoluted plan with delayed legal effect"}))
My problem is that this formula is very long, and it needs to be manually updated if we for some reason want to change the variables or add new ones. So I have tried to put the predefined variables in a separate sheet and called it "Code". So in this example, all the advice variables are put into column A in "Code", and all the different resoluted plan variables are put into column B:
{=SUM(COUNTIFS(Source!A:A;Code!A:A;Source!B:B;Code!B:B))}
But this returns 0, as I believe it counts exclusive pairs, not OR.
I have thought about simplifying the first formula by using wildcards, i.e. {"adv*met";"adv*sed"} but this will also include results where advice has not been met. Also, I don't know if it's the correct use of asterisk.
Sorry for the long text. Any advice is appreciated!
Hi! For the COUNTIFS function, you must write all possible combinations of values in Code!A:B. Then your formula will work correctly. Or you need to use a VBA macro to get all possible combinations of values and calculate them.
Hi, i have data related to my job which i cannot provide. So , lets take a example with same scenario.
In the below data, i want the output as Yes, whenever the serial number is same for Mobile and Torch.
Items Serial number
Mobile 1234
Earphone 4567
Torch 4567
Charger 6789
Bottle 1234
Torch 6789
Charger 7894
Earphone 6789
Mobile 4567
Charger 1597
Bottle 7894
Torch 1234
Charger 7564
Hi! If I understand your task correctly, the following IF formula should work for you:
=IF(((A1="Mobile")+(A1="Torch")) * (COUNTIFS($A$1:$A$13,"Mobile",$B$1:$B$13,B1) + COUNTIFS($A$1:$A$13,"Torch",$B$1:$B$13,B1))>1,"Yes","")
Wow, that works. Thank you very much Alex
Hello- I am trying to use the COUNTIFS function to see if there is a multiple cell match in a separate data set. This data set is referenced as "1" in the below formula.
This is the formula I'm using: =IF(COUNTIFS([1.xls]Sheet1!$C$4:$C$39923, "=C4", [1.xls]Sheet1!$F$4:$F$39923, "=F4"), "TRUE", "FALSE")
It returns me with a "FALSE" tag on every single row even when I know it should be "TRUE". How can I fix this?
Thank you!
Hi! Please read the above article carefully. If you want to find matching values in cells, don't write references to those cells as text. Instead of "=C4", use C4.
How can use the same formula for sum
=COUNTIFS($A$2:$A$11, "Apples", $B$2:$B$11, ">200")
Have you tried the methods described in this blog post? If you are not satisfied, please let me know and I will try to help you.
Excel A B C D
1 Team Day 1 Day 2 Day 3
2 A Present Present Present
3 A Present Present Present
4 B Present Absent Present
5 B Present Present Present
6 C Present Present Present
7 C Present Present Absent
Question :
If want to calculate the number of days Team A present from Day 1 to Day 3
=COUNTIFS ( A2:A7,"A", B2:D7,"Present" ) Excel is not reading B2:D7 it only reads B2:B7 - I do not want to add multiple formulas because the number of days I have in the report is 50+
Hi! In the COUNTIFS formula, all ranges must be the same size. Therefore, to calculate the number of values by condition in a range of multiple rows and columns, try SUMPRODUCT formula. For example:
=SUMPRODUCT((B2:D7="Present")*(A2:A7="A"))
Thank you so much for this! This is helpful!
Hi,
I am trying to count the number of times a conditions is met in column B, for a date range specified in column A.
E.g.
Column A Column B
1/1/24 Yes
1/1/24 No
2/2/24 No
3/2/24 Yes
2/3/24 No
3/3/24 Yes
I want to count the number of times yes is recorded for a particular date range (per month).
Everything in column A is formatted as a date, Column B is as text.
Hi! Please re-check the article above since it covers your case. For an example of how the COUNTIF and COUNTIFS function counts values in a date range, see here. For example:
=COUNTIFS(A1:A10,">=1/1/2024",A1:A10,"<1/2/2024",B1:B10,"Yes")
Is this formula effected if the data being used is formatted in a table? I tried to simplify for the question, but will try to explain further below;
Monday, 1 July 2024 Yes
Tuesday, 2 July 2024 Yes
Tuesday, 2 July 2024 Yes
Saturday, 6 July 2024 Yes
Saturday, 6 July 2024 Yes
Saturday, 6 July 2024 Yes
Saturday, 6 July 2024 No
Sunday, 7 July 2024 Yes
Sunday, 7 July 2024 No
Sunday, 7 July 2024 Yes
Saturday, 13 July 2024 Yes
Saturday, 13 July 2024 No
Monday, 15 July 2024 Yes
Sunday, 14 July 2024 No
Saturday, 20 July 2024 No
Sunday, 21 July 2024 No
Sunday, 21 July 2024 Yes
Saturday, 27 July 2024 Yes
Friday, 2 August 2024 No
Saturday, 3 August 2024 No
Saturday, 3 August 2024 No
Sunday, 4 August 2024 Yes
Sunday, 4 August 2024 Yes
Saturday, 24 August 2024 No
Wednesday, 7 August 2024 No
Saturday, 17 August 2024 Yes
Saturday, 17 August 2024 Yes
Saturday, 17 August 2024 Yes
Sunday, 18 August 2024 Yes
Sunday, 18 August 2024 No
Sunday, 18 August 2024 Yes
Friday, 23 August 2024 No
Saturday, 31 August 2024 Yes
The data above is formatted as a table. dates column is "Bump in Date" & second Column is "Repeat Event"
Here is the exact formula I am using.
=COUNTIFS(Table134[Bump in Date],">=1/7/2024",Table134[Bump in Date],"<1/7/2024",Table134[Repeat Event],"Yes")
I keep getting a result of zero. A manual count of this data shows I should be getting a result of 6.
Ignore me, I got it to work. There was an error in the formula I had posted in my previous reply anyway.
Hi! Your formula can only return zero because you have specified the same date as the start and end of the time interval. Both conditions cannot be TRUE at the same time.
I have a table with three columns: Date, Color and Name. I am trying to calculate the number of people (i.e., distinct number of Names) for given Date and Color combinations. I thought I could do it using 'sum' and '1/countifs', and I tried the method in this article (and many other methods), but I seem to be missing something. The formulas I used are below. The correct values are:
2 distinct names for Red - 11/1/2006 (Bob and Dave)
3 distinct names for Red - 12/1/2006 (Chuck, Dave and Jim)
1 distinct name for Green - 11/1/2006 (Chuck)
1 distinct name for Green - 12/1/2006 (Jim)
Here are the correct results in table format:
Restated, here are the correct distinct number of Names by Date and Color
Red Green
11/1/2006 2 1
12/1/2006 3 1
But the formula I used provides clearly erroneous values (3, 6.5, 3 and 1.5).
Here are the table, the correct results, my formula's incorrect results, and the formulas.
Date Color Name Distinct Names by Date and Color
11/01/06 Green Chuck Correct Values: Red Green
11/01/06 Red Bob 11/1/2006 2 1
11/01/06 Red Bob 12/1/2006 3 1
11/01/06 Red Dave
12/01/06 Red Chuck Incorrect Results:
12/01/06 Red Chuck 11/1/2006 3 3
12/01/06 Green Jim 12/1/2006 6.5 1.5
12/01/06 Red Dave
12/01/06 Red Jim
12/01/06 Green Jim
Formulas in Cell:
11/06 Red (3): {=SUM(IFERROR(1/COUNTIFS($C$2:$C$11,$C$2:$C$11,$B$2:$B$11,F$2,$A$2:$A$11,$E3),0))}
12/06 Red (6.5) {=SUM(IFERROR(1/COUNTIFS($C$2:$C$11,$C$2:$C$11,$B$2:$B$11,F$2,$A$2:$A$11,$E4),0))}
11/06 Green (3): {=SUM(IFERROR(1/COUNTIFS($C$2:$C$11,$C$2:$C$11,$B$2:$B$11,G$2,$A$2:$A$11,$E3),0))}
12/06 Green (1.5): {=SUM(IFERROR(1/COUNTIFS($C$2:$C$11,$C$2:$C$11,$B$2:$B$11,G$2,$A$2:$A$11,$E4),0))}
Please let me know where I want astray.
Thanks much!
Hello! You want to calculate values for dates that are part of a string of text that appears in the Name column. The formula uses dates from the Date column. These dates do not always match the dates in the text in the Name column. To make sure that the count is correct, you want to extract the dates from the Name column into a separate column. Instead of using the data in column A, use this column in your formula.
To extract the second word from the text "Bob 11/1/2006" and get "11/1/2006", you can use these guidelines: Get last word from string.
I hope it’ll be helpful.