The tutorial explains how to use COUNTIFS and COUNTIF formulas with multiple criteria in Excel. You will find a number of examples for different data types  numbers, dates, text, wildcard characters, nonblank 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 the COUNTIF function is intended for counting cells based on a single condition in one range, while COUNTIFS allows using several criteria and ranges. In practice, however, you can use Excel COUNTIF with multiple criteria as well. So, the aim of this tutorial is to help you figure out the most efficient formula for each particular task.
Excel COUNTIFS function  syntax and usage
As I've already mentioned, the Excel COUNTIFS function is purposed for counting cells across multiple ranges based on one or several conditions. The COUNTIFS function is available in Excel 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
 defines the first range to which the first condition (criteria1
) shall be applied, required.criteria1
 sets the condition in the form of a number, cell reference, text string, expression or another Excel function, required. The criteria defines which cells shall be counted and can be expressed as 10, "<=32", A6, "sweets".[criteria_range2, criteria2]…
 these are additional ranges and their associated criteria, optional. 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. As well as with any other formula, 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!
1. You can use the COUNTIFS function in Excel to count cells in a single range by a single condition as well as in multiple ranges with multiple conditions.
2. Each additional range must have the same number of rows and columns as the first range (criteria_range1 argument).
3. Both contiguous and noncontiguous ranges are allowed.
4. If the criteria is a reference to an empty cell, the COUNTIFS function treats it as a zero value (0).
5. You can use the wildcard characters in criteria  asterisk (*) and question mark (?). See this example for full details.
For the detailed description and syntax of the COUNTIF function, please see this tutorial  Excel COUNTIF function.
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 handle multiple conditions and ranges.
COUNTIFS and COUNTIF (with multiple criteria) for numbers
By and large, COUNTIFS formulas for numbers fall into 2 categories  counting numbers between the two values you specify, and based on several conditions.
Example 1. Counting numbers between X and Y
You can count how many numbers are between the numbers you specify in two ways  using a COUNTIFS function or a difference between two COUNTIF functions.
COUNTIFS formula: =COUNTIFS(C2:C10,">5", C2:C10,"<10")
This formula counts how many numbers between 5 and 10 (not including 5 and 10) are contained in cells C2 through C10.
If you want to count cells based on the same condition but including 5 and 10, add "=" to the criteria like this: =COUNTIFS(B2:B10,">=5", B2:B10,"<=10")
COUNTIF formulas to count numbers between X and Y:
=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. The formula will produce the same result as you see 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.
Summing up, you use a combination of two COUNTIF functions in the following way. In the 1^{st} function, you count how many numbers are greater than your lower bound value (5 in this example). In the 2^{nd} function, you count how many numbers are greater than the upper bound value (10 in our case). The difference between the first and second number is the result you are looking for.
Example 2. Counting numbers with multiple criteria (AND logic)
Suppose you have a list of products indicating how many items are sold, delivered and in stock. You can use the following formulas to count the numbers of products, at least one item of which has been sold and delivered, i.e. if the values in columns B and D are greater than 0:
=COUNTIFS(B2:B11,">0", D2:D11,">0")
Here is one more COUNTIFS formula example with multiple ranges:
=COUNTIFS(B2:B11, 0, C2:C11, 0, D2:D11, 0)
The above formula counts the number of products with zeroes in columns B, C and D. This COUNTIFS formula returns 1 because only Product 5 have "0" in all the three columns.
=COUNTIFS(B2:D11,"=0")
would produce a different result because it returns the total count of cells containing a zero.Example 3. How to use cell references in COUNTIFS formulas
Of course, you can use a cell reference instead of a number in Excel COUNTIFS formulas. Just remember to enclose the operator in quotes ("") and add an ampersand (&) before the cell reference to construct a text string. For more information about the use of an ampersand in COUNTIF and COUNTIFS formulas, please see Excel COUNTIF  frequently asked questions.
So, let's make a COUNTIFS formula with a cell reference equivalent to the formula from the previous example:
=COUNTIFS(B2:B11,"="&C2, C2:C11,"="&C2, D2:D11,"="&C2)
Cell C2 used in the criteria contains a zero value, so the formula will produce exactly the same result you see in the screenshot above.
Example 4. COUNTIF formulas with OR logic
Examples 2 and 3 above demonstrate how to use COUNTIFS in Excel to count cells based on several conditions with the AND logic. Such formulas can be applied both to contiguous or noncontiguous ranges. For example, we have used this formula =COUNTIFS(B2:B11,">0", D2:D11,">0")
to find out how many products have a value greater than "0" both in column B and column D.
But what if you want a total count of cells with a certain value in several nonadjacent ranges or when at least one of the conditions is met (like OR operator)? For instance, how do you count the total number of zero values in columns B and D? The answer is to use a combination, more precisely, a sum of several COUNTIF functions:
=COUNTIF(B2:B11,"=0") + COUNTIF(D2:D11,"=0")
In a similar manner, you can add up several COUNTIFS functions if you want to count something with the OR logic. For example, the following formula counts the number of rows that have either "Product1" or "Product2" in column A and 0 in column B:
=COUNTIFS(A2:A11,"Product1", B2:B11, 0) + COUNTIFS(A2:A11,"Product2", B2:B11, 0)
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 1Jun2014 and 7Jun2014, 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 20^{th} of May and delivered after the 1^{st} 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())
COUNTIFS formulas for text values
In the previous article, I provided an example of Excel COUNTIF formula with two criteria to count the number of cells with 2 different text values in a single range. Now let's see how you can use the COUNTIFS function with multiple criteria and across multiple ranges.
Example 1. COUNTIFS for text values
Suppose, you have a table listing the results of the credits and exams passed by students. What you want to know is how many students have passed all the credits. To do this, you use a usual COUNTIFS formula with several different ranges and the same criteria:
=COUNTIFS(B2:B7,"=P",C2:C7,"=P",D2:D7,"=P",E2:E7,"=P")
If you want to know how many credits a particular student has passed or not passed, you can utilize either a COUNTIFS or COUNTIF formula, because this time you need a single range only.
For example, the below formulas will tell you how many credits Bella has passed:
=COUNTIFS(B3:E3,"=P")
=COUNTIF(B3:E3,"=P")
Example 2. COUNTIFS with wildcard characters
As I mentioned at the beginning of this tutorial, you can use the following wildcard characters in Excel COUNTIFS formulas:
 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.
Now let's see how you can use a wildcard char in your real COUNTIFS formulas in Excel. Suppose, you a list of projects assigned to your company's employees. You want to find how many projects are already assigned to someone, i.e. any name is stated in column A. And because we are leaning 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 criteria with the second range because you have dates rather that text values in column D. That is why, you use the criteria that finds nonblank cells  "<>"&""
Can COUNTIFS can be used to find the number of matches within the same range?
I have a range of 500 postal codes in one column. These are formatted in two groups of three characters each (i.e. A9A 9A9). The first group of characters will change slightly to represent a different region  A9B, A9C, A9D, etc. I would like to get a count of each regional group.
I could do this over and over again (as I've already done) as follows:
=COUNTIF(M1:M500, "A9A*")
This proved to be very repetitive to copy, paste and modify this 25 times for each of the regional postal code categories I'm working with. Can COUNTIFS do this at one time?
Hello Michael,
Regrettably, COUNTIFS cannot help in your case because it counts cells that meet ALL of the criteria you specify in the formula. You can achieve the desired result in this way:
Create an additional column and copy the following formula there =LEFT(A2,3) where A is your postal codes column. This formula will extrat the first 3 characters of the codes.
Then you can proceed in 2 ways.
Way 1:
 Sort your table by the postal code column or the newly created column with the above formula.
 Apply subtotal to the table (Data >Outline >Subtotal) with these settings:
At each change in : Column with the formula
Use function: Count
Add subtotal to: Column with the formula
 Click Ok.
 Then Press Number 2 at the left side of you sheet and you will get the count of codes by region.
For more info about using subtotals please see this article  Using Subtotals in Microsoft Excel
Way 2:
 Insert a pivot table (select your table and go to Insert > Tables > PivotTable).
 Place your pivot table onto a new sheet.
 Drag and drop the column with the formula to the Rows section and the original column with the postal codes to the Values section.
You will get the same count of codes by region.
THANKS VERY MUCH ITS VERY USEFUL FOR ME
Thank you. I just spoke to a colleague who walked me though a pivot table of my data. Thanks again.
Hello,
I have a list of classes with the day and start & end times in one spreadsheet.
The format looks like this:
M 8:00 AM  9:50 AM
T 12:30 PM  2:20 PM
M 9:00 AM  10:15 AM
F 7:00 AM  1:00 PM
In another spreadsheet i have a table like this one below. I want to know how many classes occur on Mondays between 6:00 am and 6:29 am,6:30 am  6:59 am,7:00 am  7:29 am, etc.
M T W R F
6:00 AM
6:30 AM
7:00 AM
7:30 AM
8:00 AM
8:30 AM
9:00 AM
9:30 AM
10:00 AM
10:30 AM
11:00 AM
11:30 AM
12:00 PM
12:30 PM
1:00 PM
1:30 PM
2:00 PM
2:30 PM
3:00 PM
3:30 PM
4:00 PM
4:30 PM
5:00 PM
5:30 PM
6:00 PM
6:30 PM
7:00 PM
7:30 PM
8:00 PM
8:30 PM
9:00 PM
9:30 PM
10:00 PM
10:30 PM
11:00 PM
11:30 PM
12:00 AM
I appreciate your help tremendously!
Thank you!
oops, it's not pasting like i want it to. basically, each day is a column, each time is a row. sorry for the confusion.
Hi Emily,
Can you please send me your sample workbook at support@ablebits.com and an example of the result you want to get? I believe in this way it will be easier for us to suggest a formula that returns exactly the result you are looking for.
Hi Svetlana,
I just sent the sample workbook. Please let me know if you received it.
Thank you!!
Emily
Hi Emily,
Yes, our support team passed me the workbook this morning and I emailed you the result a few minutes ago. Hopefully, the formulas work as you expected :)
Hi Svetlana,
I think I have similar problem.
Would you please publish the solution for use the time as the cretria, something like
"> 12:00 PM" and "<1:00 PM"
Regards
what is deference between countif and countifs
COUNTIF is intended for counting cells based on a single condition, while COUNTIFS allows specifying several criteria in several different ranges.
Hi Svetlana ,
I 've send sample data Plz help me for get formula
Ma'am,
I want to how many HBL availbale in sheet against city
Exmp
CITY COUMN A COLUMN B
ALIGARH HBL HBL
ALIGARH HBL HBL.
ALIGARH HBL HBL
ETAH HBL HBL
ALIGARH HBL HBL
ALIGARH HBL HBL
Ma'am,
I want to how many HBL availbale in sheet against city
Exmp
CITY COUMN A COLUMN B
ALIGARH HBL HBL
ALIGARH HBL HBL.
ALIGARH HBL HBL
ETAH HBL HBL
ETAH HBL HBL
ETAH HBL HBL
formate
cluster name HBL QTY REQUIRED
ALIGARH
ETAH
Trying to find a formula that will average only if EACH cell has a value greater than 0 in it; or if the last 3 consecutive months have a value greater than 0. Any help is greatly appreciated.
I believe, you can use a formula similar to this:
=IF(AND(E2>0,F2>0,G2>0),AVERAGE(A2:E2),"") where E, F and G are the last 3 months' values. You can add more > operators to the nested AND function if you want to check more than 3 months.
Hi,
how can i automatically count or computed in Monday to Sunday data base count as week summary
Hi Rodz,
Sorry, I cannot follow you. Could you elaborate on the task a bit more, please?
Column G is "Barcodes". Column J is "Carriers". How can it get the Excel to display "Flexsteel Truck or FedEx or UPS" in column J:J whenever a BX number (for example..BX09225)is enter in G:G?
Thanks in advance,
Glenn
Hi Glenn,
If you want to use only formulas, then enter this one in cell J2:
=IF(And(G2<>"", len(G2)>2,left(G2,2)="BX"),"Flexsteel Truck or FedEx or UPS", "")
Then copy the formula across other cells in column J.
If you want to insert some values in column J, but have these values replaced with "Flexsteel Truck or FedEx or UPS", or something like that, as soon as the code appears in column G, you need a special VBA macro to fulfill this.
Hi,
I have a query countiff not working. I have a project allocation sheet where column A is resources and B, C, D, E ....are daily dates. I have to allocate project to a resource in column A in other columns by selecting a drop down I have created in Column B onwards. Now I want a summary that how many resources are in a project end of month.
Suppose 5 resources were allocated Bug fixing project through out the month the formula would now count resources for me it counts the allocations i.e. number of times I allocated bug fixing to resources
Please help
Help please.
3 columns. Column A is a number (Day), Column B & C are time.
Count rows where day number is less than 6 and times in either B or C are between 5:00 & 7:00

A B C
D T0001 T0002
1 6:14
1 6:57
1 6:58
1 20:50
1 23:05
2 17:47
2 17:59
3 6:01
3 17:46
5 18:20
6 7:06
6 11:50
6 13:29
6 13:53
6 14:57
6 17:36
7 10:26
7 10:32
Column B & C data not pasted very well. Just put every second time in column c.
Hello Kees,
I believe the following formula will work a treat:
=COUNTIFS(A2:A10,"<6",B2:B10,">="&TIME(5,0,0),B2:B10,"<="&TIME(7,0,0))+COUNTIFS(A2:A10,"<6",C2:C10,">="&TIME(5,0,0),C2:C10,"<="&TIME(7,0,0))
In your case, the result will be 4.
Hi Svetlana I sent you an email for your assistance could you be so kind to help me? thanks Jason
Hi Jason,
Have just emailed you the formula, hopefully this is the result you are looking for.
Sex Age
F 14
F 19
F 15
F 14
F 16
F 21
F 24
how can i count with these limitations? what formula/syntax will i use?
i need to count all F with age less than 15, F equal to 15 but not more than 19 and all the F equal 20 but not more than 24
I want to tabulate it in this format.
F less than 15
F equal 15 pero not more than 19
F equal 20 but not more than 24
thanks for helping me on this one
Hi Gio,
Try these formulas:
F is less than 15:
=COUNTIFS($A$2:$A$8,"F",$B$2:$B$8,"<15")
F equal 15 pero not more than 19:
=COUNTIFS($A$2:$A$8,"F",$B$2:$B$8,">=15",$B$2:$B$8,"<19")
F equal 20 but not more than 24:
=COUNTIFS($A$2:$A$8,"F",$B$2:$B$8,">=20",$B$2:$B$8,"<24")
Hi Svetlana Cheusheva , Your solution is helping me , Thanks
I am trying to add a value for a Alpha value in a cell. The Cell can have an M=5, a C=3 or a Z=1 so in the same cell I want to show a value of the corresponding letter, with a default of "0" if they do not choose either letter.
Also I am adding another value in a subsequent cell for "C" a value of 1,2,3 will be used as as a negative of 1=0.5, 2=1 and 3=1.5, same values only apply to a Z 4=.5, 5=1, and 6=1.5.
Thanks for your help in advance...
Hi Brad,
Sorry, I'm not sure I can follow you. If you can send me your sample workbook at support@ablebits.com, I will try to help.
Dear Svetlana Cheusheva,
I appreciate your help tremendously!
can you inform that,
A
B
C
A
D
H
A
J
K
L
A
U
above there if i use the formula =COUNTIF($F$8:$F$30,"A") than result is 3
but i wants to know that, How Would I Count Only The Same Name Once/ Count Repeated Items Once.
Please help me urgently.
Thanking you,
Imam Azad
Macro Cable Ltd
Dhaka, Bangladesh
Hello Imam,
If you want to get the list of unique values, you can copy your list to a new sheet and click on the Remove Duplicates icon under the DATA tab.
If you want to get something different, please describe your task in more details.
Dear Svetlana Cheusheva,
Please solve my example: Mr. Karim is market visited statement as below
Date Code Name Type of Customers
10 October 2014 100 ABC Traders Traders
11 October 2014 101 MNZ Traders Traders
12 October 2014 201 AKA Builders Developers
13 October 2014 300 PWD Consultant
14 October 2014 100 ABC Traders Traders
15 October 2014 300 PWD Consultant
16 October 2014 201 AKA Builders Developers
16 October 2014 103 ABD Traders Traders
17 October 2014 100 ABC Traders Traders
17 October 2014 101 MNZ Traders Traders
19 October 2014 104 PQS Traders Traders
20 October 2014 100 ABC Traders Traders
Now I want to report/result:
1. How many times visited there? Ans: 12 times. it's i can do.
2. How many customers are in visited there?
Please forward formula how to solve.
Thanking you,
Imam Azad
Macro Cable Ltd
Dhaka, Bangladesh
Dear Svetlana Cheusheva,
Please help any update.
Thanking you
Imam
Dhaka
ANY UPDATE
Please be Reply
IMAM
Hello Svetlana,
I noticed that in your tutorial that COUNTIF formulas are used outside the columns/cells that need to be totalled.
I have a work sheet with a number of columns, one of which is Sales/Purchases. The data in the work sheet is extensive and the columns are very long so I like to keep track of the number of Sales and Purchases for a month by using =COUNTIF(range,"SALE")and the same for Purchases. If I place the formulas in a cell at the top or bottom of this column everything works fine. However, should I place the formulas outside this column all I get is a date (such as January 1900). Am I doing something wrong or is the formula restricted to its specific column?
Thank you in advance for any help.
Cheers,
vcoolio.
Hello Vcoolio,
You can put your formulas in any cells. For the formulas to work correctly, try the following:
1 Apply the General format to the cell with the formula (press Ctrl + 1 and select General in the Format Cells dialog window).
2 If you used relative cell references in the range argument, the references got distorted when you copied the formula. Try using the absolute cell references instead (e.g. $B:$B for column B).
If the above suggestions do not work, please send me a sample workbook at support@ablebits.com and we will try to figure out the source of the problem.
Dear Svetlana,
Thanks for that. I've sorted it out. Someone had been clearing cells by pressing the space bar and obviously a space is still a character so this was distorting any new entries such as formulas. I've made a note of your resolutions above for future reference. Excellent stuff!
Your help is much appreciated (and is worth a vodka or two!!).
Cheers,
vcoolio.
Thanks Vcoolio, I prefer Baileys : )
Hi, I have a range of cells I want to count for a skills matrix for my staff. The ranges of what I want counted are drop down menus in each of the cells with "Sound", "Advanced", "Expert" etc, and these I wanted counted in the number of times that the cells have one of those words in them. The other cells contain "UnAware" and "Basic" which I dont want counted in the original total, but put in a total for all the types of details entered, this way I will get a % of staff with the desired level of training/competence in the processes they use every day.
I tried COUNTIFS but I kept getting a zero count even though I used the formula correctly (no error message). Could you please help me work this out?
Hi Brandon,
I am sorry it's difficult for me to say why this happens without seeing your data. Please send your sample workbook at support@ablebits.com and we'll try to help.
YES!! This is exactly what I am trying to do, too  and I am getting the same nonerror of a zero result. What is the proper way to formulate this?
Hi Svetlana,
Could you be kind enough to help me out on this excel problem I am trying to refer to a particular cell for formatting but there is a conflict. I will appreciate your prompt response.
I have sent the file to your mail.
Thanks
Hi!
In your case, it probably makes sense to look up by 2 columns C and D using an array INDEX / MATCH formula. I've sent you an email with a formula example, hopefully that is the result you are trying to achieve.
Can I use countifs with more to count more than three ranges and criteria?
Hi Angela,
Of course, you can. You can actually use up to 127 dirrerent ranges / criteria in one COUNTIFS formula: )
Hi Svetlana,
How can I count two different texts in one column?
So my usual formula is: =COUNTIFS(C:C,"Passed",D:D,"Quebec")
And its giving my correct figures.
But if in the column C, i also want to count any other status that says Remediation Passed, how should I say that? No change in column D. I tried something like, COUNTIFS(C:C,"Passed" OR "Remediation Passed",D:D,"Quebec"), but that didn't work.
Grateful for your insights.
Cheers
Hi Svetlana,
I was trying to follow your tutorial and adapt it to my data, but it didn't seem to work. Would you mind helping me troubleshoot please? :)
I was trying to create a COUNTIF for instances when there's text in column A AND text in column C OR column D.
Thanks in advance!
Hiya
I have a workbook with mulitple sheets and an overview sheet.
The overview has validation list for different months whicg relates to each of the muliple sheets.
I need a count if where by the sheet name in the range will change depending on the month selected in the validation
i.e If 'May14' in cell 'Overview!'C2  Countif('May14'!R:R,Criteria)
If then in overview cell2 it is changed to Jun14 the Countif will change to Countif('Jun14'!R:R,Criteria).
Please help?!
Hi
I have two columns named as Reportable (with Yes or No) and another column named Responsible_Officer.
I am trying to do a number count for each yes per each individual responsible officer.
Hi,
Help needed on the formula for getting the desired count as shown in the table.
Need to get count as 1 when either or both the parts are 100% completed.
Name Part Status Desired Count
A LHS 100.0% 1
A RHS 100.0%
B LHS 100.0% 1
B RHS
C LHS 1
C RHS 100.0%
D LHS 0
D RHS
Thanks a lot in advance.
I am trying to tally the number of calls by dayoftheweek each person has from a table that looks like this but for the entire year:
JANUARY Sa Su Mo Tu We Th Fr
James call
John call call
Mike call
Jane call call
FEBRUARY Sa Su Mo Tu We Th Fr
James call call
John call call
Mike call
Jane call call
MARCH Sa Su Mo Tu We Th Fr
James call
John call call
Mike call call
Jane call
I have a table at the top of the spreadsheet that shows the results and looks like this:
Sa call Su call Mo call etc
James ?? ?? ??
John ?? ?? ??
Mike ?? ?? ??
Jane ?? ?? ??
What formula do I use that references the names in the left column with the rows to give me the ?? tallies based on day of the week when they occur? Thanks.
How do I use countif to meet the same criteria but across multiple ranges in multiple worksheets?
Hi Svetlana,
this is my problem
A1 B1 C1
6:00  14:00 14:00  20:00 20:00  6:00
A2 = B2 = C2 =
A3 = B3 = C3 =
for example, i have a value in different excel sheet.
if i enter 8:00 am there, it will be automatically appear in A2.
B2 and C2 will be blank.
but if i put the value 15:00 pm, it will be automatically appear in B2. A2 and C2 will be blank.
Hope you can help me with this.. Thanks
OR
A1 is 6:00 AM
B1 is 14:00 PM
A2 is 15:00 PM
B2 =A2 (This should not appear in the range of A1B1) it will leave blank
First Column 2nd column
List =LST what is the formula of getting a shortcut e.g i type
Cable List next column will automatic appear LST and so and so fort
Wire Thanks awaiting your prompt response.
Hi,
How can I count if criteria is in a list?
For Example: instead of =COUNTIF(Sursa!$G:$G,"Answered")+COUNTIF(Sursa!$G:$G,"Blind Transfered")+COUNTIF(Sursa!$G:$G,"RONA")
to have something as countif(Sursa!$G:$G,"Answered" or "Blind Transfered" or "RONA")
Thank you.
I found the answer for: COUNTIF(Sursa!$G:$G,"Answered")+COUNTIF(Sursa!$G:$G,"Blind Transfered")+COUNTIF(Sursa!$G:$G,"RONA")
instead of it, the formula is:
SUM(COUNTIF(Sursa!$G:$G,{"Answered","Blind Transfered","RONA"}))
for those who will have this problem in the future.
Great combination Kolegu. Thanks a lot, really helpful
pls av been trying to formulate a formula for my work and I need ur help. am dealing with different index nos and there various transactions whether credit or debit. I need a formula that can help me count de individual no of credit and debit for each index no and also sum the various credit and debit amount.
Hello Linda,
I am sorry, it is difficult to suggest something without seeing your data. If you can send a workbook with your sample data at support@ablebits.com, I will try to help.
Hi.
I'm trying to count the number of times a specific reason code (27 codes in total) occurs within a specified date range. I was able to use the COUNTIFS statement to identify the date range, but I can't figure out how to add the second part of the equation to the formula  that of now counting how many times each of the 27 codes occurs within the date range. The formula I'm using is:
=COUNTIFS($A$17:$A$50,">="&$R$30,$A$17:$A$50,"<"&$R$31). Any suggestions as to how I can add the next part of the statement?
Thanks for your help!
I
Hello Jane,
Just add one more Range/Criteria pair to your formula: =COUNTIFS($A$17:$A$50,">="&$R$30,$A$17:$A$50,"<"&$R$31, $B$17:$B$50,$R$32)
Where $B$17:$B$50 – the cells that contain the reason code, and $R$32 is the cell with the code you want to count.
Hi, I am having a country, site and subject number column and visit 1 to visit 62 columns with the date information within an excel spread sheet. I would like to calculate
 count of subjects per country per site per visit within the specific date range
 count of subjects per country per visit within the specific date range
As I am having a huge amount of data I would prefer to avoid the manual work which will take a lot of time to find out this information. Can you please help me?
Hi!
Please send us a sample of your data at support@ablebits.com and include the resulting report you want to get, and will try to help.
hi Mam,
I sent you an email, hope you can help me with my problem.
Thanks,
Angelo
Hi Angelo,
Our support team responded to you, hopefully the response was helpful.
I am working on a spreadsheet with a basic countif formula thats working well for me. When I add a filter to the worksheet in column A and filter by one item (e.g Apple) the formula does not autmoatically updated. Please help.
hi Svetlana,
now I'm working with Windows 7
here are the data in A1:E6, now im using array to count them by each character:
89 89 89 89 9
35 35 35 35 47
39 39 39 39 48
356 356 356 356 46
3589 3589 3589 3589 3459
1 1 1 1 12
{=SUM(LEN(A$1:E$6)LEN(SUBSTITUTE(A$1:E$6;B9;"")))/LEN(B9)}
and the reault will be:
1 2
5 6
9 8
14 9
0 0
17 3
13 5
4 4
5 1
1 7
is it possibble to sort them from smallest to biggest instead in one formula? so the result will be..
0 0
1 2
1 7
4 4
5 6
5 1
9 8
13 5
14 9
17 3
thank you for your help..
Hello Raja,
The value in cell B9 is missing. If you can send us the complete workbook with your data, we'll look into the issue. However, there is a very slim chance that your data can be sorted using a formula.
Oke I will..working on it..thanks
Hi,
In a coloumn, there are 4500 cells that contains values like 20000,1000,100,200,500,1500 etc so i want to count there are how many 100s,200s,300s,100s etc but if i use "COUNTIF" formula i have to give every time its very time taking.
So plz tell me formula that gives the count there are how many 100s,200s etc are there
Hi Subbu,
 Insert a pivot table (select your column and go to Insert > Tables > PivotTable).
 Place the pivot table onto a new sheet.
 Drag and drop your column from "Choose fields to add to report" to the Rows section
 Drag and drop your column from "Choose fields to add to report" to the Values section.
 Click on the column name in the Values section and select "Value field settings" from the dropdown menu.
 Select "count" instead of "sum" and click OK.
If you want to update information, rightclick on the pivot table report and select "Refresh" from the context menu.
Hi,
I'm creating a spread sheet to work with attendance numbers for an after school program. I'm using this formula for each grade.
=COUNTIFS(DailyAtten!C:C,A6,DailyAtten!E:E,"1")
I want to copy the formula across a spread sheet so that it will calculate daily attendance for each class. To do this, I need to be able to get the row to flow like this:
=COUNTIFS(DailyAtten!C:C,A6,DailyAtten!E:E,"1")
=COUNTIFS(DailyAtten!C:C,A6,DailyAtten!F:F,"1")
=COUNTIFS(DailyAtten!C:C,A6,DailyAtten!G:G,"1")
=COUNTIFS(DailyAtten!C:C,A6,DailyAtten!H:H,"1")
=COUNTIFS(DailyAtten!C:C,A6,DailyAtten!I:I,"1")
But instead, it copies like this:
=COUNTIFS(DailyAtten!C:C,A6,DailyAtten!E:E,"1")
=COUNTIFS(DailyAtten!D:D,B6,DailyAtten!F:F,"1")
=COUNTIFS(DailyAtten!E:E,C6,DailyAtten!G:G,"1")
=COUNTIFS(DailyAtten!F:F,D6,DailyAtten!H:H,"1")
=COUNTIFS(DailyAtten!G:G,E6,DailyAtten!I:I,"1")
Is there a way for me to set this up so that only the DailyAtten! value progresses when I continue the formula?
Thank you!
Hello Lindsey,
Just enter A6 as an absolute reference  $A$6  and Excel won't change the address when you copy the formulas to other cells.
For more details, please see:
https://www.ablebits.com/officeaddinsblog/2014/08/07/relativeabsolutecellreferencesexcelconditionalformatting/
Hello,
=COUNTIFS('Current Enlisted By Name'!$E:$E,LEFT(B6,4)&"*",'Current Enlisted By Name'!$D:$D,"WHQK"&"*")
I need that formula above to capture these UICs only:
WHQKA0,WHQKB0,WHQKC0,WHQKE0,WHQKT0,WHQLC0
Please help
Hello Rich,
This can be done either with a helper column or VBA.
If adding a helper column is acceptable, say column K and copy the following formula there, starting from row 2:
=OR(D2="WHQKA0",D2="WHQKB0",D2="WHQKC0",D2="WHQKE0",D2="WHQKT0",D2="WHQLC0")
Your main formula will be:
=COUNTIFS('Current Enlisted By Name'!$E:$E,LEFT(B6,4)&"*",'Current Enlisted By Name'!$K:$K,TRUE)
I have tried using the same multiple if's with a table that has multiple data validation lists. The user simply picks under the source column where the lead came from and then in the type column they would pick what type it came in as.
My formula is =countif(Table1[Source],"Kijiji",Table1[Type],"phone") to find out what percentage of leads were sourced from kijiji by phone as opposed to kijiji by email or our website by phone.
Not sure what I am doing wrong because it keeps throwing it out saying it is causing arguments.
Any help would be appreciated.
Hi Jason,
You were almost there. It just should be COUNTIFS rather than COUNTIF because you count by 2 conditions:
=COUNTIFS(Table1[Source],”Kijiji”,Table1[Type],”phone”)
I'm working from a spreadsheet and pulling data from the spreadsheet into a table on a new worksheet. I need to countif the data from A2:A114 is greater than 12/31/2013 and less than 4/1/2014 to obtain a count for Q1. I need to do this for each Quarter and change the dates which is working fine.
For example: =COUNTIF('Partnership Tracking '!$C$2:'Partnership Tracking '!$C$114,">12/31/2013")COUNTIF('Partnership Tracking '!$C$2:'Partnership Tracking '!$C$114,">3/31/2014")
Now I need to know of the data returned from the above formula which of those have Cleared using values in columns W2:w114 and the word "cleared". I tried this one and it is not yielding a realistic number.
Getting error:
=COUNTIF('Partnership Tracking '!$C$2:'Partnership Tracking '!$C$114,">12/31/2013")*COUNTIF('Partnership Tracking '!$C$2:'Partnership Tracking '!$C$114,"<4/1/2014")
Is it possible for me to achieve what I need?
Thanks
Monique
Hello Monique,
If I understand the task right, the following formula will work a treat:
=COUNTIFS('Partnership Tracking '!$C$2:'Partnership Tracking '!$C$114,">12/31/2013",'Partnership Tracking '!$C$2:'Partnership Tracking '!$C$114,">3/31/2014", 'Partnership Tracking '!$W$2:'Partnership Tracking '!$W$114,"cleared")
If it doesn't, you can send us your sample workbook at support@ablebits.com and we will try to help.
Svetlana and team – I enjoyed reading your blog about the “COUNTIFS” function, but am having problems using it. I have a worksheet that I want to count the number of unduplicated rows with two different criteria. I want to count the number of completed actions by analyst, but only count each class code once. The class code is in column “G”, the analyst (there are five different analysts) is in column “C”, and the analysis/recommendation is in column “AF”. When an analysis is completed for a particular class code, the column “AF” has some text. If the analysis is not complete, “AF” is empty.
I can successfully count the number of unduplicated class codes in my worksheet, by using:
=SUM(IF(FREQUENCY(MATCH(G2:G7497,G2:G7497,0),MATCH(G2:G7497,G2:G7497,0))>0,1))
My worksheet has 7,495 rows of duplicated class codes and 3.666 rows of unduplicated class codes.
I can successfully count the number of analyses completed by analyst, by using:
=COUNTIFS(C:C,"LF",(AF:AF),"")
What I’m trying to do, but can’t figure out is how to use the COUNTIFS function and count the UNDUPLICATED number of analyses completed.
Can you help?
Hello Lorna,
You can use the following array formula, please don't forget to press Ctrl + Shift + Enter to complete it:
{=SUM((IF(FREQUENCY(MATCH(G2:G7497,G2:G127,0),MATCH(G2:G7497,G2:G7497,0))>0,1,0))*((AF2:AF7498<>"")))}
But I'd recommend adding a helper column and copying the class codes there according to your conditions, e.g. here is the formula for row 2:
=if(AF2<>"", G2, "")
Then copy this formula down to the other cells in the helper column and count using your first formula. This seems to be a quicker and more flexible approach.
Svetlana  it WORKED! Many many thanks from California! Where do I send the Bailey's????
Thank you, Lorna. A virtual bottle will suffice : )
I am trying to take 2 tables  Galaxy Theatre and Empire Theatre. Each table contains a list of the theatres and the gendre of movie playing in the theatre. I need to count the number of horror movies playing in each theatre (provide a total for each theatre) using one equation. Is this possible?
The question suggests using an indrect to help, but I cannot get the Indirect function to work with countif.
Hello Nancy,
You can use the following formula to sum the results:
=CountIf(GalaxyTheatre[Genre],"Horror") + CountIf(EmprireTheatre[Genre],"Horror")
I need to track escalation in certain readings.
For example, I have 2 Columns, A and B; I need to count how many times column B is greater than column A when column B is greater or equal to 10.
Sample Data:
A B
5 8
10 15
14 11
18 22
9 10
From above, the answer/count should be 3
Thanks
Your help is great appreciated
You can use the following array formula (remember to press Ctrl + Shift + Enter):
{=SUM((B2:B6>=10)*(B2:B6>A2:A6))}
Thanks a world.
You're a live saver.
Hello Svetlana!
Good day!
Please help me, I'm a begginer on Excel. I have an exam today that may include below; (see data and scenario below)
(Scenario: Need to count the total no of landline nos, I use =COUNTIFS(DATA!D5:D135,">1") formula however it only gives me 20 total counts, where there should be a total of 21 inlcuding 3401031 LOC. 213. What formula should I use then? Please :) )
DATA:
LANDLINE NO.
325163952
2691919
4222239
323445488
2623224
324161040
2314987
4898389
2545410
2735652
2358818
4206340
2382144
4149143
4104661
3463201
2366113
322731025
2694647
3401031 LOC. 213
2339450
I am working with multiple workbooks, about 30. Each workbook has the names of associates that performed different tasks in it. I am trying to count how many times each persons name is used in all the workbooks.
(Ex. Workbook 1  Jane is used twice, Bill three times. Workbook 2  Jane is used 4 times, Bill 1 time. Jane's total would 6 and Bill's would be 4 within two workbooks. Can the COUNTIF or COUNTIFS functions acomplish this in an easy way?
Thanks!
I'm trying to calculate how many times a particular staff is scheduled to work during the week. This staff can be assigned to work in a different location so she can be listed in a column (to specify the day of the week) and rows are the location. The only problem is that I would only be needing to add two to five cells in a column, then another few cells from another column. How would I need to indicate that I'm adding another command?
Hi,
I have a project involving several varying length lists of email addresses which are overlapping, meaning there are duplicate values between the various lists. Additionally, the list order from left to right is important. Each list is an adjacent column in a single worksheet.
I would like to be able to identify (highlight via conditional formatting) the duplicate values in each successive list (column) occuring in the preceding list(s) to the left of column I'm evaluating, working my way to the nth column all the way to the right in my worksheet. For example, evaluating column B, highlight all cells where there is a duplicate value somewhere in column A. Evaluating column C, highlight all cells where there is a duplicate value anywhere in columns A or B. And so on up to my last column (20) which will be evaluated for matching values anywhere in the other 19 preceding columns to the left. Note that there are no duplicate values within any single list.
Is there a formula I can use to achieve this goal? Thanks for any suggestions you can offer!
Andrew
Hi ,
By having the Alphabets (AZ) & numbers consider from (09) need the combination of these only with 2 characters alone eg:
AA
AB
AC....
BA...
A1
A2...
Could you please give an excel formulae or as a vba macro code to display in an excel.
Please drop me an email.
Thanks,
Rajamani
Hi,
I want the cell range in =COUNTIFS to be variable, ie. it might be b4:b60 on one occasion, then with a different set of data it might be b4:b70.
Can the COUNTIFs formula pull the range in from another cell if the range is entered there?
Thanks,
Bernie
Hi, is it possible to count B4:D200 (multiple column) and a column of uncategorized age to be counted in a column of categorized age?
thanks,
Leo
Hi Leo,
Sorry, I am not sure I can follow you. If you can post a sample of your data, I will try to help.
I am trying to create a formula that will give the total number of cells within a range that contain a letter. The formula I currently have is: =COUNTIFS(C4:AC4,"=L",C4:AC4,"=F",C4:AC4,"=T"), however it is ot correct because within this range of cells three of them contain each a letter which are the ones in the formula and my total shows as 0 instead of 3.
What should I change to get it corrected or would it be possible to even have such formula?
Hello,
I have an excel file with 2 worksheets: the first worksheet contains 10 columns and 10 rows with random values (A, B, C, D, E).
I need a formula in the second worksheet to count the number of times that value "B" appears after value "A" in the first worksheet.
It's really important and urgent, if you can answer this you would save me a big headache.
Thank you
Hi,
I am counting up unique string values in a range of cells in a column. Tow of the values are "Uses Look Up" and "Value in table". To attempt to get a sum of the counts of the 2, I set up the COUNTIFS functions as follows:
=COUNTIFS(F1:F128,"=Uses Look Up",F1:F128,"=Value in table")
The value retuned is 0, however, when I count them separately the count is 75. Why did the COUNTIFS function return 0?
Thanks, Carlo
Hi,
Is there a way to have a total price adjusted based on the day they typed a quantity into a cell? Lets say price goes up on oct 24 for apples, from $10 to $15. Is there a way to have it calculate the order of 2 apples on Oct 23 (before the 24th), but also calculate the total if they order the apples on the 25th (after the 24th)? If they didn't type in the quantity till after the advanced priced date. This has to be real time.
Thanks, Ryan
Dear Mam ,
I will appreciate to resolve my problem.
i have different date in column which i required the count where dates is mentioned and rest blank not count .
Please help me
Center Revised Date 2nd PM Plan Date 3rd PM Plan Date Total Count
Bannu1 1Oct14 1Oct14 Blank ?(Answers should be 2)
Hello Muhammad,
You can use this formula to count all nonblank cells:
=COUNTIF(B2:F2,"*")
To count only dates and numbers greater than 0, please use the following array formula (you have to press Ctrl + Shift + Enter to complete it):
{=SUM(NOT(ISERROR(DATEVALUE(TEXT(A2:F2,"mm/dd/yyy")))))}
Where A2:F2 is the range you want to count.
Hi,
I have a data in 2 rows 1st row contains date, and in the 2nd row i have comment codes, i want the latest comment code and latest date in a separate cell in the same row.
Example:
1st Row: 1oct, 2oct, 3oct, 4oct, 5oct, 6oct, 7oct,.....
2nd Row: ASDF DSFA FGHJ WERT, SDFA .....
I want the latest comment and date at the end of row for each row.
I hope you understand the requirement...please help me.
Prasad
Hello,
I am not sure I can follow you. If you can send your sample workbook to our support team at support@ablebits.com, we will try to help.
Hi Swetlana,
Hope you doing well.
I am having difficulty with formulas of multiple critearia.
My formulas below is worked with me.
=Countif(ACTL!B3:B50001;A$1;ACTL!$E$2:$E$50000;B$2;ACTL!$G$2:$G$50000;$B7)
I want to add one more criteria which is date period.My Dates coloumn is starting from C1 till C1000.
The target date is at B1(15oct2014)
How can I continue to my formulas which will give me the total after 15Oct2014.
If you can help me on it will be appreciate.
Thanks & Regards.
Utkan
Hi Utkan,
All ranges in COUNTIFS formulas should have the same number of rows and columns. Most likely, you need this formula:
=COUNTIFS(ACTL!B3:B50001;A$1;ACTL!$E$2:$E$50000;B$2;ACTL!$G$2:$G$50000;$B7, ACTL!$C$2:$C$50000;$B$1)
Hello,
Problem I cannot seem solve.
Looking for a formula that counts occurrences of a value IF they occur after a certain date.
This works fine with SUMIF e.g. =SUMIF($O$2:$IU$2;"<=" & TODAY();O26:IU26) Where the sum of numbers after a certain date are returned.
DATASET looks like:
DATES : 25/1026/1027/1028/1029/10...
TASK A done by: A  B  A  A  C
TASK B done by: B  A  B  C  A
I want to know how many times A, B and C occur after TODAY...
Thanks a lot for your much appreciated help!
Floris
Hello Floris,
Please specify how many tasks and how many A/B/C/ for each task you have. Are the dates sorted in ascending order? Do you want to get the result on the same sheet or on a new one?
Anyway, I believe the best solution for this task is to use a VBA macro.
Hi,
I'm trying to get a count of dates that are due but on condition that they are listed as being follow up. My countif for follow up works fine {=countif(Q2:Q,"*up")}, and my countif for overdue dates works fine {="Follow up (due = "&countif(J2:J,"<="&today()&" )")}, but no matter what I do I can't manage to combine them. Any help would be much appreciated.
Thanx.
Turns out that the was a bracket in the wrong place:
="Follow up (due = "&countif(J2:J,"<="&today())&" )"
works very nicely.
Just mailed you my sheet can you help me with that please
Hi Svetlana,
Just mailed you my sheet can you help me with that please
Svetlana Cheusheva
Hi,
I'm trying to get a count of common digit like (12342345278912892548 = 2 100% 4 50% 8 50% 3 20%
Please help
Hi, Svetlana,
Do you know if you can use countifs accross multiple worksheets? I'm tyring to count an occurance of a entry from a pick list, it occurs in the same column on each sheet but I can't find out how to reference multiple sheets?
Any help would be greatly apprciated!
Hi Brandy,
Just add a sheet's name with the exclamation mark before the range, for example like this:
=COUNTIFS(Sheet1!A:A,1, Sheet2!A:A,1)
This formula counts how many 1's there are in column A in Sheet1 and Sheet2 in the same rows.
Hello Svetlana,
Iam impressed with your suggestions given above.
below is the table for date & related days. I want count of total Saturdays. Means it should not consider repeated entries of same dates.
My result should be 2.
Can you help me
3Jan15 Sat
3Jan15 Sat
10Jan15 Sat
10Jan15 Sat
Thanks in advance.
Hello Tushar,
If you want to count only unique Saturdays, then you need to add a helper column to your table.
Please enter the following formula into the 2nd cell of the helper column:
=IF(AND(COUNTIF($A$2:A2,A2)=1,WEEKDAY(A2,2)=6),1,0)
Where A is the column with dates.
Then just copy the formula down to the end. After that you need to sum this column, e.g. =sum(C:C)
Where C is the helper column.
Hi Svetlana, I just sent you an email asking for your help with a formula. Thank you so much for all you do!
Hi there,
Here is the formula I am working with that almost produced the desired result:
=COUNTIF('Entity SPORTS'!$A6:$A20,"'Entity SUMMARY'!A6")+COUNTIF('Entity SPORTS'!$C6:$C20,"m")
I want to count ONLY IF the first part is true AND the second part is true. Should I be using a different type of formula?
To provide some context: On one worksheet I have a list of clubs (column a  club name is repeated as many times as there are people in the club), the names of the people in the clubs (column b), and their gender (column c, listed as M or F). I want to summarize the number of males and females for each club on a separate worksheet so that column A states the club name, column b will have the number of M, and column C will have the number of F.
Any help would be greatly appreciated!
Hi,
I have a doubt in excel. I have row from a1 to d1 with numbers or string and c1 have no numbers or string.
what i am looking is when any cell is blank then the formula would return the text otherwise sum of all number from a1 to d1.
reply or solutions much appreciated
thanks
I don't have too much experience but I think a simple if min=0 then text else sum shoulf=d be easy to make.
hi
Can you explain with an example?
If you come with an example that would be great
Hey Svetlana Cheusheva, I am trying to count values in a column, but I would like to leave out all the blanks and 0 values. Any help would be appreciated.
Hi  how can I find the LATEST TIME for different products. Product can be repeated multiple times. example:
Prod1  12:30
Prod1  13:45
Prod2  07:00
Prod2  16:00
Prod1  09:00
Now it should report Prod1 as 13:45 and Prod2 as 16:00.
Thanks for your help in advance.
Hi,
Your example 4: The formula show B and C cells but you highlighted B and D cells in the printscreen.
Regards.
In cells A1 to A30 I have 2 alphabets A&E and in the BCells have values .I need a formula to count the no. Of values of A& E in the Bcell. Pl help
Hello Gespion,
Try the following formulas:
Sum values in column B corresponding to "A" in column A:
=SUMIF(A1:A20,"a",B1:B30)
Sum values in column B corresponding to "E" in column A:
=SUMIF(A1:A20,"e",B1:B30)
Is it possible to change the value of a cell so that is counted as two as opposed to one? I'm using COUNTIF to calculate the number of hours each employee is working in a schedule, but some cells equate to 2 hours and others equate to 3.
Hi Abby,
And how do you determine that a particular cell equates 2, or 3 or 1?
I skimmed the comments and didn't see my situation listed:
I want to determine the number of unique entries in a column (D:D) that fall between a pair of dates (say a1 and a2) where the dates of the events are in a different column (J:J).
I know I can count all of the entries in the date range using
=COUNTIFS(!J:J, ""&A2)
and I know I can count all of the unique entries in D:D using
=SUMPRODUCT((D:D"")/COUNTIF(D:D,D:D&""))
How do I put them together?
That didn't post right:
COUNTIFS(J:J, ""&A2)
It's not posting correctly, but hopefully you can interpret what I mean.
COUNTIFS(J:J, ""&A2)
Thank you for any help.
Hello!
Regrettably, we don't know the way to fulfill your task using just one formula. You can either add a helper column or use a VBA macro.
For example, you can enter one of the following formulas in cell K2 (if unique entries are checked by column D and date range):
1 =IF(AND(J2>=$A$1,J2<=$A$2,COUNTIFS(D:D,D2,J:J,">="&$A$1,J:J,"<="&$A$2)=1),1,0)
If you check for unique data only by column D:
2 =IF(AND(J2>=$A$1,J2<=$A$2,COUNTIF(D:D,D2)=1),1,0)
Just copy the formula to the other cells of the helper column and then sum the data in the column.
Thank you for the quick and informative reply.
Hi Svetlana,
I have data in Col that A.
I want to know no of lines between each pipe ()
Expected Result in B col for
first pipe is 0
Second Pipe is 6
Third Pipe is 7
Data

1
ZGF1213420
S ILIYAAZ
S AZEES MIAH
345322
29

2
ZGF0316174
U SUMALATA
NARASHIMHA
MURTHI
53/1
21

Kindly do the neeful.
I have emailed the sheet to you emailid.
Hi Khalid,
You need a special macro for your task, sorry I cannot help with this.
Is there any way I can use a formula that will add up different amount of rows but in the same column? Example. In cell AG12, I need it to have the sum of AF8:AF12, however, then I need AG15 to have the sum of AF13:AF15
The only solution I can suggest in this case is writing different formulas for those cells.
Hi,
Im trying to do a sum of a range whilst looking at the date completed, for example....
Qty Date
5 02Oct14
100 02Nov14
5 02Oct14
5 02Nov14
4 02Nov14
54 02Dec14
5 02Dec14
6 02Dec14
Is there a formula give me the sum of left column against the factor Oct for instance and should bring back the result 10.
Thanks for your help
Gavin
Hello Svetlana,
can you help please?
I have a workbook referencing different tabs, on one sheet I have:
Crew Manpower
A 4
A 4
B 5
C 3
And on the other sheet I have:
Crew Crew Member
A John
Steve
Beto
Frank
B Ted
Mike
Chris
Nate
Terry
In the cell that states "Manpower" I wan to count the number of crew members according to the "crew" letter. I've tried nested COUNTA, IF, AND COUNTIFS and i keep getting errors.
what am i doing wrong?
Thank you!
Svetlana,
Having some dificulties trying to figure out, how to count certain transactions that met 2 criterias City=Dallas and Type=SH.
The table has multiple values and the following columns
City
Date
Type
Transaction
I have been trying using Countifs, with no success.
Thanks a lot for your support.
Juan Pablo...
Is there way count cells when the date change? Say for instance I have a list people and their birthdays. Column A includes the names and Column B includes the birthdays. Is it possible to count how many people have the same birthdays? I believe I can do it birthday individually but is there is there a function that will automatically count them. I am do something similar to that however I have over 75,000 records to check. Is there that well automatically give me total after each birthday is changed. Like, if the previous birthday is not equal to the next date, display total of the previous birthday and reset total. Please advise. Thanks in advance!
Ok. Because question 4 passed in mass i need to track earned sick time. I want to set up the sheet that if a cell is 30 the next cell =1 if 60 =2 and so on. What formula should I use.
hi,
i have multiple mobile numbers where i want to check that how many time 1 Numbers coming but i am not able found however i have check with countifs but it not happening so please help me then how can check such type values in 10 digit mobile numbers
Regards
Bhajan lal Sharma
Hi,
I came across to this forum and it looks nice.
I need your help in excel that would assign the same number in multiple rows example below.
I need to do this in 58,000 plus records and really want to somehow automate the process.
Col1 Col2 Results
Kit 1 Kit 1
Kit 2 Kit 1
Kit 3 Kit 1
Len 4 Len 2
Len 5 Len 2
Len 6 Len 2
Len 7 Len 2
Daddy 8 Daddy 3
Daddy 9 Daddy 3
Daddy 10 Daddy 3
Amy 11 Amy 4
Amy 12 Amy 4
Amy 13 Amy 4
Stephanie 14 Stephanie 5
Stephanie 15 Stephanie 5
Stephanie 16 Stephanie 5
Mommy 17 Mommy 6
Mommy 18 Mommy 6
Mommy 19 Mommy 6
Mommy 20 Mommy 6
Col1 Col2 Results
Kit 1 Kit 1
Kit 2 Kit 1
Kit 3 Kit 1
Len 4 Len 2
Len 5 Len 2
Len 6 Len 2
Len 7 Len 2
Daddy 8 Daddy 3
Daddy 9 Daddy 3
Daddy 10 Daddy 3
Amy 11 Amy 4
Amy 12 Amy 4
Amy 13 Amy 4
Stephanie 14 Stephanie 5
Stephanie 15 Stephanie 5
Stephanie 16 Stephanie 5
Mommy 17 Mommy 6
Mommy 18 Mommy 6
Mommy 19 Mommy 6
Mommy 20 Mommy 6
I need to calculate following Commissions on Sales how can i put in Formula
<=20,000 = 1000 (Fixed)
20,000 to 50,000 = 4%
50000 to 100,000 = 3%
100,000 to 200,000 = 2.5%
And
200,000 and Above =2%
Please help me ...
Greetings,
Maybe I missed the answer but need help in applying a formula to only count dates that are within the last year. My data is pulled from SharePoint Lists then using another tab I am able to count how many items are done based on each section (currently have 5 different sections)
The formula I am using is:
COUNTIFS(Table1[Section],”Section”,Table1[Safety],””)
I would like it to only count the dates in Safety that are within the last year when the sheet is refreshed.
Any help will be greatly appreciated.
After safety I have between the quotes in the formula.
hi,
can you help please,,,
i have five columns with differents informations and want to get all in another sheet by using only code, example:
A B C D E
CODE LOCATION KM COST EXTRA
100 SOHAR 50 25 2
the information in the cells never change,,,can i make a formula ,when typing a code then get the other information in another 4 cells
thanks
ABCDE
CODELOCATIONKMCOSTEXTRA
100SOHAR50252
hello
Thank you for the details shared.
I have 4 digit numbers like 1234,2345,2334, etc. i want to know when 4 comes as 4th digit which number comes mostly in 3rd digit as well as in the 1st and 2nd.
there are thousands of numbers looking forward for your reply. it would help me a lot
Hi
I am currently struggling with this  I need a formula that works out the oldest date in A that does not have a processed date in Q.
I have a formula that works out A =MIN(Audits!A7:A3026)but cannot figure out the COUNTIF for the second criteria, does anyone know if I am on the right track with a COUNTIF or am I just doing this wrong?
Regards
as example,,in the first sheet as below,,,
A B D E F
CODE FILLING STATION K.M AMOUNT ALAWNS
5347 FALAJ ALQABAIL F/S 15 16.74 1
5294 FALAJ ALQABAIL T 16 23.02 1
5365 FALAJ ALQABAIL N 17 23.02 1
5727 SHINAS F/S 12 24.7 1.5
5124 SOHAR BEACH F/S 25 24.7 1.5
5530 SOHAR TREEF F/S 25 24.7 1.5
5336 ALWUQAIBA F/S 28 29.3 1.5
5054 SOHAR SINAIYA F/S 29 29.3 2
5299 SOHAR F/S 29 29.3 1.5
5442 HUMAIRA F/S 30 29.3 1.5
5211 AQAR F/S 45 38.51 2.5
5438 AQAR TWIN FjS 47 41.86 2
in the second sheet will be like as below,,,
J k L M N
CODE FILLING STATION K.M AMOUNT Allowances
5727 ? ? ? ?
5442 ? ? ? ?
5294 ? ? ? ?
My question is ,,if i type the number which is i column "A" in the secound sheet in the column "J" how can i make a formula to get the information from
column "B" ,"C" , "D" , "E" in the secound sheet in the column "K" , "L"
"M" ,"N" , ???or whatever columns in any sheet even at the same one,,
i hope my question clear to you
Thanks in Advance
1
2
3
4
5
6
7
8
9
10
25
64
54
654
546
40
so how can find highest number in excel sheet
Hi Zaheer,
You can use the MAX function for this, e.g:
=MAX(A1:A20)
Where A1 is the first and A20 is the last cell in the range.
Hi,
I want to count 1 column of data (contains £££) if another column contains a specific word.
Could you help?
Thanks
Hi RC,
I am not sure I understand your task correctly. Anyway, if you want to sum cell in column A if a corresponding cell in column B contains a given word, you can use the following SUMIF formula:
=SUMIF(B:B,"word",A:A)
Hi all,
I Believe someone can help me here. what i want is from few different cell, i want to select certain letters or numbers. and want to to be able to link to one cell where it
collect the selected letters and numbers from different cell and show as eg. cell 4 = (12345GB).
eg. cell 1 = 0012
eg. cell 2 = 4434
eg. cell 3 = Good Buy
i hope this is clear. its just so as i am inputting data on other cells and it generates like a ref if you like on the cell 4 as i instructed.
please help?
how would I write it sothat I could count a number if a condition is met EG
A2=Knife
B2=2
A3=Spoon
B3=4
A4=Fork
B4=3
A5=Knife
B6=7
and I want b1 to count where A2:A5 = Knife but use the totals in B2:B6
So it would count = 9
Hello Zak,
You need to use the SUMIF function for your task, here is the formula:
=SUMIF(A2:A5, "Knife", B2:B5)
For more information about SUMIF, please see this article:
https://www.ablebits.com/officeaddinsblog/2014/11/04/excelsumiffunctionformulaexamples/
I'm trying to create a spreadsheet to keep up with employee's absences, tardies, etc. for a year. Each incident is not counted after a year. So I have a cell on top that says today's date and used =today() so that when I open the file the date will fill in. I have the dates in the first column, the second is tardies, leave early and vacation. On top under today's date, I want it to show the total Tardies, for a year (from today minus one year).
Today's Date '=today()
Tardies ??? < This should be a total for the year using
Leave Early ??? < same as above
vacation ??? < same as above
Date Tardies Leave Early Vacation
11/01/13 1
11/02/13 1
11/03/13 1
11/04/13 1
11/05/13 1
11/06/13 1
For Example: the ???'s above should add from today (11/2/14) to 11/2/13 and give me a total of 2 so that when I open the spreadsheet on a certain day, that information will automatically update and show me how many tardies, leave earlies and vacation days they have for that given year.
I have a situation where the text that I want to count instances of is in various locations within the cells, e.g. A1 cell may contain 'Dog'; A2 cell may contain 'Cat, Dog', cell A3 may contain 'Bird, Cat, Dog'. I want to count all of the instances within the column that contains the word 'Dog'. It seems that no matter how I try to finesse this using COUNTIF and COUNTIFS with FIND and SEARCH etc. I can't seem to get this to work.
Please help.
Nevermind! I found the solution elsewhere.
I have a workbook with multiple worksheets of inventory from various departments. I'm trying to create a summary page that sums the different categories of inventory from each worksheet tab. For example, how many desks do we have in total.
My worksheets are set up like this:
Desks. 26
Chairs. 57
Cabinets. 1
But the info isn't in the same cells on each worksheet.
Is this possible? Im p.yaong with countif but can't get it to work.
Hi Dean,
An immediate solution that comes to mind is using a combination of SUMIF functions like this:
=SUMIF(Sheet2!A2:A7, "desks", Sheet2!B2:B7) + SUMIF(Sheet3!A6:A17, "desks", Sheet3!B6:B17)
Where column A contains the categories of inventory and column B  the corresponding numbers. You can specify different ranges and different columns for each sheet.
For more information about the SUMIF function with multiple criteria, please see this tutorial:
https://www.ablebits.com/officeaddinsblog/2014/11/12/excelsumifssumifmultiplecriteria/
Hi There,
I have tried multiple functions and nothing works!! I have tried COUNTIF, COUNTIFS, SUMIF, SUMPRODUCT and I cannot get the result I need!
can you help.
I am trying to count the number of cells which contain a certain Text before a date.
I used this formula to calculate all cells within a month that belong to date
=SUMPRODUCT((MONTH(J6:K1000)=11)*(YEAR(J6:K1000)=2014))
but now I need the first criteria to be a text specifically "DT*", and the second to be before november (<11)
can you help?
I have a set of customers and their ages in the Rows. In the Column section, I have a set of ranges from: <12months, <36months, <3years, 3 and <7years, 7 and <17 years, 17 and <40years. I want to add a "1" down the columns for each customer that is <12months, or less than 3 years, etc...Then I want to count how many of them are in that age group and get the total for each age range. May you help me please?
I am trying to use countif to return a value of less than or equal to 15 for a range of cells in column A. It keeps returning a zero result. My formula looks like this : =countif(A3010:A3190,"<=15"). Does it matter that my A column is actually a formula that is resulting in the numbers being displayed?
Hi there  hoping you can help please!
I have two columns
Total Sessions Location
1 S
10 B
5 S
6 B
what is a countifs formula to reference the location and return and count the sessions please?
many thanks
Hi KC,
If my understanding of your task is correct, you need a SUMIF formula rather than COUNTIF. For example, the following formula will pick all "b" locations in column B and return the total of corresponding sessions (column A):
=SUMIF(B:B,"b",A:A)
In your example, the result will be 16. If you are looking for something different, please clarify.
Hi, i have some formula populated column.
I was trying to do a COUNTIFS(,"") to find the data which condition false.
But i understood that COUNTIFS takes the formula as a value and gives a erratic value.
Can you guide?
Hi,
Column A: dates
Column B: numbers
I would like to calculate the number of times values in column B within a specified range occur within a specified date range. I've emailed an example file to support.
Any help would be much appreciated.
Many thanks,
Dan
Hi Dan,
I believe the following formula will work for you:
=COUNTIFS($B$2:$B$75, ">=6", $B$2:$B$75, "<=7", $A$2:$A$75, ">="&F2, $A$2:$A$75, "<="&G2)
I emailed you the worksheet with the formulas a moment ago. Hopefully, this is what you are looking for.
Hi,
How can I solved to multiple word in a criteria from single word in a excel file. pls help me
Hello Rasel,
I am sorry, your task is not clear. If you can describe it in more detail and give some example, we'll try to help.
Thanks for your replay......
Actually I would like to say,
which formula should I use from multiple condition but value will be text.
For example: sumifs functions.
If I use to sumifs formula then it result any data but it not be text.....
Please help me if you cooperated me..
If a worksheet arranged as mentioned below, HOW CAN COUNT HOW MANY CT BECOME BETWEEN AGE OF 21 TO 25. I HAD TRIED TO CALCULATE THIS IN ANOTHER SHEET BY USING FORMULA =COUNTIFS('NOMINAL ROLL '!A:A,"=CT",'NOMINAL ROLL '!C:C,">=21",'NOMINAL ROLL '!C:C,">=25"). BUT NOT SHOWING ACTUAL RESULT. REQUEST INTIMATE A SUITABLE FORMULA TO SOLVE THE THIS PROBLEM, PLEASE.
NOMINAL ROLL (SHEET1)
A B C
RANK NAME AGE
CT Vinod Singh 22
CT Rajesh Kumar Purohit 24
CT VIJAY KUMAR 25
SI CHANDAN KR PASWAN 25
CT MANTU KUMAR RAM 23
CT MITHILESH KUMAR JH 24
CT PURUSHOTAM KUMAR 23
CT AJAY MANDAL 27
SI KAILASH SOREN 29
CT DEBASHIS SHIL 27
SI CHHOTU UJMODAK 30
CT DIPAK TIGGA 27
CT SUMANTA KR DAS 30
CT SAGAR DHAMALA 28
HC SANDEEP KUMAR 25
CT RAYAZ AHMAD 23
CT MONIRATH MONDAL 23
CT BABLU NAIK 23
HC PRADIP MONDAL 23
CT TAPASA ROY 25
Hi Satheesh,
Your formula looks correct, just replace >=25 with <=25 :)
i need a formula to automatically check mark i.e(ü) mark a cell(eg:A1) if another cell (eg:H1)contains number:1
Condition is H1 may contain any numbers like 1,2,5,6,8 to 20
A1 should check mark only if there is 1
( if i drag formul A2 should check mark only if there is 2...and process upto A20 i.e number 20)
Formula should come like this:
(A1:A20) should Check H1 if perticular number contains in that cell i.e
check H1 if it contains 1,3 then tickmark A1 A3
check H1 if it contains 2, 20 then tickmark A2 A20
check H1 if it contains 3 then tickmark A3
upto 20numbers
Formula should come like this:
(B1:B20) should Check H2 if perticular number contains in that cell i.e
check H2 if it contains 1,3 then tickmark B1 B3
check H2 if it contains 15 then tickmark B15
check H2 if it contains 18 then tickmark B18
Upto 20 numbers
IF IT IS NOT POSSIBLE TO USE NUMBERS IN SINGLE CELL
I CAN TAKE H1 I1 J1...
Hello Wilfy,
If you can send your workbook with the source data and the result you want to get to support@ablebits.com, our support team will try to help you.
Hi, hope you can help. I'm trying to get a cell give me a figure of the following(Cell A is First Aid Course and Cell I says they attended or not attended)  I don't want it to count if the cell is blank (so basically a upcoming course)
Example
Course Title Start Date End Date Tutor Customer Name Attended?
First Aid 01/11/14 14/11/14 John Paul Jones Attended
First Aid 01/11/14 14/11/14 John Sarah Smith Attended
First Aid 01/11/14 14/11/14 John Mo Ali FTA
Food Safety 11/11/14 15/11/14 Lee Paul Jones Attended
Food Safety 11/11/14 15/11/14 Lee Sharon Jones FTA
First Aid 24/11/14 31/11/14 John Steve Bi
First Aid 24/11/14 31/11/14 John Simon Gee
Result to go into this type of box
Course Name Total Referred Attended FTA
First Aid ? ? ?
Food Safety ? ? ?
I can send on the sheet if its easier.
Thanking you in advance
regards
wayne
Sorry it hasn't posted correctly  just looks a mess
Hi Wayne,
Regrettably, our blog engine is not perfect... If you can send us your sample workbook at support@ablebits.com, it will be easier for us to figure out the task.
Hi,
Below is my issue. I have a few statuses which can be used to a closed ticket. I want to count all the closed tickets in the worksheet using the status. I used below formula but it did not work. Please help..
COUNTIFS(A2:A1000,"A",B2:B1000,"Night",C2:C1000, OR("Solved","Cancelled",....))
Department Shift Status
A Night Solved
A Night Cancelled
Thanks in Advance!
Chalinda
Hi,
I want to create a formula that counts how many of the letter 'y' occurs in particular columns but not in a range. The columns I want to search are D2, F2, H2, J2, L2, N2, P2, R2, T2, V2. The problem I'm encountering is that I don't want to search E2, G2, I2, ... I then want to have this formula for all rows in the spreadsheet. I don't see the answer above ... Thanks!
I want to be able to check if data in one cell in column a = x and then if so count the data in adjacent cell in column b.
To explain in more detail, I am creating a statistics chart where commissioners need to be able to be able to compare data for region 1 to region 2 and region 3.
So column A will contain region keys such as 1, 1, 1, 2, 3, 3 then in column B will be ages. So if column A contains a 1 collect data in cell adjacent to the cell it is counting.
Hi! Here's the data I want to process. I want to count the number of entries a specific person submitted that matches anything except Not Qualified.
DECLINED Peñalosa, Joyce Ann
NOT QUALIFIED Calvo, Vivian
CALLBACK Roda, Scepter John
NOT QUALIFIED Henardino Jr., Edwin
NOT QUALIFIED Asentado, Darell
CALLBACK Peñalosa, Joyce Ann
DECLINED Roda, Scepter John
CALLBACK Alipio, Marisel
DECLINED Emeterio, Absalon
NOT QUALIFIED Solomon, Cinderella
NOT QUALIFIED Solomon, Cinderella
CALLBACK Toring, Jared
CALLBACK Henardino Jr., Edwin
NOT QUALIFIED Viscayno, Mabel
NOT QUALIFIED Viscayno, Mabel
DECLINED Solomon, Cinderella
SCHEDULED Roda, Scepter John
CALLBACK Emeterio, Absalon
NOT QUALIFIED Henardino Jr., Edwin
NOT QUALIFIED Trocio, Princess Joy
CALLBACK Baruel, Coleen Grace
CALLBACK Baruel, Coleen Grace
CALLBACK Baruel, Coleen Grace
NOT QUALIFIED Toring, Jared
SCHEDULED Solomon, Cinderella
SCHEDULED Gamboa, Estrella
DECLINED Peñalosa, Joyce Ann
DECLINED Calvo, Vivian
NOT QUALIFIED Alipio, Marisel
CALLBACK Baruel, Coleen Grace
CALLBACK Arizobal, Argin
DECLINED Calvo, Vivian
NOT QUALIFIED Arizobal, Argin
CALLBACK Toring, Jared
DECLINED Emeterio, Absalon
SCHEDULED Peñalosa, Joyce Ann
NOT QUALIFIED Henardino Jr., Edwin
Hello Shwan,
Here you go:
=COUNTIF($B$1:$B$100, "Person Name")  COUNTIFS($B$1:$B$100, "Person Name", $A$1:$A$100, "Not Qualified")
Hello,
My countif formula returns 0 if it does not anything that matches the criteria. Is there a way to set up the formula so that if it doesn't find anything that matchs the formula returns "" instead of 0.
Thank you
I like to count First five character as a individual number like; 98765_A3(98765),98765_A6(98765),87654_A2,(87654),92345_A1(92345) in a column but in different row and same numbers count once only. How can I do this?
I like to count First five character(number) as a individual number in a column but in different rows and same numbers count once only. How can I do this in excel? Fore example;
A B
98765_A1A2A3 ISO
98765_A4 AATCC
87654_A2A3 AATCC
92345_A1A3 ISO
Dear All
I need urgent help on one formula. For Eg
A B C D E
AXT11232 2,500 89369 sent
I want in column E the current date if any of the cells like A, B, C or D are filled. My data will be in the same format as in A, B, C. I just want the current DATE , whenever data has been entered in any of the cells marked as A, B , C or D
I have only one column
how to count how many row have time 7 am to 8 am and next 8 am to 9 am and next?
7:31:11
7:48:41
7:51:35
7:59:49
8:02:01
8:03:47
8:06:41
8:07:16
8:07:47
8:15:20
8:19:43
8:29:16
8:33:42
9:09:05
9:09:36
9:11:56
9:19:27
9:19:55
9:20:25
9:26:13
9:34:33
9:35:19
9:47:05
9:49:42
10:10:10
10:10:24
10:10:37
10:10:59
10:11:10
10:11:22
10:11:43
10:12:07
10:12:29
10:12:42
10:13:04
10:13:25
10:14:05
Hi, for my case, cell A1 has an entry of 19/11/14 and cell B1 has an entry of 25/11/14. 25/11/14 is more than 19/11/14 by 6 days. I would like to set the formula for the dates that are more than 5 days to be false. Which means the entry above is false because it is 6 days.
And for the entries that are less than or equal to 5 days to be True.
i have a question, i am making a personnel planning for my employees. This is based on competences this means that specific people are trained to do job A but they can not do job B. I have 2 tables. One table "Training" with the name of the person and in the same row for which jobs they are trained so job A, job B, job D. Then i have the real personnel planning. I have on top which job needs to be executed so for example job A. Hence I put a name of the person. I want to have the cell highlighted in Red if the person is not trained for job A. I think to do it via COUNTIF but i am confused as I have different criteria, i need to refer first to job A, hence i need to search if that specific person is trained for job A. Can somebody help me please?
Hi,
I am trying to count the number of days marked "Holiday" in a set range but only those with a date less than 31/05/2014 but greater than 31/03/2014. The date range is in column R with the text fields in columns T:X.
The other point is that the date column only has a week commencing date, not a date for every cell.
I have tried =COUNTIFS($R:$R, "<="&I30,$T:$X, "Holiday")(where I30 is a date field with 31/05/2014). This produces an #VALUE! error. I haven't tried to enter the additional date criteria as I wanted to get the formula to work first.
Taking each part in isolation (=COUNTIF($R:$R, "<="&I300 and =COUNTIF($T:$X, "Holiday")) works perfectly but not combined.
Can anyone point me in the direction of where I'm going wrong?
Hi Svetlana
I am trying to count the number of time a postcode is used within some 35,000 records. The postcodes have the format 'AB10 9BC' and I only wish to count the first group of figures. Using =COUNTIF(G:G,"AB10*")seems to work fine BUT.... Some of the first group of numbers consist only of three digits eg 'AB1 9BC'. This means that counting for AB1* also includes and counts AB10/AB11/AB12 etc etc. How can I count only AB1 postcodes in this example? Hope that makes sense and I look forward to your reply.
Hello
i need help to be able to use the countif function or similar to count text as 2 instead of just one is this possible
thanks
Hello Liam,
Just multiply the COUNTIF result by 2, e.g. =COUNTIF(A:A,"text")*2 If you have any other conditions, please specify.
Is it possible to use the COUNTIF option when using fractions? I have a column of several lengths (32", 36", and 48"). I know the formula to count the cells  but I'd like to count the cells in the same column that only have 32" lengths in the cell. I tried the following and it did not work.
=COUNTIF(A3:A25,32")
=COUNTIF(A3:A25,32)
=COUNTIF(A3:A25,"32")
Thank you!!!!!
Hi Becky,
Try this formula:
=COUNTIF(A3:A25,"32""")
I know it looks very unusual, but it works : )
I am trying to count the number of occurrence of which the two certain conditions must be met. I tried it this way
COUNTIFS('Brgy Cases,other Agencies'!$C$11:$C$13,"*Alangan*",'Brgy Cases,other Agencies'!$V$11:$V$13,"*Those Issued*") + COUNTIFS('Brgy Cases,other Agencies'!$C$15:$C$17,"*Alangan*",'Brgy Cases,other Agencies'!$V$15:$V$17,"*Those Issued*") + COUNTIFS('Brgy Cases,other Agencies'!$C$19:$C$21,"*Alangan*",'Brgy Cases,other Agencies'!$V$19:$V$21,"*Those Issued*") + COUNTIFS('Brgy Cases,other Agencies'!$C$23:$C$24,"*Alangan*",'Brgy Cases,other Agencies'!$V$23:$V$24,"*Those Issued*") + COUNTIFS('Brgy Cases,other Agencies'!$C$26:$C$27,"*Alangan*",'Brgy Cases,other Agencies'!$V$26:$V$27,"*Those Issued*")
You can notice that there is 5 COUNTIFS function, and it's working fine. Unfortunately, when i am trying to add another set of countifs, the result returns a date format.
Is it because there is a limit for summing up the occurrence in COUNTIF limited only to 5?
Thank you.
Hi Michael,
A formula can contain up to 8192 characters, no matter how many COUNTIFS are used. Just change the format of the cell with the formula to Number.
HI
I PREPARED A WORKSHEET OF OUR EMPLOYEES TO CALCULATE SOME DATA SUCH AS THERE AGE AS ON DATE, DIVIDED THEIR AGE GROUPS ETC. BY GIVING DATE OF BIRTH. I CALCULATED THEIR AGE BY APPLYING =YEARFRAC(A1,TODAY(), 1) FORMULA. FURTHER I TRIED THEIR AGE BY DIFFERENT GROUPS SUCH AS 2025, 2630 ETC. FOR WHICH I APPLIED =COUNTIFS(C:C,"=CT",'G:G,">=21",G:G,"<=25"). BUT DID NOT SHOWING ACTUAL NUMBERS OF PERSONNEL COMES UNDER THAT CATEGORY IN SAID WORK SHEET. REQUEST HELP ME TO SOLVE THIS ISSUE.
S/N RANK NAME DATE OF BIRTH AGE AGE 2125
1 CT MUNNA 18061991 23 17
2 CT MITHUN 28021991 24
3 CT BISWAJIT 26061991 23
4 CT KARTICK 14101989 25
5 CT SOURAV 22101991 23
6 CT VIKASH 28021991 24
7 CT VEERENDRA 03011992 23
8 CT OM PRAKASH 01071989 25
9 CT PAWAN 02011990 25
10 CT SANJAY 09011990 25
11 CT SUNIL 21101990 24
12 CT Atanu 10071991 23
13 CT KALI 05021990 25
14 CT RAKESH 05011991 24
15 CT SUNIL 09021991 24
16 CT PUPINDER 12121991 23
17 CT PATIL 23111991 23
18 CT Gulab 10071991 23
19 CT Bhupendra 18121991 23
ACTUALLY ALL ARE COMES UNDER 2125 AGE GROUP BUT SHOWING ONLY 17 NOS.
THANKING U
Hi Satheesh,
The correct formula is as follwos (F:F instead of G:G):
=COUNTIFS(C:C,"=CT",F:F,">=21",F:F,"<=25")
I have a document where one column has list of name (around 100). Then I have so many other columns which can have any one value from set of 5 values(val1,val2, val3, val4, val5).
Now I want to count number of val3 in row where name is Name12.
Now to make is dynamic I have created 2 cells
1. First to select Name (data validation = list)
2. Second to select val (data validation = list)
Now I want the next row to calculate total cell where value = value of second cell but from the row where name = value from first cell.
Hello Amit,
You need a VBA macro to fulfill this task. You can ask for it in special VBA sections on mrexcel.com or excelforum.com
Hi Svetlana, I've read most of these question and can't find one with the same problem as I'm having. I have a range, for example A3:A9, and I want a result when another cell contains text from one of the cells in the range. For example if A3="cat", I want a result when, for example, C2="a cat on a mat". If the situation was reversed, and I wanted to know if the contents of a cell was contained in a range, I'd do =COUNTIF(A3:A9,"*"&C2&"*"), but I want to know if any cell in a range contains text in a cell. My problem is I can't use add anything, like a wildcard, to a range, so I can't do =COUNTIF("*"&A3:A9&"*";C2)
Is there a way to get this result?
Thanks
Ben
hi,
i have a excel sheet where many of products like 20p,6P,2D
and i want to sum of products in next sheet againts product when i filter any where in main product sheet
Hello Chandan,
I am sorry, I cannot follow you. If you can explain the task in more detail and provide a sample of your data, we'll try to help.
HI there.
I'm not well versed in COUNTIF, but I think this is what I need but not sure how to go about creating a formula.
I have column with a $$ amounts in each cell and the corresponding column/cell would either be a Yes or No.
I need to create a grand total of all Yes' and a separate grand total for No's.
Can you help?
Thanks in advance.
Marcella
Formula that will continuesly count based on Cell value and number it sequencially
Col1
ab
cd
ef
ef
cd
ab
Result
ab201401
ab201402
cd201401
cd201402
ef201401
ef201402
I have following data sheets.
sheet1data
Branch Code Risk Number of high risk No of Risk Instances
0101 H 04 18
0102 M 11 20
0103 H 9 24
0104 L 5 13
0105 M 10 26
How I can fix formula that if branch code = 0101, check risk, count number of high risk and multiply number of risk instances
Hi Svetlana,
I have been following your posts and find them very helpful.
I just need help on something:
I have a running list surgical procedures for 2014.
Col A = date (running list)
Col B = Types of Procedure (Procedure A, Procedure B,...)
I want to count total number of procedures per month:
January  How many Procedure A, B, C, D...?
February  How many Procedure A, B, C, D... and so on.
Thank you.
Please Help.
I have tree sets of data. Project Type, Resource Name and Days (columns Jan14 to Dec14).
Project Type being.. T1, T2, T3, T4, T5.
Resources X, Y, Z.....
Resource Allocation Days against each month... 17, 19, 11, 20, 20 as values
Now I need to calculate in a summary table.. Count of Months resources are allocated to a Project Type.
Can you please help me.
I have a time keeping system that is putting a "'", an apostrophe, in a a group of cells. I am trying to count the number of people that are in a range, and unfortunately the range also contains the apostrophes in otherwise blank cells. So I need to subtract the number of cells that contain the apostrophes from total number of cells. The COUNTIF does not recognize the apostrophe, I assume because it thinks it is a character to treat the cell like text rather than a date, or number, etc. (the only work around). I cannot figure out a way to fix this other than "clearing" all of the cells of apostrophes, which is very time consuming and error prone. Any ideas?
Go for.. COUNTIFS (if not using using Excel2003)
You can add new condition to exclude "apostrophes" "'".
I want to use Countif formula to test the criteria range against a specific logic. In other words, If column A contains a date, and I want to count the cell if its date is within a certain week number. What I did is =COUNTIFS(Violations!A:A,WEEKNUM(Violations!A:A=WEEKNUM(TODAY())))
But it returned 0 values. Would you please check how can I do it?
Thank you very much.. really it's very helpful
I need to use a function to count the number of Part in column c2c134 in the NWest which is in column f2f134, I dont seem to be able to enter the correct data into any function to get it to do this
I need the function to count the different condition in a single range. Like, In a single range numbers given 1,2,3,4,5,6,6. so, now i want to know in this range that how many numbers 1 to 3 are there.
example: 1,1,4,4,5,6,6. answer is 2. because in this condition 1 is two times repeating from 1 to 3 series and 6 is also 2 time repeting in 46 series.
Please help me.
I trying to have excel calculate how many times I get as close as possible to 1 with out going over, within a column of numbers in my spread sheet? Can you please help. My column is about 40 cells in length and the unit of measurement I have are random.
Example numbers  These number will be changing more then once.
.22
.45
.67
.31
Etc.
Hi i have a problem please help me to get out this problem
56 658 658
65 58 258
256 2 25
3 88 635
365 895 65
987 65 658
i want to count cell which have only two digit (example, 56,65,25 etc)
Hi,
I am trying to make a spreadsheet that will count the number of times "A" happens between "B" occurrences. I am not really sure how to explain it so here is some info of what I am trying to accomplish.
B
null
null
null
A
B
A
A
null
A
null
A
B
So for the above data, I would like to count the number of times "A" appears between any given range of "B" values. So for the first two instances of "B" there would be "1" and for the range between the second and third instances of "B" there would be "4" and "null" would not be counted (they are blank cells).
Thank you for your help.
Luke
Dell kiran 200
Dell sameer 400
Dell kiran 300
Hp amol 200
Zenith mohsin 500
Dell =countif ans 3
I want to calculate the data based on column b but calculate data form d column.I want answer if there is three dell record I want count the data
from d column that answer Is three based on dell records.
which functions is use for calculation
please help me
Hi There,
I would be deeply grateful if anyone can help me with my issues!!!
I am trying to set up my spreadsheet to enable me to calculate how many "1's" fall on the month of january.
E.G
Date Error Code(s)
01/01/2015 1
02/01/2015 1
03/01/2015 1
04/01/2015 1
05/01/2015 1
06/01/2015 1
07/01/2015 1
The idea is, I will have a break down of the error codes for each month!
I've been trying all sorts of COUNTIF formulas but have had no luck!
Please help, I'd be eternally thankful...
Am I just overlooking the obvious, or is there just not an easier formula to use to count all the cells within a column with a value that is a multiple of 5, other than:
=COUNTIF(X4:X41,5)+COUNTIF(X4:X41,10)+COUNTIF(X4:X41,15)
Listing every multiple of 5 is not even possible so surely there is another way??
PLEASE PLEASE help  even if the answer is simply "no". Thanks!
Hi there, I'm very sure my problem will be easily fixed by one of your Excel aficionados but I'm intermediate level at best. I have complied a huge roster. I had 6 dentists and we've just taken on another 2. In the past I have manually adjusted the surgery number up to our max of 6. This has now increased to a max of 8. Can I use a countif to calculate the number of surgeries in use using the Dentists initials as criteria and increasing in increments up to 8? Rather than entering these numbers manually and adjusting if someone goes on leave etc?
ie
1 TT 2 SL 3 AB 4 JM 5 TL 6 JD ?CB ?PD
Hi i've been driven mad by excel today I hope you can help.
I'm trying to make a critera with COUNTIF statement before my SUM value kicks in.
As you can see the calculation works on anything less than 12 I'm trying to get cell K15 to stop counting after 12 unfortunatley K15 will have data larger than 12 so 12 would be capped if you like. is this possible?
=COUNTIF(K15,"<13")*SUM(J15/52*4,(J15/52*K15))
Any help would be appreciated
Hi,
How are you, please help me to my below problem.
Which formula should I use from multiple condition but value will be text.
For example: =SUMIFS(A:A,B:B,"Line",C:C,"Date")
result factor=205 (suppose).
But I want to the result "text", no "digit".
So, how will I get the result "in word".....
Please help me if you cooperated me.....
Thanks in advance
Hi,
could you please advice, how to use formula if J8 contains multiple score with texts to be match with L8 (contains scoring 1,2,3). So I want if I choose/click J8 then L8 will displaying match score. Thank you
please advise how to use countif formula to use =COUNTIF(B6:B51, $A$54)/COUNTA(B6:B51) every day i need to add one coloumn in B5. however it should automatically calculate this week as B5:B52, but B5 is not getting calculated.. Thankyou
A B C
1 Start Finish Days Open
2 12/10/2014 12/13/2014 3
3 12/15/2014 12/16/2014 2
4 12/16/2014
I am using =NETWORKDAYS(A2,B2) for days open
How can i modify the above formula so it will pickup Todays's date if there is no date in B5.
Can anybody help ASAP with that.........
I am trying to count two criteria. One column is counting all "New" occurances if column two is equal to a range of 15 different zip codes. I know I can countifs (a2:a20,"NEw",....) but the second criteria is d2:d20,g1:g16. I have put G1:g16 in as an "or" condition but keep getting a "0" count. Basically I want the thing to countif a2:a20 is new and d2:d20 "is one of" several different numbers. I was trying to do it without a lengthy formula referencing each number in its own countifs formula. Can you help?
i want to count if enter one score (numbers) for one day for one employee and need to count if enter next whenever coming days that figure need to added
Is there not an easier formula to use to count all the cells within a column with a value that is a multiple of 5, other than:
=COUNTIF(X4:X41,5)+COUNTIF(X4:X41,10)+COUNTIF(X4:X41,15)
Listing every multiple of 5 is not even possible so surely there is another way??
PLEASE help if possible......somebody....anybody? Thanks! :)
let's say i have 200 names in column with some names repeated in the list. How can i get the count other than 2 specific name?
dear team
my problem is tat a cell contains a value in general format as for eg; 2085 when i count using this countif formula the value does not get matched up and doesnt return 1 to me simply as zero
When i entered into that cell it contains some extra characters as space if i try to delete that space and put enter the last value i.e from "2085" 5 has been truncated and im left with 208
can u pls help me
Col.A Col.B
1 A
2 B
3 C
4 A
5 D
6 C
7 A
8 B
9 D
I would like to extract from above table in a separate table like bellow the corresponding values with comma.
Col.B Col.A
A 1, 4, 7
B 2, 8
C 3, 6
D 5, 9
Thank you in advance!
i have 500 account number
how to search double
Dear Sir/Mam,I wanna to count objects in different cells of the same row/col., I have try so many defferent formulas,but I fail to do so,Help me.
My Question is to add defferent cells of same row/col.
Example: Count objects in C1,C5,C10,C15..........,there is no range like c1:c15,so plz help me.
Hi,
I have a spreadsheet i am trying to count how many times a value (i.e. 3) occurs within cells E8:G380 when the text value in column D is ABC. If I use: =COUNTIFS(D8:D380, "ABC", E8:G380, "3") it returns a value error. Excel also only allows me to use two countif or countifs functions per cell.
Any help is appreciated!
Thank you!
Hi Elodia,
All ranges of the COUNTIFS function must be of the same size, i.e. must have the same number of rows and columns. You can use the following formula instead:
=COUNTIFS(D8:D380, "ABC", E8:E380, "3")+ COUNTIFS(D8:D380, "ABC", F8:F380, "3")+ COUNTIFS(D8:D380, "ABC", G8:G380, "3")
Hi, Looking at the COUNTIFS with multiple criteria for dates....
Is there an adjustment I can make to this formula.....
=COUNTIFS(C2:C9, ">=6/1/2014", C2:C9, "=C1","<=C2"
So, translation  "count everything here, that match this, between this date and that date".
I need this to make it more user friendly than expecting people to manually adjust a formula in a cell.
Either in the formula or with VBA?
Thanks!
That last post missed a whole chunk of something I wrote so it now probably doesn't make sense...
I meant: From this
=COUNTIFS(C2:C9, ">=6/1/2014", C2:C9, "=C1","=C1","<=C3")
Hope that makes more sense and posts what I wrote.....
Ah. no it didn't. Waste of time!!
Jamie,
Sorry for this, none of the blog engines we've tried is perfect.
As for the formula, you should not use cell addresses inside the quotes, otherwise Excel interprets them as mere text strings. For example, instead of "<=C1" you write "<="&C1.
Try the following formulas:
=COUNTIFS(C2:C9, ">=6/1/2014", C2:C9, C1)
=COUNTIFS(C2:C9, ">=6/1/2014", C2:C9, "<="&C1)
Hi
I hope you can help me. I am trying this formula as follow.
=COUNTIFS('Sheet1'!$AV:$AV,""&"",'Sheet1'!$AR:$AR,"*Feb*") +
COUNTIFS('Sheet2'!$AV:$AV,""&"",'Sheet2'!$AN:$AN,"*Feb*")
I need to know contacts per month. If AV is not empty and AR in sheet1 / AN in sheet2 have a month, count it( they could be empty, so count only the ones that have months on them ). As simple as that.
It doesn't return any value when I use ""&"", or "*".
The format of both is "text" and the range as is the same.
It is not working and I can not see what I am doing wrong.
thank for helping!!
Hi Sara,
Here you go:
=COUNTIFS('Sheet1'!$AV:$AV, "<>"&"",'Sheet1'!$AR:$AR,"*Feb*") +
COUNTIFS('Sheet2'!$AV:$AV, "<>"&"",'Sheet2'!$AN:$AN,"*Feb*")
For more info about blanks /nonblanks, please see:
https://www.ablebits.com/officeaddinsblog/2014/07/02/excelcountifexamples/#countifblank
I am working on a spreadsheet with a basic countif formula thats working well for me. When I add a filter to the worksheet in column A and filter by one item (e.g Apple) the formula does not autmatically updated. Please help.
Hi Sham,
The point is that the COUNTIF function processes all cells within a specified rage, not only those that are visible at the moment.
1202 13
1401 110
2107 121
1108 140
1111 154
1103 210
2102 812
2103 1103
1208 1104
1014 1105
1107 1111
1105 1154
2308 1204
1206 1210
n/a 1231
1154 1401
2305 2101
1306 2102
2101 2103
1204 2107
1201 2201
1210 2302
2302 2308
1104 (blank)
1106
2104
1301
1303
1402
2201
2309
1205
I have two column's of number.
Column1 is actual numbers
Column2 is numbers that have been input manually
I want to use a countif function to tell me how number in Column2 are not in Column1
Hi Richard,
The task requires creating a helper column, say Column C. enter the following formula in C1 and then drag it down to other cells:
=IF(AND($B1<>"",COUNTIF(A:A,$B1)=0), "Not in Column 1", "")
After that, you can use the below formula to count how many numbers in Column2 are not in Column1:
=COUNTIF(C:C, "Not in Column 1")
I am trying to count the amount of cells that list "Baltimore" in column H and the Agents name in column F. So basically how many lines in a sheet that contain a name AND a particular client.
I tried =COUNTIF(Eval_Data!F:F,BRL_Agent_Summary!C4)+COUNTIF(Eval_Data!H:H,”Baltimore”)
I fount that this will count the agents name but every instance that agents name appears and also if Baltimore appears.
I also tried =COUNTIFS(Eval_Data!F:F,"C4",Eval_Data!H:H,”Baltimore")
But it isn't counting the cells I want. It is bringing back 0 when I know there is data. I'm not sure what is wrong.
Hi Joanna,
Try the following formula:
=COUNTIFS(Eval_Data!F:F,BRL_Agent_Summary!C4,Eval_Data!H:H,"Baltimore")
This time with name...
Hi!
I need to be able to count the number of columns that have a date that is less than 11 days old. I understand I could do this using the Today() function, but it keeps hanging. I currently have =COUNTIFS(Infrastructure!$A$2:$A$2000,"Infrastructure",Infrastructure!$C$2:$C$2000,"<11"(&TODAY()$C2)
Where infrastructure is the name of the tab to go to, the first criteria says if the cell is 'Infrastructure', then the problem is the last calculation. I want to count the number of occurences where today minus the date in cell c2 is less than 11. CAn you help?
Hi Jennifer,
If my understanding is correct, you need just need to tweak the formula a little bit:
=COUNTIFS(Infrastructure!$A$2:$A$2000,"Infrastructure",Infrastructure!$C$2:$C$2000,">="&(TODAY()11))
I am trying to count the number of Army in one column that is due to depart between a date range of 90 days in a separate column. I have tried =COUNTIF(I:I,="ARMY")+COUNTIF(Y:Y,">="&TODAY()+1)COUNTIF(Y:Y,">"&TODAY()+90). I get the correct number for the dates in the range, but doesn't exclude Air Force, Marines, and Navy. I hope you can help. Thank you
Hi There I wonder if you can help me with COUNTIFS for text Values:
I have NATIONALITY in row G, and TYPE OF VISA in row J,K,L,M,....(contains "1"/symbol "x" as value).
I use =COUNTIFS(G12:G117,"=NATIONALITY",J12:J117,"1",......) and it works.
now if i want to apply the formula to a new items, how is the fastest way to do that?
Hi i need to count values in a colum condicioned by another colum, just like a i have XX,YY and ZZ in colum A and Values in Colum B i need to say cout at colum B if at colum A is YY how can i do it? Need help
Is there a way to count the number of instances in a column based on a criteria and then display the results not as a number, but as a list of data from the cells that meet the criteria?
Such as:
Lenovo User 1 Serial#
Lenovo Inventory Serial#
Apple Inventory Serial#
Apple User 2 Serial#
Apple Inventory Serial#
Microsoft User 3 Serial#
=COUNTIFS(A1:A6,"Apple",B1:B6,"Inventory")
But I would like to display the rows where the data meets these criteria, instead of the number of instances.
Desired display in a separate part of the spreadsheet, while not deleting the original cells.
Apple Inventory Serial#
Apple Inventory Serial#
Thank you very much for any assistance, positive or negative.
Timothy Scully
Hello Miss Svetlana Cheusheva,
I want ask you about How do I Sum or ets in one cell. I have 2 cell (A1)(A2), in cell one(A1) have number that I enterd, an other cell (A2) I have condition is that: if A11.000.000*3. It means between 2 Numbers that I imagin
I want function alike this: =if (A11.000.000;A1*3;"No Number")
Hi Svetlana,
I have two strings FirstRow & LastRow defined as address ranges
How can I use CountIFS formula to count number of zeroes between these two ranges.
I am trying to use the COUNTIFS formula
I want to count the numbers of different interest rates in one column and produce a result if it satisfies the criteria that it belongs to a field called MN (there are various other fields..
eg COUNTIFS(('PD11'!$H$8:$H$177,"<6%"),('PD11'!X8:X177,"=MN"))
Help!!
Thanks for Guide.
Svetlana Cheusheva you are so beautiful
Hi,
please help me count the same value from different cells in the same row. The cells I need to be able to choose.
What I need to calculate is the attendance for each student but on two different occasions. So I will have to choose every second cell and thus it can't be a range.
Sun. Dec 21 Mon. Dec 22 Tues. Dec 23 Wed Dec 24 Thurs. Dec 25 Attendance
RW CS RW CS RW CS RW CS RW CS
P P P A A A P P P P 0
P P P P P P P P A A 8
As you can see, it is Communication Skills and Reading and Writing Skills that the student attends on two different times.
I tried
=COUNTIFS(D8,"=P",F8,"=P",H8,"=P",J8,"=P", L8,"=P")
and many other formulas with no success.
Hi Svetlana,
Please help me get a formula for my query..
In an excel sheet having 3 columns 'Date', 'Time' & 'Amount'. I need a formula which will add only those amount in the 'Amount' column, where the Date is from A to B & Time between and equal to C & D.
Please can you help me.?
Just to add to above query, A & B are 2 different dates, and C is a time in A & D is a time in B.
Hi, Firstly, I thank you for providing this excellent service. You are doing a great service.
I need some help with this. I want to send/link cells or rows or columns to another worksheet/book when the columns meet certain criteria.
For example, if the date and time attended in column D and E is later than a day or 10 hours compared to column B and C, and link those data that match criteria to another sheet.
Thank you and hope to hear from you soon.
Regards.
Joseph
I have a problem regarding counting multiple criteria and the problem is counting only the "year" my data date like 29/05/2014.
=COUNTIFS('Rabbit Inventory'!$F:$F;"Buck";'Rabbit Inventory'!$E:$E;$AG5;'Rabbit Inventory'!$Q:$Q;"Purchased"; 'Rabbit Inventory'!$R:$R;YEAR(2014))
Hi any one can help me on the following
Peter 02/01/15
Hari. 05/01/15
Sam. 12/01/15
Peter. 13/01/15
=countifs(a:a,Peter, b:b,"="&today3) but I suppose to get 1 ryt if today date is 13/01/15??
Hi,
Can you send me the sample excel, how to calculate & I need the count of net working day
Example: In a monthly if I used to get "n" number if volumes... I want to calculate the ageing.... But I don't want to calculate Saturday & Sunday...
Can you support me to get the the format sample through mail
DATE Service sum(totalHits) sum(Successful) sum(Dispatched) sum(Failed)
11/1/2014 MPESA Uploader 373 285 =COUNTIF:(sheet1:status1,"<=103", sheet1:status1,"<=202")
11/2/2014 MPESA Uploader 227 158 1
11/3/2014 MPESA Uploader 285 284 1
11/4/2014 MPESA Uploader 294 294 0
11/5/2014 MPESA Uploader 301 301 0
11/6/2014 MPESA Uploader 253 251 2
11/7/2014 MPESA Uploader 274 273 1
11/8/2014 MPESA Uploader 283 281 2
11/9/2014 MPESA Uploader 2 2 0
11/10/2014 MPESA Uploader 268 268 0
11/11/2014 MPESA Uploader 7 7 0
11/12/2014 MPESA Uploader 58 57 1
11/13/2014 MPESA Uploader 243 243 0
11/14/2014 MPESA Uploader 195 195 0
11/15/2014 MPESA Uploader 259 259 0
11/16/2014 MPESA Uploader 299 296 3
11/17/2014 MPESA Uploader 291 289 2
11/18/2014 MPESA Uploader 342 342 0
11/19/2014 MPESA Uploader 335 335 0
11/20/2014 MPESA Uploader 394 393 1
11/21/2014 MPESA Uploader 304 302 2
11/22/2014 MPESA Uploader 295 295 0
11/23/2014 MPESA Uploader 308 308 0
11/24/2014 MPESA Uploader 625 617 8
11/25/2014 MPESA Uploader 361 361 0
11/26/2014 MPESA Uploader 295 295 0
11/27/2014 MPESA Uploader 356 356 0
11/28/2014 MPESA Uploader 262 262 0
11/29/2014 MPESA Uploader 227 227 0
11/30/2014 MPESA Uploader 358 358 0
I need to compute the sum failed
Hi Svetlana,
I hope you may be able to help?1?
I am designing an attendance spreadsheet for a school using a set of alphabetic codes for attendance, for example  P Attended, L  Half day, A  Absent.
I also have a summary box for reporting purposes and need to count attendance and absence based on gender (M/F) and grade(k  10) and ethic background (I,NI) (a column for each).
Therefore I need to count the number of Ps, or A's that exists in the attendance data in the row IF gender=M and grade=K and ethnicity=I
I also want a value returned if the cell is still empty.
I am have tried using : =COUNT(IF((C6:C19="K")*(E6:E19="M"),F6:BM19="P"))
But this returns will a nil value.
Let me know if I need to send through the spreadsheet.
Thanks in anticipation.
Jessie
Warm regards,
Jessie
Hi,
I'm trying to get excel to find numbers ending in a 7 (i.e. 47, 57, 67) and then need to tell the computer is raise the number to 47 to 50, 57 to 60 and 67 to 70. Please can someone help please?
Thanks
Sue
=IF(C7>0;"OK";"BAD") this one is working, but if I put region
=IF(C7:G7>0;"OK";"BAD")or =IF((C7:G7)>0;"OK";"BAD") excel give me VALUE
Hi! For my previous question I get answer! But I have other one  if I want to count only "OK" results for IF function for example H7:H19, what function do I need to use?
I'm trying to calculate the total number of individuals who meet a certain criteria, but I can't seem to make the "COUNTIFS" formula fit what need. I have a column, EDU and here is some sample data. I want all data to count as 1 except "HS" which should not be counted. The purpose is to calculate the number of individuals who have postHS education.
Do you have suggestions on how to make the formula work with multiple conditions?
EDU
HS
BA
BS
AA
AS
HS
HS
BA
AA
Hello Shannon,
Please use the following formula:
=COUNTA(A2:A20)COUNTIF(A2:A20,"HS")
Where A2:A10 is the EDU column
Hello, i thank you for helping others unconditionally. I have asked for help and posted twice. Number 177 is the only one i can find. I understand that you have extremely high traffic volumne. But i was just wondering if i am able to get help here or even my request for help is misssed. thank you again for the good works you are doing for all persons and society.
I've been reading around and I apologize if I've missed this(the information has me a bit...confused and I overwhelmed)
I am trying to keep track of some projects between me and a person.
Under one column of cells I have the type of project and in another I have who did it.
I have somewhere a list of how many of each TYPE of project is done and beside that who it was done by so I can keep track of the giant project list.
So say...the C columne says "Project type 1" or "Project type 2" and the D column has "Done by Susy" or "Done by Dave"
I was keeping track of "Project type 1" in one cell. So it's counting how many we did of that type very well.
Then I did the same for Project type 2.
But when I got to the point of: "Project type 1 done by Susy", I tried to put in a formula to count how many times Project1 was done by Susy and another counting how many times it was done by Dave.
...cept it doesnt work. And I'm not quite sure how to do it. I got confused reading the information because it seems to always be consistent info in different columns or different info in the same column and rarely did I find "Only count if both these conditions are met"
Is that..even possible? Neither have value numbers. They use words rather than numbers(obviously they arent actually called 'Project 1' and '2'.
Gah..just writing gets me confused.
1 JAMES SHJ MALE MALE FEMALE KIDS
2 RICKY DXB MALE SHJ Formula ? Formula ? Formula ? 7
3CHRISTINA SHJ FEMALE DXB Formula ? Formula ? Formula ? 2
4 LIBA SHJ KIDS AUH Formula ? Formula ? Formula ? 2
5 JOSEPH AUH MALE 11
6 ZARINA SHJ FEMALE
7 WINSTON DXB MALE
8 JENNIFER SHJ FEMALE
9 MINNU SHJ KIDS
10 ARASH AUH MALE
11 MAHIR SHJ MALE
uum yeah, that was very clear how's going to Dubai and sharjah I suppose?
Hello Eddie,
I am sorry, your data look distorted in the comment. For us to be able to assist you better, please post a small sample workbook with your data on our forums and describe in detail what you want to count. We will try to help.
Thanks Svetlana, you just saved my life and a lot of typing :) awesome post
Hi,I Am making a file for my poject.I am facing the some problem in xls pls do help in that. I have to add the farmula that How may rows is on Time and Early only for NAGAR as given the example. total should be come in one cell for both On time+Early of the NAGAR name.
NAGAR On Time
RPRAD Early
BBIAD Late
CCUBG Late
NAGAR On Time
RPRAD Late
BBIAD Late
CCUBG Late
NAGAR Early
RPRAD Early
BBIAD Early
CCUBG Early
NAGAR On Time
RPRAD Early
BBIAD Late
CCUBG Late
NAGAR Early
RPRAD On Time
BBIAD Late
CCUBG Late
NAGAR On Time
RPRAD Late
BBIAD Late
CCUBG Late
Thank you.
Please use
=countifs(A2:A1000, "NAGAR", B2:B1000, "On Time") + countifs(A2:A1000, "NAGAR", B2:B1000, "Early")
Grate. Thank You.....
How to count the number of values in a cells separated with a coma.
eg.
1
4,5
6
The number of values should be 4 in the 3 cells. Count function only counts 2 values.
Please use this array formula (press Ctrl+Shift+Enter to complete it):
=SUM(LEN(A2:A10)LEN(SUBSTITUTE(A2:A10,",",""))+1)
Where A2:A10 is your cells with data. Please note that the formula will return wrong results, if your cells contain some text, e.g. "1,text,text20"
How do you count two different 'tasks' that are in the same column but only if they are marked 'active' in another column.
I seem to have botched this up & have ended up with this mess:
=COUNT((IF('Combined Project & Milestone'!F:G,"DESTRUCTIVE TESTING")),+OR((COUNTIF('Combined Project & Milestone'!F:G,"INITIAL REPORT")),+(COUNTIF('Combined Project & Milestone'!F:G,"Active"))))
REALLY hope you can help soon.
Thanks
Please disregard the above  I worked it out.
Thanks Alexander, but the formula counts empty cells also in the column. How to count only the cells with values, neglecting empty cells?
=SUM(LEN(A2:A10)LEN(SUBSTITUTE(A2:A10,",",""))+(NOT(ISBLANK(A2:A10))))
Thanks again Alexander.
I am ubable to count below data
Name Time
A 12:03:00 PM
B 12:17:00 PM
C 12:00:00 PM
D 12:44:00 PM
E 5:01:00 PM
f 12:21:00 PM
A 12:03:00 PM
B 12:17:00 PM
C 12:00:00 PM
D 12:44:00 PM
E 5:01:00 PM
f 12:21:00 PM
A 12:03:00 PM
B 12:17:00 PM
C 12:00:00 PM
D 12:44:00 PM
E 5:01:00 PM
f 12:21:00 PM
NAME Count
A
B
C
D
E
f
Please help
Hello there, I hope you can help with the following worksheet with multiple tabs.I Maybe COUNTIF could work but i really can't figure it out how.
Code Account Title Jan05 Feb05
1000 Advertising $750,75 $
2000 Office Equipment $ $
3000 Printers $ $
1st I would like to set the date in each column as 16Jan15Feb, 16Feb15Mar etc., so that it sums up the invoices amount for each code of only those dates, at the moment it only adds each full month, and i can't seem to find a way to change it without errors. This is how the formula is shown:
=SUMIF('Itemized Expenses'!$J:$J;"="&($A6&TEXT(C$4;"mmmyy"));'Itemized Expenses'!$E:$E
2nd  I would like to add another formula to this, which also adds the amounts within those dates depending on methods of payment and display it in another column, that is the total paid in cash, total credit card, total direct debit, etc.
Thank you very much in advance for your help.
Lotus
I have two columns, column 1 Dates, column 2 Names.
01/02/2015 ARYA
01/02/2015 ARYA
01/02/2015 ARYA
02/02/2015 ARYA
From the above example, I want the count to reflect 2, in other words I would want it to count as a single occurrence per day i.e., 01/02  one occurrence & 02/02 one occurrence. Please help me with this. Please help me without date ranges as i need to use it for the entire year for different names.
Hi Alexander,
I guess even my query has been missed out. 176 is my number. So please if you can help me out.
Thank you.
Excellent! Thanks for the clear explanation. This really helped.
H
Hi!
How can I use countifs and vlookup together?
Thank you.
Hi Lea,
We have a tutorial on using VLOOKUP with SUMIF and SUMIFS:
https://www.ablebits.com/officeaddinsblog/2014/08/05/excelvlookupsumsumif/
COUNTIFS and VLOOKUP work in a similar way.
If
Names Sales Profit
Kim 100 10
John 250 25
Tim 300 30
Tom 325 33
Sheetal 350 35
Nidhi 375 38
Shyam 400 40
Rahim 425 43
Ali 375 38
Sheetal 325 33
Tom 275 28
Seema 225 23
Tom 175 18
Kim 150 15
Sheetal 200 20
John 250 25
Tim 300 30
How use 'countif'formula for
Q : Excluding Kim there are how many people in the list?
ok i am trying to make spread sheet that tracks the units average score for said events, but I don't wont to enclude the certain personal that are on a profile preventing them to take a certain event.
So I guess the question is there a way to put an AVERAGE Formula and a COUNTIF Formula in the smae cell, in order track all this type of information or am I too far out in Left Field this function is impossible?
Thanks for your time and answer/assisstance with this issue I have.
Recipt Credit
Cash Book Bank
564 914308
30,000 30000
30000 4654
43660 30000
1245247 19344
30000 465654
Recipt Credit
Cash Book Bank
564 914308
30,000 30000
30000 4654
43660 30000
1245247 19344
30000 465654
I want to know matching values in col a & b
e.g there are 2 values (30000) in col b
it should show corresponding 2 values in col A & not 3
values as appeared in col a
Hello Svetlana, I really hope there is an answer to this I've tried several ways but it doesn't seem to work. Assuming I have data in A1:A3 and the data totals are in A4 & A5. The data that needs to be added across for example are 2/2, 3/2, 4/1; the left number is the attendance for adults and right side are the children. The data total for A4 would = 9 and A5 = 5. Does it matter if the data in one cell is seperated by either symbol? Ex. 2/2 or 2:2 or 2+2? I need HELP how to add the data across but keeping left side total from right side. Is there a simple solution?
Hi. I have an excel spreadsheet with first names in column A and last names in column B and other information in columns C and on. I access and change this file daily. I need to know when a specific person shows up 6 or more times and then I have to move the data to another sheet. I already have it sorted by last name, column B, but I still have to go through and count each individual name to make sure there are 5 or less or I have to move them. Is there a way to use COUNTIF(S) to let me know if there are names that appear more than 5 times and maybe even what the names are? I have to go through this process every day and it would save me a lot of time looking through the 800 rows.
Hi,
A B E F
1 Santosh 10 Raj
2 Raj 5 Sajan
3 Sajan 8 Ankit
4 Ranjan 9 Tara
5 Sameer 10
6 Priyanka 4
7 Ankit 8
8 Tara 6
in the above spread sheet there is some names from A1 to A8 consequent the figures on B1 to B8,
My question is Cell E1,2,3,4 i mentioned few names in jumbling way ok, if those names are in the list of A1 to A8 then i want the consequent figure (which mentioned at B1 to B8) to be visible @ F, ok
guide me with the formula...............
Here you go...
if you want name with consequent figure..
=IF(ISERROR(VLOOKUP($C2,$A$2:$A$9,1,0)),"",$C2&VLOOKUP($C2,$A$2:$B$9,2,0))
If you only want the figure then...
=IF(ISERROR(VLOOKUP($C2,$A$2:$A$9,1,0)),"",VLOOKUP($C2,$A$2:$B$9,2,0))
Greetings from South Africa!
I am creating a database to count four different types of clinical personnel receiving training on medical male circumcision. Information will produce two basic spreadsheets. One counts the number of trainees per training type (8) and segregating each clinical personnel type per month (1,350 cells). The other segregates each clinical personnel type and training received per province (state) (9,350 cells) Thanks to information from your website I created formulas that are working, but my question is how can I copy and paste the formulas that automatically change the month I want to capture. An example is below where 01 represents January. How can I copy and paste the formula setting the month to change automatically to 02, 03, etc?
=COUNTIFS($A$25:$A$5000;"01";$G$25:$G$5000;"D";$I$25:$I$5000;"S";$L$25:$L$5000;"EC"&"")
Thousands of Thank yous if you can help.
i have a problem
that is i have a range of data from A2:A308 i want to place a criteria that if the data in the range falls =8 and =13 it should display tertiary
i have a range of data from A2:A308 i want to place a criteria that if the data in the range falls less than 8 it should display primary if greater than equal to 8 and less than 13 it should display secondary and if greater than equal to 13 it should display tertiary
Hi Zeta,
I think this will help...
=IF($A1=13,"Tertiary","Secondry"))
=IF($A1=13,"Tertiary","Secondry"))
Part code Qty
1 12 Part number '1' total
2 85 Part number '2' total
1 47 Part number '3' total
4 69 Part number '4' total
3 32
1 12
4 45
2 12
3 36
1 58
4 51
2 78
Please help in summing the total of each part number...
Hi there. I need help with Countif. So I have two columns, both are dates.
1. Target Award Date
2. Actual Award Date
I need to count how many actual award dates were before the target.
For example.
Target Award Date is 2/10/2015
Actual Award Date is 2/8/2015
This should be counted
If the Target Award Date is 2/11/15 and actual award Date is 2/15/2015, then it wouldnt be counted.
Hello,
I have two sheets. One is called "Rollup" and the other is called "Data". On the "Rollup" sheet, I am using column "E" for my formula and what I'm trying to do is count anytime whenever the name in column "B" of the "Rollup" sheet appears in D2:D39228 of the "Data" sheet. So, on the "Rollup" sheet for example, cell E2 will have a COUNTIF formula that asks how many times the name in B2 of "Rollup" sheet appears in D2:D39228 of the "Data" sheet. Does that make sense?
Hi,
I have 3 columns containing the following info regarding subscription period to a website: User, Service Start Date, and Service End Date, with the dates ranging from 2013 to 2016. I'm having some trouble using both COUNTIF and COUNTIFS to get how many Users (A2:A617) are active on a particular period (e.g. Jan2013 has 21 active subscribers, Feb2013 has 24, etc.).
I would really appreciate your help.
Thank you.
Hello!
I have two columns I have to match. I thouught I can use COUNTIF function to calculate the frequencies from column B matching it to a value in column C (as there are more then one values in column C, so I have to choose to calculate the frequencies of each of them separately). How do I do that? I tried all the combinations but I don't think I understood the above explaination correctly.
Long story short, thank you, Svetlana, for this post! Your explanation and examples using COUNTIFS were just what the doctor ordered!!
Hello Svetlana,
I'm not sure if I can use COUNTIF/COUNTIFS for my following query. Perhaps the solution may be SUMPRODUCT as I have tried. I'll wait for your advice.
I have a column (A) going down 1000 rows. Each cell has either "Inside" or "Outside" typed in them. The next column (B) has dates in each cell (also going down 1000 rows). I need to search Column A and if the cells contain the word "Inside" or the word "Outside", total the number of dates in Column B that are less than or equal to(<=) Today(). There will occaisionally be blank cells in Column B. I have come up with this formula but it still counts the blank cells in Column B :
=IF(ISBLANK(A:B),"",SUMPRODUCT((B:B<=TODAY())*(A:A="In")+(B:B<=TODAY())*(A:A="Out")))
Greatly appreciate any help.
Regards,
Leela.
Hello again Svetlana,
Apologies. I neglected to type the words "Inside" and "Outside" in full within the formula in my last post. Sorry!
Regards,
Leela.
hi, i need your help:
can i incloCan I include function within the COUNTIFS function like the following example:
=COUNTIFS(D4:AH4,"COUNTA(D4:AH4)")
It does not give me correct answer. Please help me.
thank you.
Hi Ghazi,
If you are looking for a way to count nonblank cells in D4:AH4, you can use the following formula instead:
=COUNTIF(D4:AH4,"<>"&"")
I'll explain what I have:
I created color function to count cells with certain color and now I want to count celles that has value and certain color at the same time
Ghazi,
I am afraid I cannot help in this case because I do not know how your function works. BTW, we also created a function to sum and count cells by color, you can find it here:
https://www.ablebits.com/officeaddinsblog/2013/12/12/countsortbycolorexcel/
Hi There
I was wondering if it was possible to have 3 COUNTIFS formulas in one cell?
Hi Jake,
It is not possible to have several different formulas in one cell. However, you can put 3 different conditions in 1 COUNTIFS formula.
Hi There,
I am not able to find the right countif formula to count "AA" in next column with reference to another column!! For example in Column 1 I have different Cable types and in next four columns whether is applicable or not I had mentioned with AA, BB, CC, DD. Here I have to count how many "AA" are available against one type of Cable. Could any one please help me out??
Hi Kardon,
If column 1 (let's say column A) contains unique cable types only, i.e. a single record for a certain type, you can use the following formula:
=COUNTIF(B2:F2, "AA")
Where columns B  F contain AA, BB, CC, DD. The formula counts the number of AA in each row.
If you have multiple instances of the same cable type in column A, then the best solution I can come up with is this lengthy formula:
=COUNTIFS(A2:A8, "cable1",B2:B8, "AA") + COUNTIFS(A2:A8, "cable1",C2:C8, "AA") + COUNTIFS(A2:A8, "cable1",D2:D8, "AA") + COUNTIFS(A2:A8, "cable1",E2:E8, "AA") + COUNTIFS(A2:A8, "cable1",F2:F8, "AA")
Where "cable1" in the cable type, 2 is the first and 8 is the last row with data.
Is there a way to combine a COUNTIF with a MIN function? I have three different amounts for each client, and I need a count for how many times the first value was the least of the 3, how many times the second value was the least of the 3, and how many times the third value was the least of the 3. Any help is greatly appreciated.
i have a sheet in which a column shows percentage of attendance i have to provide the formula for next column to give the values for the percentage..for example
if the student have percentage in between 75 to 80 i have to give 01 marks and 81 to 85 then 02 marks ,86 to 90 03 marks etc.the how to write formula?
Hello MANSUR,
You can do this using nested IF functions. Please see the following example, which is very similar to your task:
https://www.ablebits.com/officeaddinsblog/2014/12/03/exceliffunctioniferrrorifna/#usingmultipleif
PLZ REPLY FAST
Great article; maybe you can help me. I have a named table with over two dozen date columns and one column of text.
I want to count the number of dates within a date range and that have a specific text value
date1 date2 ... date26 Text
Logically this would be:
If Text = "Major" and Date = upperLimitDate then count it.
I can't get COUNTIFS to do this.
My example was cut off, let me try again...
If Text = "Major"
and Date GE lowerLimitDate
and Date LE upperLimitDate then count it.
Henry
Hi Henry,
You can use a formula similar to this:
=COUNTIFS(Table1[text], "Major", Table1[date], ">4/10/2015", Table1[date], "<4/20/2015")
Where [text] is your Text column, [date]  column with dates, 4/10/2015 is lowerLimitDate and 4/20/2015 is upperLimitDate.
Evening Svetlana,
I have used your posts many a time to gain answers for my questions and until now was always able to find an answer. I have an issue that I cannot figure out. What I am looking for is a way to count the number of records that meet multiple criteria. My issues comes when adding in the multiple fields. I have a project list with Start dates, Team name, Department and Type of project. Above that I have a list of the start and stops for each week of the year.
What I am trying to do is count the records where the start of the project is between the start\stop date for the given weeks but only when the project team is a given name (Blue, or Green) or the Department is a given name (SRT). The fields are exclusive but complimentary so I need to add an "or" statement but cannot get it to work.
Hi Thomas,
To count with the OR logic, you need to add several SUMIF or SUMIFS functions, for example:
=COUNTIFS(A2:A10, ">=start date", A2:A10, "<=end date", B2:B10, "team1",) + =COUNTIFS(A2:A10, ">=start date", A2:A10, "<=end date", B2:B10, "team2",)...
found the answer. I need to test criteria on each and sum:
=COUNTIFS(date1,text)+COUNTIFS(date2,text)+...+COUNTIFS(daten,text)
Hi,
i want to count if the column contains a date(unconditioned)
Excellent information dear...!
This information made my day.
Once again, thanks a lot.
I have a problem with a "countifs" statement and I was wondering if anyone can help.
One of my columns has cells where the content is <=5 as the value and it isn't a calculation. I want my countif to count how many instances of those, but when I put it in my countif calculation it sees the <= as part of the calculation and produces the wrong results. Is there any way to see <=5 as a value instead of a calc in that cell?
Any help is appreciated.
Thanks,
Sharon
Hi Sharon,
I cannot figure out a way with COUNTIFS either. But you can use the following array formula instead:
=SUM((A1:A100="<=5"))
Please remember to press Ctrl+Shift+Enter to enter it correctly.
Hi, I'm trying to calculate the following.
IF a cell in B2:B19 contains the word "Food" THAN I want the numbers in corresponding cells in E2:E19 to be added up.
For example B2 and B5 contain the word "Food". Their corresponding cells E2 and E5 contain "E2: 8 and E5: 10", the total of the formula would be 18.
The B column contains various Types in text and the E column contains different amounts of that types in numbers. I want to see the totals of every type without having to manually choose E2+E5 etc.
I hope this makes sense.
I found the answer
=SUMIF(B:B,"Food", E:E)
hi! I have a column 3400 reference value (persons id) and want to count how many times listed in an other column (trips in taxi). can you help me? thanks
Hi Paula,
Amusing that your persons id column contains unique values only, you can use a formula similar to this:
=COUNTIF($B$2:$B$1000, $A2)
Where A is the id column (row 2 is the first row with data) and B is the other column (trips in taxi).
Thanks worked great for me!!
I'm trying to use a COUNTIF formula, where the criteria is a cell reference to a formula result.
Is there anyway the COUNTIF formula will pick up the formula result value ?

A,B, C
1)7,1,"=IF(A1>0,A1+B1"
Result in "C1" will be "8"
Now I would like to use value "8" as "criteria" in the COUNTIF function.
=COUNTIF($AA$1:AA$100,"=C1")
But it won't pick up the "8" value but returns "0"
Thanks in advance...
Hi Mark,
When you enclose cell references in quotes, Excel interprets them as text strings. Once you remove "", your formula will work fine:
=COUNTIF($AA$1:$AA$100, $C$1)
You can also put it like =COUNTIF($A$1:A$100, "="&C1)
1st 2nd 3rd 6th 7th 8th
7:59am None
Scheduled Closed 8:02am No Show 8:03am
I need a formula to only count the times which are equal to or greater than 8:00am. i need it to only count the times.
Hi Pam,
=COUNTIF(A1:D100, ">="&TIMEVALUE("8:00 am"))
Where A1:D100 is your range.
Box ID Sku Sku Sku Sku Locations
A 123 123 456 123 Shop
b 123 456 789 123 dock
C 789 456 789 456 whse
D 123 123 store1
Search sku 123 box ID locations records
a shop 3
b dock 2
d store1 2
Base on the data given, how can I get the desired output?
Data:
Box ID Sku Sku Sku Sku Locations
A 123 123 456 123 Shop
b 123 456 789 123 dock
C 789 456 789 456 whse
D 123 123 store1
Needed result/output:
Search sku 123 box ID locations records
a shop 3
b dock 2
d store1 2
Average from a sum of cells.
Column A Column B
425 Current
365 Current
354 Not Current
142 Not Current
454 Current
Average total of only the ones that state Current, leaving out the Not Current and blanks.
Nevermind, found it.
=AVERAGEIF(A1:A7,"Current",B1:B7)
It was the middle that I was stumped on.
Oops wrote it backwards.
=AVERAGEIF(B1:B7,"Current",A1:A7)
Hi
I have 2 columns of data:
1st column:
Senior
Senior
Junior
Senior
Junior
Senior
2nd column:
50
60
30
70
40
50
I want to count how many Seniors have a figure < 60 in column 2.
The answer should be 2  how can I do this please?
Hi David,
You can use a COUNTIFS formula similar to this:
=COUNTIFS(A1:A100, "senior", B1:B100, "<60")
If i want to count "P" , "Late" and "Half Day" in same cell what will be the formula using countif formula.
Hi Runu,
To sum values with the OR logic, you need to add up 3 SUMIF functions, like in
Example 4. COUNTIF formulas for noncontiguous ranges (OR logic)
Thanks Svetlana,
and if I wanted to use a cell reference instead of 60 do I just use "<C1" for instance?
Excel COUNTIF and COUNTIFS have a peculiar syntax that is very confusing. In particular, when using cell references in criteria, you have to enclose the operator in quotes and add an ampersand before the cell reference, like "< "&C1
You can find more details in the "Ampersand and quotes in COUNTIF formulas" section of the COUNTIF tutorial:
https://www.ablebits.com/officeaddinsblog/2014/07/02/excelcountifexamples/#countifsyntax
Hello Svetlana,
I have a list of parts with different end connections, some of those parts have more than one connection:
0.5" NPT 1" NPT 0.25" NPT 0.75" NPT
A X
B X X X
C X X X
D X X X
E X X X
F X X X
Can i use the CountIF function to tell which parts have the most common connections?
Thanks
Karim
Many thanks Svetlana!
Hi, I keep getting a value error for the following formula. Column E contains data from a validation list so I am not sure if this is affecting the outcome. Column K has words written as RED, GREEN, AMBER. Essentially I want to know how may meet the criteria of both Live and Red.
The validation data is held in a worksheet entitled Sheet 3  cell range D5D9.
=COUNTIFS('Programme Risks'!E4:E944,"Live",'Programme Risks'!K4:K920,"RED")
Thanks in advance.
Hi Ritchie,
All ranges in COUNTIFS formulas must have the same number of rows and columns. So, I think changing the second range to K4:K944 should fix the problem.
HI,
I have 2 columns with multiple values. I want to count the unique No.of customer codes for each sales Agent. For instance, the value for Agent X shall be 2 (10 and 6).
Sales Agent Customer code
X 10
X 10
Y 25
Y 5
X 6
Z 10
Hi,
I am trying to count the number of times multiple values appear in a cell across a row. e.g
A1 4,5,8
B2  5, 31
This would add up to 5, but i can't find a formula that can calculate this.
Thanks in advance
EXPECTED ACTUAL DELIVERY STATUS
09/01/2015 07/01/2015 LATE
04/01/2015 31/01/2014 ONTIME
10/01/2015 06/01/2015 LATE
10/01/2015 LATE
30/12/2018
Hi,
I would like to ask what will be the formula if I want to have the status as "Late" or "Ontime" for the data table above. It will also show a "Late" status if it already lapsed the expected date even if in the "Actual Delivery" column is blank. It will also not categorize the status if the expected delivery column is blank once it is not yet past the due date.
Thanks in advance.
Hi Loy,
You can achieve the result by using nested IF functions.
To be able to suggest a proper formula, I need to understand the conditions better.
So, assuming that "Expected Delivery" is column A and "Actual Delivery" is column B, the formula returns:
"Late" if A2 is not blank and B2>A2, or if B2 is blank and A2 is greater than today;
"Ontime" if both A2 and B2 have dates and B2<=A2;
No status (empty string) if A2 is blank "once it is not yet past the due date". How do we know this? What is the due date?
The following formula works with the above logic, except that it does not check "once it is not yet past the due date":
=IF(OR(AND(B2>A2, A2<>""), AND(B2="", A2>TODAY())), "Late", IF(AND(A2>=B2, A2<>"", B2<>""), "ontime", ""))
I need to count multiple criteria. I have a spreadsheet where the counts will be housed. This spreadsheet is looking at another spreadsheet's data to obtain the counts. In column A the city will be located. There are 7 different cities in a column. In column B there will be the project names. There will be about 4 of these. Column C has the start date and Column D has the end date. I want my formula to look at column A and find Sacramento, then look at column B for only specific project name, then look at column D for the date range of 3/26/2015 to 4/25/2015 and provide a total count.
So for instance, Sacramento will have 4 projects listed so there will be 4 separate rows for Sacramento. One row for each project. But I only want to count two of the projects. How would I put this into a formula?
So if the project names are Red, White, Blue, and Yellow (so 4 rows for Sacramento). I want my formula to look at column A for Sacramento and count only if column B has Red and White and column D for the date range of 3/26/2015 to 4/25/2015.
Right now I have a formula that counts how many cells are within that date range for Sacramento but it is counting for all 4 projects. I want it to only include the date range for projects Red and White (column B).
Thank you for your time and effort.
Dear Svetlana,
I need to create a countif formula that will allow me to start counting in multiple spots.
I.E 0  0  0  0  100  100  0  200 I want it to count FOUR becuase the trend starts. I need this for multiple lines with different starting points.
You can treat this as says and months it was a new product so we didnt have sales in the 1st 4 months, but sales started on the 5th month and we had two months with 100 cases the 7th month we didn't have any sales and the 8th month we had 200cs.  I.E we averaged 100 cases per month. 400 cases in four months.
the next item may be
I.E 0  0  50  0  100  100  0  200
Please help. I have tried everything i can think of.
Hello Tom,
If my understanding is correct, you need a Helper Column to fulfill this task. For example, if your data are in columns A:H, then in cell I2 you need to place the following array formula (use CTRL+SHIFT+ENTER to enter it):
=COLUMNS(A2:H2)+1MIN(IF(A2:H2>0,COLUMN(A2:H2),9999))
Where A2:H2 are the cells with data. You will get the number of months from the sales start (4, 6, etc.).
Then copy the formula down across the column I.
After that you can calculate Average in the adjacent column using this formula:
=sum(A2:H2)/I2
Hi,
I have a bill wise sale data in which i want to count how many times a category is coming.
Setting is as follows:
Col A: Bill number (27000 + rows including duplicates, and 2000 + rows unique)
Col C: Time period of sale (4 time periods)
Col D: Product code/name (multiple)
Col H: Category (18 categories)
Objective is to find non duplicate unique bill count (Col A), for all 18 categories (Col
H) set in rows, and 4 time periods set in columns
Pls. help with a formula
Thanks
Anmol
Ms. Cheusheva
I have a good one for you, I need to take and if column B has the word ALSE I need to count the number in column C and post the resulting total number. Any Ideas?
Thanks for all the help
Hello Chris,
Please use one of the following formula:
=SUMIF(B2:B1000,FALSE,C2:C1000)
or =SUMIF(B2:B1000,"ALSE",C2:C1000)
(based on the content in column B  FALSE/TRUE or the word "ALSE")
For more information about the Sumif function please see here:
https://www.ablebits.com/officeaddinsblog/2014/11/04/excelsumiffunctionformulaexamples/
Thanks for the help works great.
Looking for a formula that will add cells if another cell matches a specific value.
Example:
A1=1, B1=2, C1=8, D1=60
A2=2, B2=4, C2=4, D2=80
A3=1, B3=1, C3=12, D3=60
A4=3, B4=2, C4=4, D4=120
A5=3, B5=1, C5=4, D5=40
A6=2, B6=2, C6=8, D6=80
What I'm looking for, is B7= the sum of the values for the B column that have a 1 in it's A cell for that row. B8= the sum of the values for the B column that have a 2 in it's A cell for that row, and so forth. This is sort of what I have been looking at, but have not been able to complete it. IF(A1:A6=1,B1+B3,??
Here is an example file: https://www.dropbox.com/s/3tbit1g63hjohw5/Book1.xlsx?dl=0
Thank you for any help.
Hi Brian,
You can use a SUMIF formula similar to this:
=SUMIF(A1:A6, 1, B1:B6)
Please check out the formulas for your real data here:
https://www.dropbox.com/s/l4lwnh86q0bt3ij/Book1.xlsx?dl=0
Hi,
I want to maintain my attendance register with conditions. Like if a employee took more than 4 days leave (continuously). automatically it should display straight to his name: warning
for example
Attendance Register
employee 1 : b1:af1 (31 columns for month)
If he took 4 days leave continuously then only result should show warning message.
If he took 1 day leave and works for 2 days and again took leave. no need to warn.
I just need if he took continuously 4 leave days with b1:af1, result warning !
Please help, Thanks in advance for responding to my queries
Hi,
Sort of complicated question. I have 3 columns that I am working with. All three columns are filled with "Y" or "N" telling me a yes or no answer.
I want to count if there is a "Y" in column B AND a "N" in column D for ONE single row and then add all those together.
This is what I have but it is not calculating it correctly, can you help?
=COUNTIFS(B2:B39, "Y", D2:D39, "N")
Hello there!
I am working on a tracking worksheet where I would like to create a formula in sheet #2 to be able to calculate a total count of a specific LETTER CODE on a SPECIFIC DATE. The data would be in Sheet #1.
Sheet # 1 has the following:
Column A  dates(Example 5/1/15, 5/2/15,etc)
Column B  Letter Codes (Example  C, AD,L,K)
Then I have Sheet #2. Here is where I would like a formula to show the total count of a specific LETTER CODE on a SPECIFIC DATE from sheet 1.
Any help or guidance is EXTREMELY appreciated!!!
Thanks in advance!!
A B C D E F G H
3000037398 0040 4
3000022099 0410 3.6 7.6 7.6
3000022099 0410 2.4 2.4 3 3
3000037398 0040 5
3000037398 0040 2.6
3000037398 0040 2.4
3000022099 0410 1.4 2.5
Hi
I would like to lookup the value / item in Column A which may appear more than once and then total up all the numbers from columns "B" to "H" relevant to the value in "A"
i.e the total for number 3000037398 0040 will be 14.
Thanks
I want to maintain my attendance register with conditions. Like if a employee took more than 4 days leave (continuously). automatically it should display straight to his name: warning
for example
Attendance Register
employee 1 : b1:af1 (31 columns for month)
If he took 4 days leave continuously then only result should show warning message.
If he took 1 day leave and works for 2 days and again took leave. no need to warn.
I just need if he took continuously 4 leave days with b1:af1, result warning !
Please help, Thanks in advance for responding to my queries
Please help me
I want to maintain my attendance register with conditions. Like if a employee took more than 4 days leave (continuously). automatically it should display straight to his name: warning
for example
Attendance Register
employee 1 : b1:af1 (31 columns for month)
If he took 4 days leave continuously then only result should show warning message.
If he took 1 day leave and works for 2 days and again took leave. no need to warn.
I just need if he took continuously 4 leave days with b1:af1, result warning !
Please help, Thanks in advance for responding to my queries
Hi Svetlana, I would really appreciate any help you can give me with the following:
I have 2 columns of data like this
Column 1 Column 2
Beer 1
Wine 2
Beer 1
Beer 2
Wine 3
and so on,is there a way to calculate the data to get the total amount from column 2 per item from column 1?
Thanks in advance,
Hi Kel,
You can use a SUMIF formula similar to this:
=SUMIF(A1:A5, "Beer", B1:B5) where column A is the product name and column B is qty.
Hello,
Thank you for your helpful tips however I have a scenario nonapplicable to the ones you have provided. I'd greatly appreciate if you could help me create a formula for the following scenario:
I need to get a count of Employee Names that do not have an assignment to an Employee Group. For Example:
Column A
Employee Names
Column B
Employee Group
I need a count of cells to be counted only when there is text in Column A and no text in Column B.
Currently I am using the following syntax: =Countifs(A2:A1000,"*",B2:B1000,"")
This syntax is returning a sum of cells with formulas minus those cells with data populated.
How can I fix this?
Hi Sarah,
Try this one:
=COUNTIFS(A2:A1000,"<>"&"",B2:B1000,"")
hi i want to use cell as a criteria by counti formula,
and i could not find out solution. is it possible?
if possible how i can do it
Hi Kerem,
Of course, you can use a cell reference as a criteria. In this case, you enclose the operator in quotation marks and add an ampersand (&) before the cell. For example:
=COUNTIFS(A2:A10,">"&$D$3)
Thanks Svetlana for helping me and resolve all my statistic issues.
Hi Svetlana,
I am working on date range and I want to get the weekly counts with inclusive dates. Can you pls. help me how to figure out the cell reference with variable dates?
Thanks in advance,
Jose
Time Criteria using counti if
=countif(A1:A100,"<="&TIME(10,0,0))
I want to know how many people arrived before 10 AM from that column.
Excel is not giving the output.
Would b great if anyone could help
Hi Sourav,
Try this one:
=COUNTIF(A1:A100,"<="&TIMEVALUE("10:00 AM"))
Hi,
i want to know how can i count in given below table there is final result is latest revision.
No. Rev.00 Rev.01 Rev.02
1 C D B
2 C B B
3 D C B
4 D B A
Hi.
I wanted to know how to write a formula in Excel for the following:
Suppose there are variable names available in individual cells in a column in one sheet. Excel needs to count (and subsequently sum up) the number of times those names appear in a column in another sheet and display the answer in a cell in another sheet.
Greatly appreciate your assistance on this.
Regards,
Ken
Hi Ken,
Your Countif formula may look similar to this:
=COUNTIF(Sheet2!A2:A100, Sheet1!A2)
Where column A in sheet2 is where all the different names appear, and A2 in sheet1 is an individual cell with a variable name you want to count.
Hi,
I have a table in excel with dates, times and list of random names for each day at different times.
I would like to be able to count how many times all the different names appear. It is very much like an attendance sheet (only I'm trying to count non attendances), with session starting every 30 minutes. Ideally, I would also like to know times and dates with most non attendances.
Thank you very much for your help.
thanks a lot Svetlana, it is really very helpful
Hi Svetlana.
I have a table and the rows contain either "TRUE" or "FALSE" in each cell.
Is it possible to count how many times have "True" happened 3 in a row on each roll? e.g TRUE TRUE TRUE
So at the following example, "True True True" happens 1 time only:
FALSE FALSE FALSE TRUE FALSE FALSE TRUE TRUE TRUE FALSE
and what if I have 4 True happened in a row:
FALSE TRUE TRUE TRUE TRUE FALSE TRUE FALSE
Will Excel read "True True True True" as 2 times?
Thank you!
Hi Svetlana,
Hoping you can help with my data comparison.
I have two sets of timesheet data (3 cols  project, person and hours) and i have managed to find ones where all three match between the sets. what i am trying to find out is where the Project and person match but the hours have changed... i want to find out by how much the hours have changed
So if in Set A  person x has done y hrs against Project 1
But in Set B  Person x has done Z hrs against project 1.
i would like the formula to tell me that Person X's hours against project 1 has changed by (yz)hrs
thank you ... this would save me hours of manual work!
thanks
meena
How do i count a row based on specific column data. Like count the number of cells with numbers and exclude Fridays from the column.
well simple way is this =COUNT(E22:AI22)COUNT(I22,P22,W22,AD22)
but i want it more automated where the COUNT(I22,P22,W22,AD22)are the Fridays and COUNT(E22:AI22) is the cells with numbers
Hi, I'm trying to count the number of items between two dates which meet a criteria (A3). So far i have got this, which seems to be only giving the results which are greater than 1 May as opposed to between 1 May and 31 May:
=COUNTIFS('DRAFT AUDIT RESULTS REGISTER'!$B$3:$B$100,A3,'DRAFT AUDIT RESULTS REGISTER'!$A$3:$A$100,">=1/05/2015")*AND(COUNTIF('DRAFT AUDIT RESULTS REGISTER'!$A$3:$A$100,"<=31/05/2015"))
How to Solve this.
In cell B5, use the COUNT function to calculate the total number of students in the class.
where A17:A52 contains student IDs.e.g,3934118,3934404
etc
you dont have to ise COUNTA,COUNTIF,COUNTBLANK etc functions.only use COUNT function
Hi,
I want a formula that counts 2 or more rows like AND gate logic.
Example:
one row is having "pens" and another row having "RED color".
I want how many red color pens are there.
Thanks in advvance for ur assistance
Hi!
Supposing that Column a is "pens" and column B is color, you can use the following COUNTIFS function:
=COUNTIFS(A1:A100, "pens", B1:B100, "red")
Hi,
15Jun MDM
16Jun PTP
17Jun DTDE
18Jun MDM
19Jun PTP
20Jun QTC
15Jun RTR
Above is the one which is there in my excel.
Am using formula as to find between dates from 15th to 19th
=COUNTIF(H2:H9,">=6/15/2015")COUNTIF(H2:H9,">6/19/2015")
Along with this, I would like to add condition which Matches I column too, say if I need to see between the date rang what are all there as "MDM", in this example it should return a count as 2.
Kindly let me now how to add the condition.
hi Murugesh, you might want to try this. was having the same problem but manage to solve it like this. reason for using DATE() was because, they are comparing date serial with text before. but since you converted your dateText to the same comparing format, it would works. hope it works for you too! :)
=COUNTIFS(H2:H9,">="&DATE(2015,6,15),H2:H9,"<="&DATE(2015,6,19),I2:I9,"MDM")
No accident type A B C R
1 A Total... ... ... ...
2 B
3 B
4 R
5 B
6 A
7 C
accident type can filter.I want to get the total for each accident type for long data sheet. Thanx a lot for this service
Howdy,
I’m trying to figure out how (or) if I can apply countifs to my spreadsheet. What I have going:
In Column “I” I have Panel ID i.e. C3D
In Column “J” I have # of panels (“1” per line item currently)
Column “L” I have panel ID again
And “M” I have QTY
My thought process behind this so far, is when I receive a trucking invoice, I receive, i.e. 20 panels of C3D. Well, currently I have to enter C3D 20 times with a “1” next to each in # of panels to get the QTY in “M” to count it. What I want to do here is be able to enter “20” in # of panels in “J” for ID C3D in “I” and have it say, on row 9 in column J is C3D, and for that entry is 20 of them and count 20 for C3D. Say tomorrow I can 3 more C3D. Next row in row 10, C3D with 3 and the countifs not stop at the first one with just 20, but keep going and say, hey, 3 more for a total of 23.
Any help would be greatly appreciated!
Good Morning!
This tutorial was very helpful. I am having an issue with some dates in the formula:
=COUNTIFS(tblEvents[Date of Request],">=6/15/2015",tblEvents[Date of Request],"=J1",tblEvents[Date of Request],"<=J2")
Thank you! Tanya
Hi,
I am trying to count how many consecutive days each employee works in a given date range. Here is how my spreadsheet is set up:
A1 = Title Name
A2 to A100 = Employee Names (an employee name may be lkisted 20 or more times in a row and then the next employee's name  in alphabetical order)
C1 = Date Worked Title
C2 to C100 = Dates (consecutive dates)
H1 = Hours Worked Title
H2 to H100 = Hours worked each day
So, I am trying to find for each employee, what is the maximum days in a row (consecutive days) each employee worked.
Thanks in advance.
I have a formula that is working to get my answer, but the problem is I need to use that answer in another formula and that isn't working. Any help on that?
1st Formula: =IF(COUNTIF(F:F,"*Corr*"),"1",)
2nd Formula that need to be able to count the "1" in the first formula. : =SUMPRODUCT(('Raw Data'!C:C=C4),('Raw Data'!H:H=1))
Having a problem figuring out why a countifs formula doesn't work. I have a file that has several types of status closings in it  Verified; VerifiedPaid; Verified Denied; Canceled; Taken; Assigned.
Based on the client I want to count the number of any trip status that is {Verified* or Taken* or Assigned*}
And further has the Level of Service that is any of . . .
=SUM(COUNTIFS(Table1[Broker Client],"VA Client Health",Table1[Status],{"verifi*","Taken*","assign*"},Table1[Trip Level of Service],{"ambu*","Lodg*","Meal*","special*","Mass*"}))
What am I missing with the Wildcards and {}. It's a 145000 + row file
Thanks the countifs definition and example really helped me resolve a calculation issue in one of the formulas I was using.
dear mam,
i have the following data in sheet
A B C D E
1 SL RM RM/SL JY/RM SL
2 JY SL SL RM/SL RM
3 RM RM/SL JY/RM JY SL
i want to count "SL" or any other string combine with other. which countif cirteria should i use?
Hi TEJASWINI,
You can use a usual COUNTIF formula like this:
=COUNTIF(A2:E4, "SL")
Countifs with multiple criteria as is not showing correct input
Countifs(A:A,"Jan",B:B,"User1",B:B,"User2")
result showing as 0
m from a hospital background, i do need to use excel a lot to complete ma audits and various reports. m having a problem with my audit data. there are around 18 columns and about 500 rows, data validation has been applied, which gives me 3 options for each cell (yes no and NA). It is similar to your example of "COUNTIFS for text values:: counting who passed all the subjects. but in ma sheet there are three options, out of these i want to count yes and NA together and neglect no. i tried number of formulas but couldnt get the right one. atlast i counted number of "no" first in a row then subtracting it from total which gives me the value of number of "yes and NA" in same. But i was wondering if there is a possibility of counting two texts together in a single row.
Hi JOLLY,
You can add up the results of 2 COUNTIF functions, like this:
=COUNTIF(A1:F500, "yes") + COUNTIF(A1:F500, "na")
Good afternoon,
If using =COUNTIF(C5:C21,"X")/ROWS(C5:C21) to get the percentage of what X equals.
How do I edit this formula to know the percentage of what X equals if I need to add multiple columns and rows together?
Columns C & D, Rows C5:C21 & D5:D21
Columns C & E, Rows C5:C21 & E5:E21
Columns D & E, Rows D5:D21 & E5:E21
Thank you,
Kelly