Jul
10

How to use Excel COUNTIFS and COUNTIF with multiple criteria

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, non-blank cells and more.

Of all Excel functions, COUNTIFS and COUNTIF are probably most often mixed up because they look very much alike and both are purposed for counting cells based on the specified criteria.

The difference is that 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:

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)
• `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 with a single condition as well as in multiple ranges with multiple conditions. If the latter, only those cells that meet all of the specified conditions are counted.
2. Each additional range must have the same number of rows and columns as the first range (criteria_range1 argument).
3. Both contiguous and non-contiguous 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 1st function, you count how many numbers are greater than your lower bound value (5 in this example). In the 2nd 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)

The COUNTIFS function in Excel is designed to count only those cells for which all of the specified conditions are TRUE. We call it AND logic, because Excel's AND function works this way.

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.

Note. You do need to use three separate ranges in the formula, one per column, if you want to count the products that contain "0" in all the columns. A simpler formula with a single range `=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, i.e. when all criteria are met. Such formulas can be applied both to contiguous or non-contiguous 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 non-adjacent 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 1-Jun-2014 and 7-Jun-2014, inclusive:

`=COUNTIFS(C2:C9, ">=6/1/2014", C2:C9, "<=6/7/2014")`

`=COUNTIF(C2:C9, ">=6/1/2014") - COUNTIF(C2:C9, ">6/7/2014")`

Example 2. Count dates with multiple conditions

In the same manner, you can use a COUNTIFS formula to count the number of dates in different columns that meet 2 or more conditions. For instance, the below formula will find out how many products were purchased after the 20th of May and delivered after the 1st of June:

`=COUNTIFS(C2:C9, ">5/1/2014", D2:D9, ">6/7/2014")`

Example 3. Count dates with multiple conditions based on the current date

You can use Excel's TODAY() function in combination with COUNTIF to count dates based on the current date.

For example, the following COUNTIF formula with two ranges and two criteria will tell you how many products have already been purchased but not delivered yet.

`=COUNTIFS(C2:C9, "<"&TODAY(), D2:D9, ">"&TODAY())`

This formula allows for many possible variations. For instance, you can tweak it to count how many products were purchased more than a week ago and are not delivered yet:
`=COUNTIFS(C2:C9, "<="&TODAY()-7, D2:D9, ">"&TODAY())`

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.
Tip. If you want to count cells with an actual question mark or asterisk, type a tilde (~) before the character in your COUNTIFS formula.

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 non-blank cells - "<>"&""

Important note! Please remember, when you use an Excel COUNTIFS formula with multiple ranges, all of the ranges must all be the same size, i.e. have the same number of rows and columns. Otherwise, your formula will return the #VALUE! error. This applies to all COUNTIFS formulas in Excel, with any data type - numbers, dates and text.

815 Responses to "How to use Excel COUNTIFS and COUNTIF with multiple criteria"

1. Michael says:

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.

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.

• Ajay Arora says:

if column A contains numeric value like = 1 2 3 4 5 6 7 8 9 10 etc...
and column B contains three status like = Ontime, Late & Not Done

then I want count-if of column A's numeric value 3 and 4 and column B contains status of Not Done

kindly help me.

• joel says:

=countifs(A:A,">=3",A:A,"<=4","B:B,"Not Done"

• joel says:

Few typos above.
=countifs(A:A,">=3",A:A,"<=4",B:B,"Not Done")

• BRAJESH says:

THANKS VERY MUCH ITS VERY USEFUL FOR ME

• Jeethan says:

I have the same problem where I want to count how many agents are working between 06:00 AM and 10:00 AM. How can i do that using countif function, send suggestions

• Jeethan,

You can use a formula similar to this:
=COUNTIFS(A1:A21, ">="&TIMEVALUE("6:00 AM"), A1:A21, "<="&TIMEVALUE("10:00 AM"))

• ReMind says:

Or this :D

=IF(AND(E2=1,OR(F2>=TIMEVALUE("04:00:00 PM"),F2<=TIMEVALUE("04:00:00 AM"))),1,0)

• ReMind says:

sorry...was for another comment.

2. Michael says:

Thank you. I just spoke to a colleague who walked me though a pivot table of my data. Thanks again.

3. Emily says:

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

Thank you!

4. Emily says:

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.

• Emily says:

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

• Hassan says:

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

• Hi Hassan,

A formula can be similar to this:

=COUNTIFS(A1:A21, ">"&TIMEVALUE("12:00 PM"), A1:A21, "<="&TIMEVALUE("1:00 PM"))

• FIROZ AKHTAR says:

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.

• Nitin Srivastava says:

Hi Svetlana ,

I 've send sample data Plz help me for get formula

• Nitin Srivastava says:

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

• Nitin Srivastava says:

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

5. New Excel user says:

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.

6. rodz says:

Hi,
how can i automatically count or computed in Monday to Sunday data base count as week summary

• Hi Rodz,

7. Glenn Campbell says:

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?

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.

• Atul says:

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

8. Kees Struik says:

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

9. Kees Struik says:

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.

10. Jason Pisani says:

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.

11. gio bitoy says:

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

• Nur says:

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.

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

Thanking you,
Macro Cable Ltd

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

Dear Svetlana Cheusheva,

Please solve my example: Mr. Karim is market visited statement as below-

Date Code Name Type of Customers

12 October 2014 201 AKA Builders Developers
13 October 2014 300 PWD Consultant
15 October 2014 300 PWD Consultant
16 October 2014 201 AKA Builders Developers

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,
Macro Cable Ltd

Dear Svetlana Cheusheva,

Thanking you
Imam
Dhaka

ANY UPDATE

IMAM

13. vcoolio says:

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.

• Kanchan Khandekar says:

I need a help to do below thing. Suppose I have column A which contains values either of A,B or C as Follows.

Column A
A
A
B
A
B
B
C
A
Now what I have to do is, I want to count pair of AA, AB, AC likewise, based on the two consecutive values, if First row contains A and 2nd row contains A, then AA count should be 1 likewise go on.
What I tired is I can get count for two cells only using following formula =COUNTIFS(A1, "=A",A2,"=B")
But I don't how to go on increasing the rows.

14. vcoolio says:

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.

15. Brandon says:

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.

• Am says:

YES!! This is exactly what I am trying to do, too - and I am getting the same non-error of a zero result. What is the proper way to formulate this?

16. Mr Sunny says:

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.

17. Angela says:

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.

Cheers

• Alejandro says:

=COUNTIFS(C:C,"Passed",D:D,"Quebec")+COUNTIF(C:C,"Remediation Passed)

This is useful for differing values.

or

=COUNTIFS(C:C,"*Passed",D:D,"Quebec")

This is more efficient and works to find anything containing the word "Passed" as in your example.

19. Tim says:

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.

• Alejandro says:

Tim,

See if this works:

=COUNTIFS(A:A,""&"",C:C,""&"")+=COUNTIFS(A:A,""&"",D:D,""&"")

20. katy says:

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 'May-14' in cell 'Overview!'C2 - Countif('May-14'!R:R,Criteria)
If then in overview cell2 it is changed to Jun-14 the Countif will change to Countif('Jun-14'!R:R,Criteria).

21. KC says:

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.

22. Ansu says:

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

23. Mark says:

I am trying to tally the number of calls by day-of-the-week 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.

24. Pagz says:

How do I use countif to meet the same criteria but across multiple ranges in multiple worksheets?

25. Jerrie Mar says:

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

26. Jerrie+Mar says:

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 A1-B1) it will leave blank

27. Jix says:

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.

28. Kolegu says:

Hi,

How can I count if criteria is in a list?

to have something as countif(Sursa!\$G:\$G,"Answered" or "Blind Transfered" or "RONA")
Thank you.

29. Kolegu says:

instead of it, the formula is:

for those who will have this problem in the future.

• Cemil Binlik says:

Great combination Kolegu. Thanks a lot, really helpful

30. Linda says:

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.

31. Jane says:

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?

I

• Hello Jane,

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.

32. avp says:

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?

33. angelo says:

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.

• Sham says:

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.

34. Raja says:

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

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

• Raja says:

Oke I will..working on it..thanks

35. Subbu says:

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 drop-down menu.
- Select "count" instead of "sum" and click OK.

If you want to update information, right-click on the pivot table report and select "Refresh" from the context menu.

• MICHAEL says:

Name Gender Religion DIT0101 DIT0102 DIT0103 DIT0104 DIT0105 TDH 1100 total Average Grade Award
Jane female Christian 56 88 88 99 65
Maulid male Muslim 87 56 75 67 56 87
Hanifer female Muslim 56 34 67 87 78 44
hardeep female Hindu 67 36 43 34 35
janet female Christian 45 89 87 4 67
Andrew male Christian 45 68 88 23 87
Habib female Muslim 67 76 32 87 56
vijay male Hindu 75 56 87 28 45 78
Jack male Christian 88 67 34 77 67 32
H

36. Lindsey says:

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.

37. Rich says:

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

• 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")

=COUNTIFS('Current Enlisted By Name'!\$E:\$E,LEFT(B6,4)&"*",'Current Enlisted By Name'!\$K:\$K,TRUE)

38. Jason says:

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

39. Monique Drummond says:

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.

40. Lorna says:

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:G7497,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.

• Lorna says:

Svetlana - it WORKED! Many many thanks from California! Where do I send the Bailey's????

41. Nancy says:

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

42. Freedom says:

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

• You can use the following array formula (remember to press Ctrl + Shift + Enter):
{=SUM((B2:B6>=10)*(B2:B6>A2:A6))}

• Freedom says:

Thanks a world.
You're a live saver.

43. Robman23 says:

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

44. clogsden09 says:

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!

45. Nina says:

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?

46. Andrew says:

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

47. Raja says:

Hi ,

By having the Alphabets (A-Z) & numbers consider from (0-9) 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.
Thanks,
Rajamani

48. Bernie says:

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

49. Leo says:

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.

50. CARMEN says:

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?

51. lera says:

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

52. Carlo Mantini says:

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

53. Ryan says:

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 .

Center Revised Date 2nd PM Plan Date 3rd PM Plan Date Total Count

Bannu-1 1-Oct-14 1-Oct-14 Blank ?(Answers should be 2)

You can use this formula to count all non-blank 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: 1-oct, 2-oct, 3-oct, 4-oct, 5-oct, 6-oct, 7-oct,.....
2nd Row: ASDF DSFA FGHJ WERT, SDFA .....

I want the latest comment and date at the end of row for each row.

56. Utkan Ay says:

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)

57. Floris says:

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/10|26/10|27/10|28/10|29/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.

58. Avi says:

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.

• Avi says:

Turns out that the was a bracket in the wrong place:
="Follow up (due = "&countif(J2:J,"<="&today())&" )"
works very nicely.

59. Fayaz says:

Just mailed you my sheet can you help me with that please

60. Fayaz says:

Hi Svetlana,

Just mailed you my sheet can you help me with that please

61. Shafin says:

Svetlana Cheusheva
Hi,

I'm trying to get a count of common digit like (1234-2345-2789-1289-2548 = 2 100% 4 50% 8 50% 3 20%

62. Brandy says:

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.

• Tushar says:

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
3-Jan-15 Sat
3-Jan-15 Sat
10-Jan-15 Sat
10-Jan-15 Sat

• Alexander says:

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.

63. Laura says:

Hi Svetlana, I just sent you an email asking for your help with a formula. Thank you so much for all you do!

64. Ali says:

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!

65. karthi says:

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.

thanks

• Avi says:

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.

• karthi says:

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.

67. Anuj says:

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.

68. Gespion says:

Hi,

Your example 4: The formula show B and C cells but you highlighted B and D cells in the printscreen.

Regards.

69. Gespion says:

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)

70. Abby says:

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?

71. LePome says:

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?

• LePome says:

That didn't post right:
COUNTIFS(J:J, ""&A2)

• LePome says:

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.

• LePome says:

Thank you for the quick and informative reply.

72. Khalid says:

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
34-53-22
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.

73. CH says:

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.

74. Gavin says:

Hi,

Im trying to do a sum of a range whilst looking at the date completed, for example....

Qty Date
5 02-Oct-14
100 02-Nov-14
5 02-Oct-14
5 02-Nov-14
4 02-Nov-14
54 02-Dec-14
5 02-Dec-14
6 02-Dec-14

Is there a formula give me the sum of left column against the factor Oct for instance and should bring back the result 10.

Gavin

75. Giovanni says:

Hello Svetlana,

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!

76. Juan says:

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

77. Takedia says:

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!

78. christine says:

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.

79. bhajan lal Sharma says:

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

80. Kit Elloran says:

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

• Kit+Elloran says:

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

81. Umer says:

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%

82. NYT8er says:

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.

• NYT8er says:

After safety I have between the quotes in the formula.

83. raed says:

hi,

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

84. raed says:

-----A----------B------------C------------D-----------E
--CODE------LOCATION-------KM-----------COST--------EXTRA
--100-------SOHAR---------50------------25-----------2--

85. raed says:

hello

86. Jesu Dass says:

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

87. Count if problem says:

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

88. raed says:

as example,,in the first sheet as below,,,
A B D E F
CODE FILLING STATION K.M AMOUNT ALAWNS
5347 FALAJ AL-QABAIL F/S 15 16.74 1
5294 FALAJ AL-QABAIL T 16 23.02 1
5365 FALAJ AL-QABAIL 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 AL-WUQAIBA 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

89. zaheer khan says:

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.

90. RC says:

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)

91. Mohammed says:

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.

92. zak says:

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

93. Denise H says:

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.

94. RandyO says:

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.

95. RandyO says:

Nevermind! I found the solution elsewhere.

96. Dean says:

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.

97. Shwan says:

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?

98. Kali says:

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?

99. Ryan says:

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?

100. KC says:

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.

101. Kowshick says:

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?

102. Dan says:

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.

103. Rasel says:

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.

• Rasel says:

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

104. satheesh says:

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 (SHEET-1)
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 MONIRATH MONDAL 23
CT BABLU NAIK 23
CT TAPASA ROY 25

• Hi Satheesh,

Your formula looks correct, just replace >=25 with <=25 :)

105. wilfy says:

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

106. Wayne says:

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.

regards
wayne

107. Wayne says:

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.

108. Chalinda K says:

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

Chalinda

109. Bil says:

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!

110. Jon Fuller says:

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.

111. Shwan says:

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

112. M2 says:

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

113. Hasan says:

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?

114. Hasan says:

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
AXT-11232 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

116. amid says:

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

117. Vil says:

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.

• Vil says:

And for the entries that are less than or equal to 5 days to be True.

118. mathieu says:

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?

119. Rob says:

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?

120. Bob R says:

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.

121. liam says:

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.

122. Becky says:

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

123. Michael says:

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.

124. satheesh says:

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 20-25, 26-30 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 21-25

1 CT MUNNA 18-06-1991 23 17
2 CT MITHUN 28-02-1991 24
3 CT BISWAJIT 26-06-1991 23
4 CT KARTICK 14-10-1989 25
5 CT SOURAV 22-10-1991 23
6 CT VIKASH 28-02-1991 24
7 CT VEERENDRA 03-01-1992 23
8 CT OM PRAKASH 01-07-1989 25
9 CT PAWAN 02-01-1990 25
10 CT SANJAY 09-01-1990 25
11 CT SUNIL 21-10-1990 24
12 CT Atanu 10-07-1991 23
13 CT KALI 05-02-1990 25
14 CT RAKESH 05-01-1991 24
15 CT SUNIL 09-02-1991 24
16 CT PUPINDER 12-12-1991 23
17 CT PATIL 23-11-1991 23
18 CT Gulab 10-07-1991 23
19 CT Bhupendra 18-12-1991 23

ACTUALLY ALL ARE COMES UNDER 21-25 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

126. Ben says:

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

127. Chandan Tiwar says:

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.

128. Marcell says:

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?

Marcella

129. May says:

Formula that will continuesly count based on Cell value and number it sequencially

Col1
ab
cd
ef
ef
cd
ab

Result
ab-2014-01
ab-2014-02
cd-2014-01
cd-2014-02
ef-2014-01
ef-2014-02

130. musharaf ahmed says:

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

131. Melody says:

Hi Svetlana,

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.

132. Bhagesh says:

I have tree sets of data. Project Type, Resource Name and Days (columns Jan-14 to Dec-14).

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.

133. Dan says:

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?

• Bhagesh says:

Go for.. COUNTIFS (if not using using Excel2003)

You can add new condition to exclude "apostrophes" "'".

134. Mohamed says:

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?

135. Mak says:

Thank you very much.. really it's very helpful

136. Lynda says:

I need to use a function to count the number of Part in column c2-c134 in the NWest which is in column f2-f134, I dont seem to be able to enter the correct data into any function to get it to do this

137. Nikhil Nanwani says:

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 4-6 series.

138. Troy says:

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.

139. sarwar says:

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)

140. Luke says:

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

Luke

141. Sameer says:

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

142. Zakk Baker says:

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!

143. Tracy says:

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

144. Karen says:

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

145. scott says:

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

146. Rasel says:

Hi,
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".....

147. wan says:

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

148. Jaikumar says:

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

• GRS6411 says:

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

149. Rick says:

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?

150. Anonymous says:

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

151. Tracy says:

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

152. Raj says:

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?

153. arvind says:

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

154. Costin says:

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

155. vikash gupta says:

i have 500 account number
how to search double

156. SUNIL KUMAR says:

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.

157. Elodia says:

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

158. Jamie Booth says:

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!

159. Jamie Booth says:

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

160. Jamie Booth says:

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)

161. Sara says:

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

162. Sham says:

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.

163. RichardR says:

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

164. Joanna says:

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

165. Jennifer says:

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

166. Terry says:

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

167. hanif says:

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?

168. Joao says:

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

169. Tim S. says:

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

170. Mahyar says:

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

• Mahyar says:

I want function alike this: =if (A11.000.000;A1*3;"No Number")

171. Farooq says:

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.

172. Satya says:

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

173. hemant sharma says:

Thanks for Guide.

174. hemant sharma says:

Svetlana Cheusheva you are so beautiful

175. Elvis says:

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.

176. Roshin Stephen says:

Hi Svetlana,

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.

• Roshin Stephen says:

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.

177. Ruman D says:

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

178. Rosman says:

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

179. Anonymous says:

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,"="&today-3) but I suppose to get 1 ryt if today date is 13/01/15??

180. Giwrishankar says:

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

181. Pauline says:

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

182. Jessie Stanley says:

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

183. sue says:

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

184. Artūrs says:

=IF(C7>0;"OK";"BAD") this one is working, but if I put region

185. Artūrs says:

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?

186. Shannon says:

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 post-HS 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

• Alexander says:

Hello Shannon,

=COUNTA(A2:A20)-COUNTIF(A2:A20,"HS")

Where A2:A10 is the EDU column

187. Ruman D says:

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.

188. Sam Powell says:

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.

189. Anonymous says:

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

• Eddie says:

uum yeah, that was very clear how's going to Dubai and sharjah I suppose?

• Alexander says:

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.

190. Eddie says:

Thanks Svetlana, you just saved my life and a lot of typing :) awesome post

191. Vishal says:

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
CCUBG Late
NAGAR On Time
CCUBG Late
NAGAR Early
CCUBG Early
NAGAR On Time
CCUBG Late
NAGAR Early
CCUBG Late
NAGAR On Time
CCUBG Late
Thank you.

• Alexander says:

=countifs(A2:A1000, "NAGAR", B2:B1000, "On Time") + countifs(A2:A1000, "NAGAR", B2:B1000, "Early")

• Anonymous says:

Grate. Thank You.....

192. LG Singh says:

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.

• Alexander says:

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"

193. Dee says:

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

• Dee says:

Please disregard the above - I worked it out.

194. LG Singh says:

Thanks Alexander, but the formula counts empty cells also in the column. How to count only the cells with values, neglecting empty cells?

• Alexander says:

=SUM(LEN(A2:A10)-LEN(SUBSTITUTE(A2:A10,",",""))+(--NOT(ISBLANK(A2:A10))))

195. LG Singh says:

Thanks again Alexander.

196. Pushap says:

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

197. Lotus says:

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 Jan-05 Feb-05

2000 Office Equipment \$- \$-

3000 Printers \$- \$-

1st- I would like to set the date in each column as 16Jan-15Feb, 16Feb-15Mar 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;"mmm-yy"));'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.
Lotus

198. Ravi says:

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.

199. Roshin Stephen says:

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.

200. H says:

Excellent! Thanks for the clear explanation. This really helped.
H

201. Lea says:

Hi!

How can I use countifs and vlookup together?

Thank you.

202. Randhir Godase says:

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?

203. Mike says:

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?

204. Anonymous says:

Recipt Credit
Cash Book Bank

564 914308
30,000 30000
30000 4654
43660 30000
1245247 19344
30000 465654

205. Raj Jagtap says:

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

206. A. Clemente says:

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?

207. TechTeacher says:

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.

208. Santosh says:

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

209. Felipe says:

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.

210. saista says:

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

211. zeta says:

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

212. Jaaaaaaaaaaaaason says:

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

213. Rebecca says:

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.

214. John says:

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?

215. MarcusM says:

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. Jan-2013 has 21 active subscribers, Feb-2013 has 24, etc.).
I would really appreciate your help.
Thank you.

216. Ana says:

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.

217. William says:

Long story short, thank you, Svetlana, for this post! Your explanation and examples using COUNTIFS were just what the doctor ordered!!

218. Leela says:

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.

219. Leela says:

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.

220. Ghazi says:

can i incloCan I include function within the COUNTIFS function like the following example:
=COUNTIFS(D4:AH4,"COUNTA(D4:AH4)")

thank you.

• Hi Ghazi,

If you are looking for a way to count non-blank cells in D4:AH4, you can use the following formula instead:
=COUNTIF(D4:AH4,"<>"&"")

221. Ghazi says:

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

222. Jake says:

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.

223. kardon says:

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.

224. Steven says:

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.

225. MANSUR says:

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?

226. MANSUR says:

227. Henry says:

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.

228. Henry says:

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.

229. Thomas says:

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

230. Henry says:

found the answer. I need to test criteria on each and sum:
=COUNTIFS(date1,text)+COUNTIFS(date2,text)+...+COUNTIFS(daten,text)

231. Nahla says:

Hi,

i want to count if the column contains a date(unconditioned)

232. Yogesh says:

Excellent information dear...!
Once again, thanks a lot.

233. sharon says:

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.

234. Rachel says:

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.

• Rachel says:

=SUMIF(B:B,"Food", E:E)

235. paula says:

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

236. Joe says:

Thanks worked great for me!!

237. Mark says:

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"

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

238. Pam says:

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

239. marlon says:

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

240. marlon says:

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)

243. David says:

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

244. Runu says:

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 non-contiguous ranges (OR logic)

245. David Betts says:

Thanks Svetlana,

and if I wanted to use a cell reference instead of 60 do I just use "<C1" for instance?

246. Karim says:

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

247. David Betts says:

Many thanks Svetlana!

248. Ritchie says:

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 D5-D9.

=COUNTIFS('Programme Risks'!E4:E944,"Live",'Programme Risks'!K4:K920,"RED")

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

249. Mihai says:

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

250. Hassie says:

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.

251. Loy says:

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 "On-time" 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.

• 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;

"On-time" 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<>""), "on-time", ""))

252. Stevie says:

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.

253. Tom says:

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

• Alexander says:

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)+1-MIN(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

254. Anmol Singh says:

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

255. Chris says:

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

256. Brian says:

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.

257. jana says:

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 !

258. Lexi says:

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

259. Nurse Betty says:

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

260. Gavin says:

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

261. jana says:

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 !

262. jana says:

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 !

263. Kel says:

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?

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

264. Sarah says:

Hello,

Thank you for your helpful tips however I have a scenario non-applicable 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,"")

265. kerem says:

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)

266. mangi says:

Thanks Svetlana for helping me and resolve all my statistic issues.

267. Jose Marcos says:

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?

Jose

268. Sourav says:

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

269. idrish khan says:

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

270. Ken says:

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.

271. Chris says:

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.

272. kerem says:

thanks a lot Svetlana, it is really very helpful

273. Mark says:

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!

274. meena says:

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 (y-z)hrs

thank you ... this would save me hours of manual work!

thanks
meena

275. anish says:

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

276. Melissah says:

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

277. verymo says:

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,393-411-8,393-440-4
etc

you dont have to ise COUNTA,COUNTIF,COUNTBLANK etc functions.only use COUNT function

278. lakshmi kanth says:

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

279. Murugesh says:

Hi,

15-Jun MDM
16-Jun PTP
17-Jun DTDE
18-Jun MDM
19-Jun PTP
20-Jun QTC
15-Jun 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.

• KuRo says:

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

280. chiran says:

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

281. Ben says:

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!

282. Tanya says:

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

283. Larry says:

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.

284. Chris says:

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

285. Jim says:

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; Verified-Paid; 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

286. JHatcher says:

Thanks the countifs definition and example really helped me resolve a calculation issue in one of the formulas I was using.

287. TEJASWINI says:

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

288. Anas says:

Countifs with multiple criteria as is not showing correct input
Countifs(A:A,"Jan",B:B,"User1",B:B,"User2")
result showing as 0

289. JOLLY says:

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

290. Kelly says:

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

• Randi says:

Kelly,
Did you ever figure out this formula? I am trying to do the exact same thing and I cannot get it.

If you did and could share it I would be much appreciative.

Thanks!

291. Dean says:

Hi Svetlana,

I can't seem to get a simple formula to work:

I have a grid spanning 31 columns wide and 5 rows deep. In any one of the cells I can have either, "M", "H", "S", "P" or nothing.

The 5th row has to total up how many times in each of the 31 columns how many of each of the letters are shown and add them up. I'm currently trying to use this one in row 5 of column c for example:

=COUNTIFS(C5:C9,"P",C5:C9,"h",C5:C9,"s",C5:C9,"m")

The box just returns 0 even when there are matching values.

Can you help?!

• Dean says:

I literally solved it straight after posting this - typical! Took me two hours to admit defeat and post this question, then solved it almost immediately afterwards!

I used this to fix it (I wasn't adding the values together!):

=COUNTIFS(B5:B9,"P") + COUNTIFS(B5:B9,"h") + COUNTIFS(B5:B9,"s") + COUNTIFS(B5:B9,"m")

Thanks for providing the inspiration to sort it!

292. Ritche says:

Hi.

I hope to get help from you.

I have in one sheet a serial number of products. In another sheet, it shows the dates to when customers called and complained about a product. What I would like to achieve is that I would like to know how many complaints a specific product received in a 7 days, 30 days and 90 days period using the product serial number as reference.

Thank You for any response.

293. Matt Keita says:

Hi,

I am working on a spreadsheet that has some strings in columns A2:K2.
In a separate sheet I have in rows A2:A15 some strings (PP1, PP2, PP3...PP15)
In cell L2 I want to be able to put a formula to count how many time any of the strings in A2:A15 appear in A2:K2.

Thank you any help in greatly appreciated.

Matt

294. Keith says:

hello, I really need help with this.
I have two sheets- the first one has an option to put in a date range as seen below:

Start Date End Date
14/07/2015 22/07/2015

in the second sheet i have a yearly schedule for all my staff, but the way i have it laid out is the number of staff that will be in the office in the first column and the date will be in the second column, like below:

staffed Date
20 13/07/2015
21 14/07/2015
22 15/07/2015
20 16/07/2015
19 17/07/2015

What i need the formula to do is check the date range in the first sheet against the numbers and dates in the second one and return a value.

Example- i put a start and end date of my holidays into it, it checks the sheet and if i have less than 20 staffed on any of the dates specified it will say rejected. put if there is 20 or more staffed each day it will say approved.

295. Shubham says:

Dear Svetlana,

I want to count the numbers when Cell value of Column A is less than Cell value of Column B of same row. Also want to apply the same logic for continuous 10-12 rows.

Kindly suggest

• Shubham,

Because your task requires comparing 2 ranges, you need an array formula like this:
=SUM((A1:A12<B1:B12) * 1)

Due to it being an array formula, you must press Ctrl+Shift+Enter to enter the formula correctly.

296. Rachel says:

Hi,
I have a countif function that is working:

=COUNTIF('Sheet1'!C:C,"<=10/07/2015")

but I want to swap the date out for a cell reference e.g \$AI\$1 (will still contain the same date format). I cannot seem to get this to work.

• Hi Rachel,

The following formula seems to be the one you are looking for:
=COUNTIF('Sheet1'!C:C,"<="&\$AI\$1)

297. Bea says:

Hello,
I want to use the countifs formula when the value in cell E2 is present in the list PROSPECT AND when the value in cell L2 is in the list REGULAR.
I use the formule :
=COUNTIFS(PROSPECT;E2;REGULAR;L2)
However it returns #VALUE!
It works perfectly when I use a single countif formula : =COUNTIF(PROSPECT;E2)

• Hi Bea,

The first thing that comes to mind is checking if PROSPECT and REGULAR lists are of the same size, since the syntax of COUNTIFS requires that all the ranges have the same number of rows and columns. Is this the case?

• Bea says:

Hi Svetlana,
they are 2 columns in a separate sheet. 1 containing 10 values, the other only 3.
Is that what you mean?

• Hi Bea,

Exactly. For the COUNTIFS formula to work, both lists must have the same number of rows.

298. SR says:

Hello Svetlana,

I'm creating an wedding invite spreadsheet. I have four columns: NAME, GUESTS, RESPONSE, AMT ATTENDING. I would like to to create a conditional cell (AMT ATTENDING) on each row that will determine how many are attending. So, if the RESPONSE cell has 'N', the AMT ATTENDING cell on that should equal 0. If the RESPONSE cell equals 'Y', the ATTENDING cell should equal the GUESTS cell on the same row.

Is this possible? Thank you so much!

• Hi!

Of course, you can do this by copying the following IF formula in AMT ATTENDING column beginning in row 2:

=IF(C2="N", 0, IF(C2="Y", B2, ""))

Where C is RESPONSE and B is GUESTS.

• SR says:

Works perfectly! Thank you!!

299. Jose says:

Hello Svetlana,

the following formula is giving me some sintax error in the last field. I am trying to create a criteria ">=" that refers to another spreadsheet.

=COUNTIFS('Sheet1'!D1:D10,'Sheet2'!C5,'Sheet1'!I1:I10,'Sheet2'!">="&'Sheet2'!G3)

What would be the right expression? Thanks a lot,

Jose

• Hi Jose,

The criteria can be expressed as follows:
=COUNTIFS(Sheet1!D1:D10,Sheet2!C5,Sheet1!I1:I10,">="&Sheet2!G3)

Is this what you are looking for?

300. Rami says:

Hi,

I have a table to do with letting of properties, table has following info 'property' 'type of property''applicant names' 'type of applicant, UOCC, HGR, FTA, TRA, MTA' and also the date that a property has been let.

I would need to count per calendar month, how many properties were let per applicant type?

Thank you

301. Chalo says:

Hi,
I need to use the COUNTIFS to count ID#’s that are between specific dates (for example: 6/22/2015 -6/28/2015; 6/29/2015-7/5/15; 7/6/15-7/12/15) but I have more than one entry for the same ID# and I don’t want to count duplicate ID#s.
Thanks
ID# Dates
UTIC05019_000 7/14/2015
UTIC05019_000 7/14/2015
UTIC05019_000 7/14/2015
UTIC05019_000 7/14/2015
UTIC05021_000 6/24/2015
UTIC05021_000 6/24/2015
UTIC05021_000 6/24/2015
UTIC05022_000 6/27/2015
UTIC05022_000 6/27/2015
UTIC05023_000 6/28/2015
UTIC05023_000 6/28/2015
UTIC05027_000 6/28/2015
UTIC05027_000 6/28/2015
UTIC05030_000 6/26/2015
UTIC05030_000 6/29/2015
UTIC05030_000 6/29/2015
UTIC05030_000 6/29/2015
UTIC05030_000 7/1/2015
UTIC05032_000 7/8/2015
UTIC05032_000 7/8/2015
UTIC05032_000 7/8/2015

302. Paul says:

Hi,

Do the different parts of the countif formula all have to be contained within the same worksheet?

I have created a report which has data on one tab, and a query form on a second tab. The countif formula is looking at concatenated fields which I want it to return a value of the number of occurrences of a selected concatenated value (such as contract number and month). I am getting a zero when using the countif formula which uses the data as the first part of the countif, but the second part of the countif formula is from a cell in my query form.

If I do a countif where both table and criteria are on the same tab, I get the value I'm expecting.

Thanks,
Paul

303. kim says:

Hi I am trying to use a countifs formula for the following situation:
I have multiple staff and I want to get a tally of how many times each staff puts in overtime for each reason for overtime.
I have been successful with this but I want to take it to the next level and find out the total amount of time corresponding to each reason for each staff member. So the formula is referring to a separate tab in excel and basically saying that if B4:B30 says "X" and c4:c30 says "Y" I want the value of D4:D30 that corresponds to this condition to show in the cell. Is this possible?

• Hi Kim,

Let me check if my understanding of the task is correct. so, if B4 says "x" and C4 says "y", you want to pull the value from D4. If so, you can enter the following formula in row 4 and then copy it down to other cells in the column:

=IF(AND(B4="x", C4="y"), D4, "")

If you want to sum the numbers in D4:D30 that correspond to those conditions, then you can use the following SUMIFS formula:

=SUMIFS(D4:D30, B4:B30, "x", C4:C30, "y")

304. Melissa says:

Hi!

I have two columns (all text). The first column is a list of names (could be repeated) and the second column is a list of factors (i.e. "ontime", "late", etc.). I want to know how many times a certain name (from the first column) hits each factor from the second column. Hopefully this is clear.

Thank you!

• Melissa says:

Hi Again,

Please disregard. I used a SUMPRODUCT formula which worked like a charm!

305. Rick says:

Good Morning,

I am so thankful I found this site.
I am looking for a simple formula to find all 4s in column b, that has a code of X3,Z1, and Y9 in column E, PLUS all 5s in column B, that have blanks in column D, with codes of X3, Z1 and Y9 in column E.
Can this be done? If not, thats fine, I will complete it the same as always.

Rick

A B C D E

5659 4 91B 91B2 Z1
2935 4 68C 68C2 Z1
6572 4 92W 92W2 Z1
8675 4 91D 91D2 Z1
7627 5 12B 12B2 Z1
7450 5 19D 19D2 X3
9171 5 36B 36B2 Z1
3503 5 11B 11B2 Z1
5759 5 36B 36B2 X3
5062 5 15D 15D2 Z1
9088 4 91E Y9
1665 4 68W 68W2 x1
7212 4 68W 68W2 x1
4539 4 15U 15U2 Z1
2579 4 74D 74D2 Z1
8052 5 14H X3
2423 5 15U X3
5228 5 12P Z1
9080 5 19D Z1
9076 5 11B x1
691 5 74D x1
3504 5 31D x1
8594 5 14E x1
919 5 11B x1
4518 5 11B X3

306. Craig says:

I am trying to use a count function, but struggling to get the formula to work.

I have an area that will be filled in with different dates. On a separate sheet I have a running date column. I want to count how many boxes have a certain date in it.

=COUNTIF(Sheet3!\$C\$23:\$EF\$34,"=14/8")

This is the formula I'm currently using, which works fine, but I cant drag it down. I've tried doing the alternative:

=COUNTIF(Sheet3!\$C\$23:\$EF\$34,"=B8")

(B8 is where the 14th August is located). This does not work.

Thanks
Craig

307. Hassan says:

How chose two fromm three number the best.
Excample:
A: 30
B: 20
C: 10
Two the best 30+20=50

308. Jonathan Barham says:

I am trying to count the number of people who have done an intervention by the place from which they come from. I am taking this information from another sheet. The thing is they have done up to four interventions and the intervention columns are not in direct order of one another. So I am trying to count the number of people say playing football which could be in four columns by the place the live.

I have tried =COUNTIFS('USER SHEET'!AC2:AC10000,Intervention!D3,'USER SHEET'!B2:B10000,Intervention!C4,('USER SHEET'!AF2:AF10000,Intervention!D3,'USER SHEET'!B2:B10000,Intervention!C4,('USER SHEET'!AI2:AI10000,Intervention!D3,'USER SHEET'!B2:B10000,Intervention!C4,('USER SHEET'!AL2:AL10000,Intervention!D3,'USER SHEET'!B2:B10000,Intervention!C4))))

309. colleenwaters says:

Hello. I’m trying to count unique text values in column A that meet date criteria in column G as determined by one cell (H158160) that contains a drop down list of dates selected by users. The intention is for them to be able to select the date from the list and see the count of account numbers, the sum of units sold, and the sum of revenue. I’ve got all of the formulas working except for the count of account numbers, because I’m having trouble with the COUNTIFS formula. I’ve been able to count unique values using the array formula {=SUM(1/COUNTIF(A2:A796,A2:A796)}, but I don’t know how to set these multiple criteria. Hopefully you can help. Thanks in advance!

310. abraham says:

hi,
i have got a big data of employees working for a mining comapany, i want to know the number of employees that come for breakfast, lunch and dinner differently. how can i do that?
thanks

311. abraham says:

i have got data of employees for a mining company. i want to count those that appear on the breakfast, lunch and dinner differently. how can i do that?
thanks

• Hi Abraham,

If you can provide more details about your data structure, we will try to make a formula.

312. Wale says:

Outlet type: A, B, C, D

Expected Product range by outlet type

A - 20g,30g,170g,410g,400g tin,400g pouch,uht fc,uht lf
B - 20g,30g,170g,410g,400g tin,400g pouch
C - 20g,30g,170g,410g
D - 20g,30g,170g

Product presence report (Yes means available while No means not available)
Peak 170g - No
peak 410g - Yes
peak 30g - Yes
peak 20g - Yes
peak 400g Tin - No
Peak 400g P - No
Peak 900g - No
Peak uht fc - No
Peak uht lc - No

Hi Svetlana, i have above merchandizing report data. Based on the outlet type and the lists of products expected there and the product presence report, i want a formula that will count and return the number of products present in the outlet by considering the type of outlet (A,B,C,D). Thanks

• Wale says:

Dear Svetlana,

Still waiting please, i believe you are working on my request. Thanks

313. rukuruku says:

YES NO
x
x
x
100%

hi,how can I automate, such that when is placed in a NO column then the percentage reduces eg

YES NO
x
x
x
67%

regards

314. rukuruku says:

Svetlana Cheusheva,hope yo helping me,thanks

315. andrea says:

I am working with excel to draw up characters in columns consisting of random alphanumeric combinations. For ex: TRG, 0RG, 12TT, in column organization. I want to list the number of each individual character and how many times the character appears within each column. .. IE: 0=1, R=2, T=3. The countif function completely works for alphabet characters, but not for numeric when next to alpha.

The 0 numeric when beside an alphabet character is simply not recognized. I've attempted some * with no luck..I'll keep reading could you please provide some insight?

• Hi Andrea,

You can use the following array formula (Remember to press Ctrl+Shift+Enter to enter it correctly):

=SUM((LEN(A1:A100) - LEN(SUBSTITUTE(A1:A100, 0, ""))))

Where 0 is the digit you want to count.

Instead of supplying a numeric value in the formula, you can place it in some other cell, then the formula will be slightly different, as in the following example: Array formula to count any given character in a range.

316. Melissa says:

Hi Svetlana,

I'm using excel mac and I'm trying to countif a date range and using a separate worksheet the name of the worksheet is KIDS and the column in that worksheet is R. Please where am I going wrong, below is an example;

=COUNTIFS(KIDS!R:R, ">01/07/2015", KIDS!R:R, "<31/07/2015")

Thanks heaps :)

317. Cathal says:

Hi there,

I have to get 2 columns of data from a data sheet.

Basicly Column X is a name and Column Y is an answer of one of six results.

While Column X will remain the same I need to see how many of each of the results appear for Coloumn y of the 6 criteria.

I used =COUNTIFS(A1:A941, "JOHN", C1:C941, "CLOSED")

But it wont tell me how many of the columns under Johns name are closed

Can you help?

318. Tommy says:

Hi,

I want to count the number of cells from "today's date" to a specific charater, lets say S.

For example: If todays date where 15:th of july and I have my sheet like this:

Dates:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
S P T L L

I want it to count the number of cells, regardless if the cells are blank or have a different character(P,T or L) from 15th of july until it finds the first S, thus 15-3=12.

Hope you can help.

Thanks

• Tommy says:

Somehting went wrong when I sent the question as seen above, it would be: 15-1=14.

319. MBIBSC85 says:

Below is my formula, it is counting everything in the first () and not the second. Please help.

=COUNTIFS('513TH BYNAME'!G2:G1500,"09L5",'513TH BYNAME'!H2:H1500,"OO")+ COUNTIFS('513TH BYNAME'!I2:I1501,"9J,9Y,9T,9V,9R,9Q",'513TH BYNAME'!K2:K1501,"*2015")

Does not count the second COUNTIFS('513TH BYNAME'!I2:I1501,"9J,9Y,9T,9V,9R,9Q",'513TH BYNAME'!K2:K1501,"*2015")

320. Javier says:

Hi Svetlana -

I was wondering if you could help me on how I can use Countif or Countifs to check a list/colmun of different words, tell me how many times it appears in a search of about 300 rows. I have about 70 different words to search in it would be time consuming if I just simply worked one at a time using =countif(A1: LP12,"Different Words to Look up").

I believe there is a formula that I can just either drag and use to look all at onces.

Thanks,

Javier

• Hi Javier,

If you have a list of those 70 words in some column, you can reference the first cell in the formula, say \$A13, and then copy the formula down to other cells:
=COUNTIF(\$A\$1:\$LP\$12, \$A13)

321. Bob says:

I'm trying to create a countif formula to avoid duplicate records. Two of my headings are "Name" & "Date" my goal is to NOT enter a name using the same date more than once. Can you help?

322. Mark says:

Svetlana, I've got a workbook where I'm tracking attendance at various classes. Each class and attendance roster is a separate tab. I've got an employee summary tab where I've set it up to count the number of times each employee appears across the various tabs. I'm using this formula =SUMPRODUCT(COUNTIF(INDIRECT("'"&\$A\$2:\$A\$15&"'!A1:M200")A29)) for that and it's very successful. A2:A15 are the tab names, A1:M200 the range on each tab to search and A29 is the employee name. So far so good.

Now I need to do a similar count, but only when the class is required by the person's title. I had to set up the names of the various tabs (see above) in order to make the previous formula worked. So I just made it a grid by adding titles across the top and indicating which class was required for each title. I'm just lost now on how to make that work with a formula. Any help is appreciated.

how can i count 2 categories in same raw ex n , b
n b n b n b b b b b n b n b n b n b n b

324. Cindy says:

Hello,
I want to count the number of concentration values in a specific range (4,000) for a subject list. However,each subject (listed by patient ID) can have 2-5 concentrations (each listed in a separate row), and some are in more than 1 range (e.g. low and high). I would want to group by patient ID, keeping all their samples together, but sort the data into those patients with all samples being in the low range, all in the high range, all in the middle range, and then another group with those that are mixed ranges.

325. sidheswar says:

2-2-2= 6
2-2-2= 6
6-4-6= 16
4-4-8= 16
2-4-2= 8
4-4-4-2= 14
how ca we add these items and get sum

• If you simply want to some cells row-by-row, you can use a usual SUM formula, e.g. =SUM(A1:C3)

326. Partha Dutta says:

Hi Svetlana,

I am having a problem with countif function. I want to test a column data with dates and count them by the weekdays. Here is what I wanted to do, R18=COUNTIF(B3:B12,WEEKDAY(B3,1)=Q18). It is not working.
How to count a column data by testing those datas with another function(Weekday)?

327. Enrico MArgioni says:

Hello

I am trying to use a countifs combined such as shown below. What I am am trying to do is with to check each of the cells referenced in the range to see if the text in the criteria is present. Overall intent is to determine a percentage of compliance. So in this case if each of the cells referenced are determined to be met in the criteria check then the cell where this formula would be 100%. If only part or none of the criteria are met then the cell would be the percentage based on the counts. I have tried adding an If before the count but it seems something is wrong.

=(COUNTIFS(BA5,"2008 or Above")+COUNTIFS(BA5,"Not Applicable")+COUNTIFS(BC5,"Has Maintenance",BC5,"Not Applicable")+COUNTIFS(BR5,"All Physical Servers Have Maintenance",BR5,"Not Applicable"))

328. TONY says:

Is there a way to calculate the amount of hours in the same cell if I inputted 8am-4pm? the goal is to use one row to input the time as shown above and a row to show the total amount of hours. in this case would be 8.

329. patrick says:

wow thanks, i know now how to use count-if

330. Jerin says:

Hi team,

I had a query about countif and sum if and I have sent an email to the support email ID. I just want to know how long does it normally take to get a response.

cheers

331. Jeff says:

Hi Svetlana,

I am attempting to count rows that contain a specific piece of text in the C column as well as a specific number in the I column. The formula I am attempting to use is:

=COUNTIFS(Sheet9!C2:C163,"="&B3,Sheet9!I3:I163,"=7")

The formula returns a #VALUE! error but I am unable to locate it. Would you please point out what I am doing wrong?

Thanks.

332. rahul says:

I want to search an array where one value is an exact match, and the second value falls within a range (specified by two columns – an upper and a lower bound)

333. Chuck says:

Hi Svetlana
I am attampting to count repairs done by tool type (column A), serial number (column B), and technician (column C). I've tried this and it doesn't seem to be working:
=COUNTIFS(B3:B25,AA17,C3:C25,"<=2945",F3:F25,AA10)

AA17 is a specific tool type
AA10 is a specific technician

334. shane says:

hi there. hope you can help. i have table of 2000 lines. i have created count formulas to go over the entire data...=COUNTIF(J5:J4000,"=2")
issue i have is that i want to break it down per person (far left hand column) then still use the same formulas to get the same formula but only for the person as well as the whole 2000 lines data.

in the 2000 line data, there will be approx 15 names that i need individual results from... hopes this makes sense and you can help

PERSON JOB NUMBER CALLS MADE TODAY
BILL 432 1
BILL 4 1
BILL 34 1
BILL 325342 2
BILL 5 2
PETER 342 2
PETER 5432 2
PETER 5 2
PETER 52308 2
PETER 432 2
PETER 5 2
PETER 432 2
PETER 5 2
STEVE 3425 2
STEVE 3425 2
STEVE 434 5

335. Quin says:

Hello Svetlana,

I need help analyzing data in Excel. I am working on my thesis and the data was sent to me from a program via Excel.

1. I saw the how to create a drop down in excel. How can I convert the numbers in the excel file to for example yes = 1 and no = 2?

2. Once I am done with that portion, do you have a link where I can analyze the information?

Thank you

336. James Burney says:

Hi Svetlana,

Could you please help? I am attempting to count a different range of between numbers and assign them a score if they hit the specific criteria.

Example.
Monthly Unit Sales 100 - 500 = 1 point 500 - 1000 = 5 points 1000+ = 10 points.

Curious to what formula I can apply as there are at least 8 columns requiring the same formula & I then need a total score when all columns are combined.

Thanks
James

337. Murali says:

Greetings!
Whilst adding attendance using countif function if P = 1 shift and the person continues for the second shift which is marked as 2P = 2 how can the two be added? ie if there are 30 days and the person has worked 1 shift extra it should count as 31 Thanks Murali

338. Shashi Bhushan says:

Hello Svetlana,

Hope you are doing great.
I would like to know how to know if there are multiple combinations of two rows.
For Example:
Column 1 | Column 2
------------------------
A | value1
A | value1
A | value2
B | value3
B | value4
C | value5
C | value5
D | value6

Here 'A' from Column 1 has multiple values in Column 2, namely 'value1 and value2'.
Similarly 'B has 'value3 and value 4'.
Suppose there are thousands of such records.
And I would want to know which elements in Column1 has different matches in Column 2.

Any help is much appreciated.

Thank you,
Shashi Bhushan

339. PONRAJ says:

how to enter only 2 item in a CELL in ms excel 7. EX - IN A CELL ONLY YES / NO --- CAR / BUS

340. Kody says:

Jamaica
Jamaica
Hi, so this is my data:
USA
Jamaica
Bulgaria
USA
France
Jamaica
Russia
USA
USA

and the question asks to use the IF or COUNTIF function to find the number of countries (which is 5.... but what is the formula?)

341. Andreas Funk says:

Hello!
I am having an issue related to this article that I just can't seem to solve.
I get an excel dump every week with all cases handled by a support organisation.
My job is to summarize the number of cases created by a member of a specific group of people
I am also supposed to summarize the ammount of cases that have been created by one specifik user and where the case is assigned to a specifik case group.
The file is in 4 collumns. Collumna A is case ID which is not interesting to my assignment.
Collumn B is the name of the case group that the case is listed under.
Collumn C is the time the case was created
Collumn D is the name of the person that created the case

Collumn D can contain up to 50 different names but I need to count the ammount of cases related to a specifik list of 12 names.
I need to count them per day so between midnight one date and the next.

Can this be done using a set formula, so that I can just paste the new excel dump in to a set worksheet and get the informations directly?

Would very much appreciate your input.
Best regards
Andreas

• Andreas Funk says:

I can send an example by email if you want. Just write me on

andreas . funk /at\ umea . se

Thanks!

342. Rishi Bansal says:

Hi

I am facing a problem to plot the value against "Logical_Text", Wherein Same "Logical_Text" are in Col. "A" (in multiple rows) and A Value has been given against one of the "Logical_Text" in Col. "B". I need to plot the Value given (in Col. "B") against the "Logical_Text" in Col. "A" on all the rows against "Logical_Text" in Col. "A".

Thanks & kind regards

Rishi Bansal

343. Srikanth says:

Hi Svetlana,

I have a data which a row consists of both Text (Y & N) and Dates (29/09/2015).

I want to count the number of cells that contain both Y and date but donot count N.

Please suggest me a formula to count number of cells.

• Srikanth says:

All Y , N, Date are in different cells.

344. Melony says:

In column Q, I need a formula that will count all of the cells that have ROH,EXEC,and STAFF. I tried using the format below but it did not work.

=COUNTIF(Q11:Q275,"ROH") + COUNTIF(Q2:S11,"EXEC") + COUNTIF(Q11:Q275,"STAFF")

• Alexander says:

Hello Melony,

In your formula, the second COUNTIF contains a different range, and this may be the cause of the problem.
Try the following formula:
=COUNTIF(Q11:Q275,"ROH") + COUNTIF(Q11:Q275,"EXEC") + COUNTIF(Q11:Q275,"STAFF")

345. Liam R says:

Hi,

I would like to ask for some assistance in the COUNTIF formulas. I have a comprehensive list which gets fed information from another spreadsheet. It looks in these cells for the word(s) im looking for.Now in the same cells for each one, i'm looking for about 100 different words which means i need to write the "word" in every formula.The cells are separated and cant be group-selected Overall due to my spreadsheet and how broad it is for what I want and need, ill need to change the word in the equation approximately 1600 times. Not really in the mood to do that if possible.

Heres the formula example Im using right now. Can I reduce the size of this formula and make it more concise. If you want me to send an example spreadsheet, im willing to do so.

=COUNTIF('Group A'!E6:I6,"")+COUNTIF('Group A'!P5:T5,"")+COUNTIF('Group A'!E13:I13,"")+COUNTIF('Group A'!P14:T14,"")+COUNTIF('Group A'!E21:I21,"")+COUNTIF('Group A'!P22:T22,"")

Thanks :)

• Alexander says:

Hello Liam,

• Liam R says:

346. Fiston says:

Hi,
I am struggling with my data in which I have a list of people with their ratings (ranging from 0 to 5) the past 5 years and I need to separate the people who had at least a rating of 4 twice during the past 5 years.
I tried a combination of IF and Countif but can't seem to get it right. Your help would be highly appreciated.
Thanks

347. Rishi Bansal says:

Hi Alexander

Kind regards

Rishi Bansal

348. K.Nagasundar says:

which formula used for the below table

Book stock Physical stock Difference stock (+)stock (-)Stock

Physical stock (-) Book Stock is difference stock, if difference stock comes (+) go to (+)Stock otherwise comes (-) go to (-) Stock

how to use the If formula, explain clearly

349. ejoy says:

Hi,

Good day!

I was hoping you could help me on this, I need to create a statement comparing values only in one column. If there are duplicate values, the first value will be tagged as "primary" and the succeeding "secondary"
Is this possible?

Kind regards,
ejoy

350. Rajkumar says:

True, Microsoft Excel 10 is the value of the F column. 6 When the value is put in the column H 4 value in column G to be put. Examples of this type of formula.

351. Rajkumar says:

True, Microsoft Excel 10 is the value of the F column. 6 When the value is put in the column H 4 value in column G to be put. I have required this type formula.

352. Jo says:

Hi,

I need to count the number of postal codes within a certain range, e.g. between 2420 and 2490. I have been using the countifs function, however it does not seem to be counting all the postcodes that exist within the range specified (=COUNTIFS(\$A\$2:\$A\$10000,">=2420", \$A\$2:\$A\$10000,"="&\$H2,\$B\$2:\$B\$10000,"<="&\$I2) - where h2 is a date and so is I2). I have also found that this formula is not counting all the dates that fall into this range in column b. Is there something also wrong with this formula?

I would appreciate any help I can get!

Jo

353. Jo says:

Oh dear - that did not post correctly!

The first formula is =COUNTIFS(\$A\$2:\$A\$10000,">=2420", \$A\$2:\$A\$10000,"="&\$H2,\$B\$2:\$B\$10000,"<="&\$I2) (where h2 and I2 are dates) and b is a column of dates

Hopefully that helps clarify.

Thanks,
Jo

354. Rhett says:

I'm trying to calculate how many times a state appears in a range. =COUNTIF('[2State Calendar.xlsx]Calendar'!\$J\$6:\$K\$11,"*NY*")

I want to calculate multiple states appearing in the same range. Is this possible?

355. Dhavl Oza says:

I want to compare two rowed cell values and find minimum between both,Minimum result number will be multiply by 3.5 and stored to third cell.

10
9
9*3.5

356. Syida says:

Hi,
I need to simplify no of item for selected branch in a month? or to combine below countif formula.

1. TO COUNT NO OF ITEM IN JULY 2015
=COUNTIF(I:I,">=07/01/2015")-COUNTIF(I:I,">07/31/2015")

2. TO COUNT NO OF ITEM FOR BRANCH CODE "AG"
=COUNTIF(A:A,"=AG")

357. John Howell says:

I've hit a brick wall with sheet that I am trying to Create to create a rota from a list of tasks that need to be performed at different times of the day. I have a list of tasks that must be performed and a person's name is input against it and the name is the automatically transferred to the rota by the formula.

I have set up the rota so that the cell under a specific time and persons name tests if the person has been allocated the task with the formula =IF(F8="Jane","task1","")
so that when Jane looks across the rota against her name she can see the tasks that have been allocated to her. This is working perfectly.

The problem is that at 12:00 Jane might be allocated any one of two tasks that need to be completed at the same time or no task at all. I have been trying to use the above formula and adding a second If statement onto it to test another cell if the first statement results in FALSE.
and this is not working.

Can you help?

358. Chris says:

In Example 4. COUNTIF formulas with OR logic is there a reason you did not use the sum of the array?
=SUM(COUNTIFS(A2:A11,{"Product1","Product2}, B2:B11, 0))

I am also wondering the best way to count rows with multiple OR logic? I know the following doesn't work with 2 columns of OR logic, but what would be the best way to accomplish this?
=SUM(COUNTIFS(A2:A11,{"Product1","Product2}, B2:B11,{"0","14"}))

359. Eric says:

Hello,
This is my current formula for two different columns: =COUNTIF(B12:AF12,"lt") and =COUNTIF(B12:AF12,"d").
I am tracking Time, Attendance and other things for employees. If they have a late on one day (cell) I put LT and it counts it in the LT Column. If I put D for Disciplinary action it will count it on the D column but what if I have a late and Disciplinary action on the same day? I want it to count it on each different column and cell needed.
Thank you,

Eric

360. C Bullock says:

I would like to use a countif function in a pivot table. I have four columns of data with different suspension codes in each column. I might have violation "I01" appear in any of the four columns. I want to count how many times "I01" is in any of the four columns. I love the pivot table because then I can use them to count how many times this infraction is reported at each of the schools I work for. Any suggestions would be appreciated.

361. apsar says:

hi dear i dont have knowledg of excel formulas so plz guide me how learn a advance excel formula.

362. MOhamed Ibrahim says:

HI,

I HAVE A EXCEL SHEET CONTAINS PERVIOUES HEALTH CHECK UP DATES AND I HAVE CREATED GRADES ACCORDIGNLY,
WANT TO FIX DATE FOR THE GRADES AS MENTION BELOW
IF GRADE A MEANS HAVE TO GET THE DATE OF NEXT HEALTH CHECK UP FROM PREVIOUS CHECK UPDATE WITH 180 DAYS ADDED

363. Mike says:

I have 5 columns,
1. Date In
2. Time In
3. Date Out
4. Time Out
5. Date to count
I have a long list of these dates and times and want to find out by date and hour how many occurrences.

7a-8a
8a-9a
9-10a etc.

How do I assemble countifs to produce the hourly data?

Countifs(A1:A31,E1,B1:B31,">="&TIMEVALUE("7:00 AM"),B1:B31,="&TIMEVALUE("7:00 AM"),D1:D31,<="&TIMEVALUE"("8:00 AM")

I can't get to this to work so it shows between the in and out times and date.

Thank you,
Mike

364. Lyle says:

=COUNTIFS(C12>F12,">=1", C21>F21,">=1") ???

Basically looking to have a total for wins and losses in a sports results column.

So if 71 (C12) is greater than 70 (F12) give it a value of 1 (a win), then add that to the next line (C21) if that is a value of 1 as well.

365. Humberto says:

Hi,
Example:
- M M N N
M V V D D
M V V V
N D V D E
N V E E
WE need do know how many "V" we have in the table with the conditions (Horizontal/Vertical) M/N ; M/N ; N/M ; N/M
The result is:
M/N = 1
M/M = 4
N/M = 2
N/M = 0

366. Taylor says:

I am working with something like this:

COLUMN A COLUMN B COL.C COL.D
uc856489 85694 SXTI y182
54852 SXTI y345
uc854585 95484 TAMI y652
45584524 6382 TAMI Y212
54585 SXTI Y121
UC845845 12548 JRWS Y222

I'm trying to find a rule that will let me see if there is get a count of empty trailers on the yard by scac code "SXTI" (COL C) BUT I don't want to calculate the loaded SXTI trailers (WHEN COLUMN A SHOWS A 3 STARTING WITH UC* OR 455) I was going to make a line for each scac code on a different sheet with the rule cell next to it just showing the number. Is there a way that will work for this? I have tried many combinations but can't seem to get it to work.

367. shilna says:

I want to count how many nonzero cells exists. All cells are time formatted. I am populating with elapsed time.
Column A - 0:00
Column B - 8:05
Column C - 9:22
I should get result as 2.

368. Kim says:

I am trying to figure out a formal that would highlight a certain cell if a X appears a certain number of times within a certain data range. I am trying to work on an attendance sheet where i need the persons name to highlight if the attended 3 items in one data range and 2 in another data range and have data in another field that contains a drop drown list. I think i need to do a sum countif but I can't figure out how to set to highlight if a certain number within the data field is meet.

369. DAVZ says:

help on how to set COUNTIF to count only cell with >=25% but <=29% only,, meaning count only cells when it contains 25% and below 30%

370. beesee says:

From the example above, if you use the following:

=COUNTIFS(B2:B11,"=0", C2:C11,"=0", D2:D11,"=0")

it will count from the row only if all three meet the criteria

however if you do:

=COUNTIFS(B2:B11,">0", C2:C11,">0", D2:D11,">0")

It will count from the row if any one, two, or all of the values is greater than 0

q1: how is that logical?
q2: How do I do the operation so that it counts only if all three satisfy the criteria

371. Corey Worster says:

I'm trying to get a total count of the values in one column, i.e. "Y100" and with all rows that contain that y100 count the number of cells in another column. i.e. "A". I've tried =COUNTIFS('Mods (Entire)'!B:B,"Y100",'Mods (Entire)'!C:C,"A") and =COUNTIFS('Mods (Entire)'!B:B,"=Y100",'Mods (Entire)'!C:C,"=A") both come back with a count of zero. Any and all help is greatly appreciated, will send sample if needed.

372. Sanjeev Kathuria says:

I have data in Column F. The data is like either "M" or "MH", either "E" or "EH", either "N" or "NH". This is for counting Shifts.

I am using formula to count : =COUNTIFS(F4:F30,"=E",F4:F30,"=EH") for counting Evening Shifts. But it is givining me value as 0 (Zero). Please let me know where am I wrong.

373. Sharat says:

Hi Svetlana,

If there are two columns A and B with following data:
A B
Apple 1
Banana 1
Orange 3
Apple 1
Apple 1
Banana 1
Banana 1
Banana 1
Banana 1

In the above data I would first like to search in column B for value 1 and then count only unique values in column A in such a manner that the function returns 2 (i.e., Apple and Banana). Is this possible and if so how can it be done?

374. chuman says:

when i scroll from one column to another column it have changes of his values

ie

=+COUNTIFS(\$G\$5:\$G\$38,"ACTUAL",\$H\$5:\$H\$38,"D")

another one =+COUNTIFS(\$G\$5:\$G\$38,"ACTUAL",\$H\$5:\$H\$38,"D") only copied where i want =+COUNTIFS(\$G\$5:\$G\$38,"ACTUAL",\$I\$5:\$I\$38,"D")

375. ravi says:

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.

376. Yuvaraj says:

Dear All,

I need your help to show the total time spent between multiple two cells repeatedly.

For example, I'm starting my work by capturing the start time in A1 & ending at A2 and I'm getting the difference in A3 using the formula =A2-A1. After a short break, I'm again starting the time in A1 & ending in A2. Then what is the formula to be used to calculate the total timing spent in A4.

377. chuman says:

my 1st column data is- =+COUNTIFS(\$G\$5:\$G\$38,"ACTUAL",\$H\$5:\$H\$38,"D") this one

when i scroll it 2nd column the data is- =+COUNTIFS(\$G\$5:\$G\$38,"ACTUAL",\$H\$5:\$H\$38,"D") this one

my question is why not data scrolled by column reference,where ever the data wants =+COUNTIFS(\$G\$5:\$G\$38,"ACTUAL",\$I\$5:\$I\$38,"D")

plz chk and tell me the soluation

Thanks

CHUMAN

378. Brent says:

Trying to create a count for check (Tick) marks (where "P" is a tick). I can have multiple ticks per box in Excel. Currently it wants to add each tick. If I add 1 more tick to a box (2 ticks in a box) it will subtract it instead of add on to my current total. How do I fix this?

My current syntax:

=COUNTIFS(C7:C25, "P", C7:C25, "P")

Thanks.

379. swati says:

There's a File A and a File B, both in xls
Both have the two columns each of the same names, say, "1" and "2".
File A has 100 records. File B has 150 records.
We need to add a column "3" to File B, and put a value 'NotFound' in it if the value in "1" is not found anywhere in A's column "1".
After that is done, we need to repeat, in reverse. That is, add a column "3" to File A, and put a value "NotFound" in it if the value in A's "1" is not found anywhere in B's column "1".

380. Faruq says:

Trying to simplyfy the below functions:
C D
0 On Board
0A On Board
1A
3 On Board

=COUNTIFS(HK!C4:C8,"=0",HK!D4:D8,"=On Board")+COUNTIFS(HK!C4:C8,"=0A",HK!D4:D8,"=On Board")+COUNTIFS(HK!C4:C8,"=1",HK!D4:D8,"=On Board")+COUNTIFS(HK!C4:C8,"=1A",HK!D4:D8,"=On Board")

Tried this ... but no luck :(
=COUNTIFS(HK!C4:C8,{"=0","=0A","=1","=1A"},HK!D4:D8,"=On Board")

Thanks

381. trent says:

Ok here is my question I have a multiple part number in one row and a serial number for each part number entered in a second row. I would like to count only the highest serial number for each of the different part numbers is this possible?

382. David says:

Hi Svetlana. Is it possible to use Countifs to pick up one of the criteria from a specific cell? I have tried, but it only ever returns a 0 value.

For example, I have a list of companies in column A, the month in which an order was delivered in column B, and a note of whether the goods were OK or faulty in column C. I want to find out how many orders were delivered faulty in a given month.

The formula
=COUNTIFS(B3:B100,"=03 (14/15)",C3:C100,"=Faulty")
works fine, but would it be possible to use another cell (let's say B102) to enter the month I am looking for and get Countifs to pick up and use the criteria from that cell?

Many thanks.

• Hi David,

If my understanding it correct, you want a formula to "extract" a month number (03) from values in column B. If all the values in B have the same pattern like 03 (14/15), including a space before the opening parenthesis, you can use the following wildcard in criteria1:

=COUNTIFS(B3:B100, "03 (*", C3:C100,"=Faulty")

As for entering the month is a separate cell, probably it's also possible, but I cannot think of such a formula at the moment, sorry.

• David,

Here's how you can count cells based on the month number in cell B102:
=COUNTIFS(B3:B100, B102&" (*",C3:C100,"Faulty")

Important! You should enter the month number in cell B102 exactly as it appears in column B (03 in your example). And since Excel cuts off leading zeros in numbers, 03 shall be entered in cell B102 as text (Home tab > Number group > Text).

383. Sasha says:

How do I make a formula that states that column D is 30% of Column D?

• Hi Sasha,

Probably there's some misprint here. Column D cannot be 30% of the same Column D :)

384. dhaval kartikeya says:

i have a data in excel sheet with cell content like this
the a 1,2,3
the p 1,2,3
how do i count the number of commas in the range with cell content.
i want the outcome like this
cell_contain count
"the a" 3
"the p" 3
i'll be very thankful if you provide answer for my query

385. Greg says:

Hi Svetlana,

I'm trying to count the instances a value is less than zero given two dynamic column ranges match. They are named ranges 'universe' and 'markets'. I have the following formula to give me the total number of instances that I have a match but I'm not sure where to add the criteria to return how many of these matches are less than 0.
{=COUNT(IF(ISNUMBER(MATCH(universe,markets,0)),\$A\$7:\$A\$35))}

Thanks for having a look!

386. Mayur says:

Hi Svetlana,

What formula can be used to find the count of cells that contain the text that is present in some other cell?

For eg. i can hardcord the formula like this: COUNTIF(\$E\$3:\$W\$17,"Svetlana")

• Mayur says:

*hardcode

• Hi Mayur,

Nothing prevents you from supplying the cell address to the formula, e.g.:

=COUNTIF(\$E\$3:\$W\$17,\$A\$1)

387. Sonia says:

Happy New Year Svetlana!

I would like to know how to find text in a range on a sheet that will display in a cell based on the criteria inputted in adjacent column:

So, I have an excel sheet "other sheet" showing a database of reg plates and vehicle type for a fleet of vehicles. On another sheet "current sheet", I need the vehicle type to display automatically in one column (see below where it says formula) when I type the reg plate number in the adjacent column.

THANKS :)

CURRENT SHEET OTHER SHEET
REG TYPE REG TYPE
A B 123 NISSAN
1 123 formula 321 LANDROVER
2 321 456 BUS
3 456

• Sonia says:

"CURRENT SHEET"
REG 123, 456, 345
TYPE FORMULA REQUIRED

"OTHER SHEET"
REG 123, 456, 345
TYPE NISSAN, LANDROVER, BUS

388. yash pandey says:

Hi Svetlana Cheusheva Mam, Please solve my problem. There are subject codes and grades scattered in various columns & rows in excel sheet. How can i extract counted grades & codes in one cell of excel sheet jointly. What formula use in it. for example excel sheet given below yellow for your knowledge subject code 41 and next row grade A1 A2 B1 B2 C1 C2 D1 D2 .HOW TO COUNT TOTAL 41 A1 IS ............?
CANDIDATE NAME SUBJECT CODE MRK GRD SUBJECT CODE MRK GRD SUBJECT CODE MRK GRD SUBJECT CODE MRK GRD
RAM 30 99 A1 41 95 A1 30 99 A1 41 95 A1
SHYAM 41 99 A1 48 95 A1 41 99 A1 48 95 A1
RAJ 30 94 B1 41 88 A1 30 94 B1 41 88 A1
PHILP 37 92 A2 42 80 B1 37 92 A2 42 80 B1
PIKASO 41 80 A2 42 91 A1 41 80 A2 42 91 A1
PHAS 37 93 A1 42 81 B1 37 93 A1 42 81 B1
PARI 37 90 A2 42 70 B2 37 90 A2 42 70 B2
RAJ 30 88 A2 41 68 B2 30 88 A2 41 68 B2
RAMCHARAN 41 93 A1 42 82 A2 41 93 A1 42 82 A2
ANUPAM 41 77 B1 42 82 A2 41 77 B1 42 82 A2
VIJAY 41 80 A2 42 78 B1 41 80 A2 42 78 B1
VISHAL 41 82 A2 42 73 B2 41 82 A2 42 73 B2
VINAY 37 82 B1 42 65 C1 37 82 B1 42 65 C1
VIMAL 41 72 B1 42 67 C1 41 72 B1 42 67 C1

• yash pandey says:

• yash pandey says:

CANDIDATE NAME SUBJECT CODE GRD SUBJECT CODE GRD SUBJECT CODE GRD SUBJECT CODE GRD
RAM 30 A1 41 A1 30 A1 41 A1
SHYAM 41 A1 48 A1 41 A1 48 A1
RAJ 30 B1 41 A1 30 B1 41 A1
PHILP 37 A2 42 B1 37 A2 42 B1
PIKASO 41 A2 42 A1 41 A2 42 A1
PHAS 37 A1 42 B1 37 A1 42 B1
PARI 37 A2 42 B2 37 A2 42 B2
RAJ 30 A2 41 B2 30 A2 41 E
RAMCHARAN 41 A1 42 A2 41 A1 42 A2
ANUPAM 41 B1 42 A2 41 B1 42 A2
VIJAY 41 A2 42 B1 41 A2 42 B1
VISHAL 41 A2 42 B2 41 A2 42 B2
VINAY 37 B1 42 C1 37 B1 42 C1
VIMAL 41 B1 42 C1 41 B1 42 C1

Sub. Code Total code A1 A2 B1 B2 C1 C2 D1 D2 E TOTAL
30 6 result here
41 20 8 6 4 1 0 0 0 0 1 20 IT IS MANUALLY COUNT AND WRITE
37 8
48 2
42 20

TOTAL IS MANUALY COUNT AND WRITE. YOU C A1 A2 B1 B2 C1 C2 D1 D2 & E BLOCK
I WANT FORMULA IN BLOCK THAT AUTOMATICALLY COUNT AND WRITE . I HELP YOU FOR YOUR UNDERSTANDING . ONE LINE

There are subject codes and grades scattered in various columns & rows in excel sheet.
How can I extract counted grades & codes in one cell of excel sheet jointly. for example code 41 get A1, A2 B1 B2 C1 C2 D1 D2 & E. for example given below.

389. yash pandey says:

390. Joey says:

Hi!

I have 2 column in I need to count the date on column "B" with the corresponding text on column "A"
I want to count this : Z13-BW20D IF I HAVE CORRESPONDING DATE ON COLUMN "B"

Can some giving me a little help for this one?

Sample:
___________( column A)_____ (Colum B)

KPC164-FAB-Z13-BW20D-01#0001 19-Dec-15
KPC164-FAB-Z13-BW20D-01#0002 19-Dec-15
KPC164-FAB-Z14-BW20D-01#0002 15-Dec-15
KPC164-FAB-Z14-BW20D-01#0003 15-Dec-15
KPC164-FAB-Z15-BW20D-01#0023 05-Dec-15
KPC164-FAB-Z15-BW20D-01#0024 05-Dec-15

391. jOEY says:

A B
KPC164-FAB-Z13-BW20D-01#0001 19-Dec-15
KPC164-FAB-Z13-BW20D-01#0002 19-Dec-15
KPC164-FAB-Z14-BW20D-01#0002 15-Dec-15
KPC164-FAB-Z14-BW20D-01#0003 15-Dec-15
KPC164-FAB-Z15-BW20D-01#0023 05-Dec-15
KPC164-FAB-Z15-BW20D-01#0024 05-Dec-15

392. Mayra says:

Hello, I have been trying to figure out how to come up with a formula that counts cells from two different columns but that both represent one person. For example, I have an admitted column and a discharge column for clients. I'm looking at the first quarter of the fiscal year which counts October, November and December. However, when looking at each month individually, for example, October has clients that were admitted in August so I am trying to come up with something that counts before October but not after October as well as before November but not after November, etc. Does that make any sense? I apologize, I have been working on this for a while.

• Hello, Mayra,

393. natasha says:

i have a table of employees by department and then a breakdown per day of holiday days and i need to count the amount of holiday days by department by month. is this possible?

• Hello, Natasha,

394. ASIF says:

my problem that conditional formatting in sheet formula that if date enter than red color how cell end count automatically red color
exp:-
09/01/2016

09/01/2016

how to count two cell red any formula

395. jules says:

I'm trying to use countif or countifs to count how many times a name shows up, the catch is there are many names, and I don't want to find all of them to do =countif(a5:a25,"JANE DOE"). Is there a way for excel to count and prompt the name & qty? I'm trying to calculate how many ECNs are assigned to individuals within a large group. Thank you for your help!

• Hello, Jules,

1. Select an empty cell in your table
2. Go to the Data tab -> Consolidate
3. Select Count from the Function drop-down list
4. Highlight the column with names and the one next to it
5. Tick the left column check-box and click OK.

Hope this helps.

396. rohit says:

Count the number in Unic criteria. To say

397. Zahir says:

Hello, I have founded a very important function from this site.
thank you very much.

398. Raj K says:

How can I calculate the number of leads received during specific time ranges. E.g. how many leads did I received during 0:01:00 to 0:30:00 and so on.

I've 10,000s of leads received during 0:00:01 to 24:00:00. I am taking a time range of 30 minutes, 0:01 - 0:30, 0:31 - 1:00 an so on..

399. Charu says:

Hi I have a Excel Sheet, Where i have Numbers achieved by sales team like (In Column A)
1005,25900,5000,2005,1598,5368 etc..now i want to use a formula where i need to get the answer like..
if in Column a amount is upto 1000 then in column be it should count 2 points and if amount is in between 1001-2000 then 3, if amount is 2001-4000 then it should show as 5..

can u help on this

• Hello, Charu,

Please enter the formula below to B1 and copy it across the column:
=IF(A1 < 1001, 2, IF(A1 < 2001, 3, IF(A1 < 4001, 5)))

400. Mark says:

Hi, I need a total count how often a student has achieved a grade of C or above (A*, A, B or C). The cells containing the grades are not next to each other so I need to have a range of data counted from separate cells and presented as one number.

• Hello, Mark,

=CONCATENATE("A* = ",COUNTIF(A:A,"A*"),", A = ",COUNTIF(A:A,"A"),", B = ",COUNTIF(A:A,"B"),", C = ", COUNTIF(A:A,"C"))

Here A:A - is the range with the grades.

401. Daniel says:

Hi Svetlana,

I am attempting to create a formula which sums one cell based on two other cells meeting a certain criteria (month and a specific word).

Cells A3:A2000 are where my dates reside.
Cells B3:B2000 are where my cost resides.
Cells E3:E2000 are where the word resides.

If cells A3:A2000 has a date within January and cells E3:E2000 has the word Groceries, I would like to Sum cells B3:B2000.

Thanks for any time you have in dedicating to this question.

• Hello, Daniel,

You can try the formula below:
=SUM((B3:B2000) (MONTH(A3:A2000) = 1) (C3:C2000 = "Groceries"))

402. MAHESH says:

Hi,

I need help to complete my first excel document. details below.

I need to add criteria in Validation like this (MV1601, MV1602, its needs to continue up to MV1654 ). Can anyone please help me to finish my document soon.

Many Thanks
Mahesh

403. Susanne says:

I am trying to construct a very complicated count if formula that will track if my Cubscouts have completed certain NOVA achievements. http://www.scouting.org/stem/Awards/CubScout.aspx

For Example To earn the CubScout NOVA Award: Science Everywhere

Condition #1 Ai and Aii>1
OR
Condition #1 Bi and Bii>1
OR
Condition #1 Ci and Cii>1
AND Condition#2 A:E>0 (pick one A thru E)
AND Condition #3 A:C=3 (do all A, B and C)
AND Condition #4 A:B=2 (do A and B)
AND Condition #5 A=1 (do A)

Each requirement will be listed in a row and then the individual boys will be tracked in the columns. I can send a spreadsheet with these entered if it is easier to visulaize it that way

404. Shakthi says:

Hi,

I am very much happy to see your blog for all clarifying excel formulas..
there are two sheets around 20,000 thousand lines the projects are repeated each lines had X value in one sheet and another sheet had Y value...

My questions are...
How to accumulate value project wise between X and Y and comparing the difference...without using pivot and v lookup formula...

thanks
shakthi

405. Stacy says:

how would I countif the criteria is a date and I want to count if the year matches?

• Hi Stacy,

You can use the YEAR function to extract a year from a date.

It's difficult to suggest an exact formula without knowing anything about your data.

406. D.A says:

How to make start time and finish time between many times in excel?
For example, we want to know start time, finish time, and idle time for each hub.
The raw data :
Order Number Time Hub
A-1 1/1/16 6:49 PM BSI
A-2 1/1/16 6:49 PM BSI
A-3 1/1/16 6:49 PM BSI
...

Expected result :
Hub Total Packages Start Time Finish Time Idle Time
BSI 81 1/1/16 6:49 PM 1/1/16 6:52 PM
BNO 50 1/1/16 3:32 PM 1/1/16 6:25 PM
CKU 49 1/1/16 3:35 PM 1/1/16 6:27 PM

• Hello,

You can use the following formula to calculate the start time:
=MIN(\$B2:\$B181)

Here is the formula you can use for the "Finish time":
=MAX(\$B2:\$B181)

407. Angelozzz says:

Hi how to make formula for this long spreedsheets

building 1 Chain
building 1 Chain how many chain received by building 1 ???
building 1 cups how many cups received by building 1 ???
building 2 Chain
building 2 Chain
building 3 Chain
building 3 cups
building 3 glass
building 4 pencil
building 4 pencil
building 100 bag

• Hello, Angelozzz,

408. will says:

hi

I am doing a rota/roster based on our initials at work we rotate who works mondays. column a is date and column b is initials - how do i work out how many mondays people do by a formula?

• Hello Will,

Please try to use the following array formula:
=SUM((--(WEEKDAY(A1:A43,2)=1))*(1/COUNTIF(\$B\$1:\$B\$43,\$B1:\$B43)))

A1:A43 is the range with the dates here;
\$B\$1:\$B\$43 is the range with initials.

Once you enter the formula, you need to press Ctrl+Shift+Enter.

If you don't get the expected results, please send a sample worksheet with your data to support@ablebits.com. Please include a link to this blog post and your comment number.

409. Ed says:

Hi,
I need to count many numbers within the same cell.
For example, (10,12,13,15,16,17,18,20) is located at J16.
The numbers represent calander week numbers for an event.
At the moment I am physically counting many hundreds of these cells, high risk of human error.
Can excel count the above example would = 8
I can't use different cells, as software importing this data cannot deal with many cell references, only one cell.
Thanks, Ed.

• Hi Ed,

If we assume that there is always one more number than there are commas, you can use the following formula:
=LEN(A1)+1-LEN(SUBSTITUTE(A1,",",""))

Here A1 is the cell with numbers you want to count.

410. Abhijeet says:

I use this formula to Overlap dates with more criteria =COUNTIFS(\$A\$3:\$A\$16,H3,\$C\$3:\$C\$16,">="&I3,\$B\$3:\$B\$16,"<="&J3)

But i want to return value how to use index match formula in this formula

411. thawfeeque says:

hi Svetlana, i have created a any year calander, now i wanted to mention the holidays. here in Sri Lanka the holiday not constant for every year it is differ the other years, i think if we created a holiday chart and can match to the calender? pls help me

412. Mariah says:

Hi,

I have a spreadsheet that I am working on and cannot figure out if I can do this or if this is even possible.
So what I have is 3 columns. I have dates in 2 of them. I need the 3rd column to perform an "If" or "COUNTIF" statement that is.. If the date in column 2 is greater or after the date in column 1, calculate the days late or the difference in column 3. Is that possible? Would love to know. Thank you!!

• Hi Mariah,

You can use the following formula in column 3:
=IF(B22>A22,(B22-A22),"")

Here column A is column 1 and column B is column 2.

• Mariah says:

Thank you so much.
I got it to work but how do I get it for the whole column? I was selected on one cell and did the IF statement but how do I set it for the whole column?
Thanks again!

413. Seth says:

Hello, i am trying to count the number of customers in a list when each customer has a code of 13 or 18 and the same open dates. The last 2 in the list would "count"

Code Date Opened Name id
13 12/11/2015 Customer 4 63679
18 12/11/2015 Customer 3 63693
18 12/11/2015 Customer 2 63670
13 12/11/2015 Customer 1 63715
18 12/11/2015 Customer 1 63688
13 12/12/2015 Customer 63770
4 12/12/2015 Customer 63770

I can't for the life of me figure out how!
Any help would be greatly appreciated :)
Thanks!

414. Ramki says:

Hi Seth,

The simple solution to your query is this:

Step 1: Convert the data range to a Table [use Ctrl T] function.
Step 2: When you have done this place your cursor on any part of the table and right mouse click and select Table and Total rows.
Step 3: In the Code column select 13 and 18 and only those customers with the specified code will be listed.
Step 4: In the date column select the date you want, for example 12/11/2015. The only the specified date will be displayed.

The table can be expanded and more conditions via filter can be selected.
Further, if you have Excel 2013 you can have Slicers as filters and visually you can select the criteria you want.

Regards,

ramki

• Seth says:

Hi Ramki, Thanks for the quick reply!
Sorry, but I mistyped. The customer can has to have code(s) of 13 or 18 and an additional code(s) between 1 and 27. In the example the customer has a code 4. The dates must also match.

Basically i am trying to count the number of customers who opened multiple accounts on the same day, but one or more of the accounts has to be a 13 or 18.

Hopefully this helps clarify.

Thank you!

415. Wendy says:

My question is I have to work out the number of instances where the figures over a number of cells and rows fall outside of a specified range.
ie: column D to I from rows 3 to 233 have numerous figures, I have to work out the number of instances these figures fall outside of 4 and 7.

• Hello Wendy,

=COUNTIFS(D3:I233,">=4",D3:I233,"<=7")

If it doesn't help, please send a sample Excel file to support@ablebits.com and include a link to this blog post with your comment number.
We'll do our best to assist you.

416. Vinay Shivhare says:

Thanks Svetlana... This post has been of great help to me... :)

417. Andrew says:

Hello,

I am trying to create an excel formula based on a tree risk assessment model with three variable factors:

1) The size of part likely to fail
2) The frequency of use of the area, and
3) The probability of failure (PoF).

Each variable has a range between 1 and 6. For example if the size of part equals 3, the frequency equals 2 and the PoF equals 2 then the overall risk is given as 1/4000.

I have the table with all of the possible combinations of the 3 variables listed, each correlating to an overall risk ranking. My question is: Is it possible to create a formula based on the table to retrieve the specific risk ranking based on a random combination of the three variables?

Any light shed on the topic would be greatly appreciated.

Thanks,
Andrew

418. Erik says:

Hello,
I'm trying to find the formula for; if a given number out if entered number in a designated cell is one of the designated numbers, a give text populates in assigned cell.
* assigned roster number I.E. 300,400,500,600.. = Team 1
* assigned roster number I.E. 325,445,515,618.. = Team 2
* assigned roster number I.E. 312,422,5671,643.. = team 3
Etc.

With that: The formula will see the entered number from tab #2 cell A3 roster number.

Text populates of assigned team in tab #1 cell B2 from that data.

419. Ramki says:

Hi Seth,

I am sorry that I could not come back to you earlier as I was out of town for a meeting.

RE your query - you can select any number of codes you want from the "Codes" column drop down filter. Once you have done that select the date from the Dates column drop down filter and your result will be got.
Remember the sequence of using the dropdown filters - First the Codes and then the Dates.
Use of Slicers would be more easier for simple conditions.
Regards
Ramki

420. Mary says:

I need a range of cells to tell me a number when criteria from another cell range is met.
EX: C3:C95 is where I need to know how many if range D3:D95 is "A"

I have tried countifs in a variety of ways and can only get it to recognize 1 for any number placed in C3:C95

421. Satya says:

Hi
Please help me i have TAT data of the employee were i required Acquired tat for individual employee who is falling in which range like -
1) 0 to 5
2) 6 to 10
3) 11 to 15...
required employee wise split

kinldy help

422. Taimoor says:

Hi,

How can i count no.of employees working in different departments year wise.
For example total no. of employees is 100, how can i know that how many employees were working in different departments in different years.
Total=100
In year 2015, 10 were working in accounting, 20 were working in admn. etc etc
In year 2016, 35 were working in accounting, 10 were in R&D, 20 were working in admn. etc etc

Thanks.

423. Gab says:

Hi, Can you help me in generating a countif formula in my table? For example Cell-A states the Company Names, ex. A,B,C,D, then in Cell-B states the specific date they submit their reports, ex. 01/31/2015,02/12/206. The problem is I want to count the numbers of the report they submitted in a month. Many companies, Many reports submitted in 1 month, How can I use countif in this problem? please help me. I dont know how to use date in Countif.

• Hi Gab,

You can use the following formula to count reports, say, submitted in January 2016:

=SUMPRODUCT(--(MONTH(B2:B100)=1), --(YEAR(B2:B100)=2016))

Where B1:B100 are cells containing submission dates.

To count reports for any other month / year, simply replace =1 and =2016 in the formula with the required numbers.

424. Abhijeet says:

Hi

i use this formula =COUNTIFS('Process Data'!\$A:\$A,Tally!\$A3,'Process Data'!\$C:\$C,Tally!\$B3,'Process Data'!\$H:\$H,Tally!C\$2)

I need to use in VBA how to use Countifs Function so please tell me code foe this

425. Gurpreet Singh says:

Hi ,
I have one situation where I am stuck. I have two columns, one with car name and 2nd with number of sales in a year.
Can i use count if to get what car and how many units are sold.
I tried giving criteria as Camary and range as the no of units sold, but that did not work. I tried selecting both columns as range, but that did not work either.

• Hi Gurpreet,

You can use the following array formula:
{=SUM(B1:B10 * (A1:A10 = "Camary"))}
where
"car name" values are in A1:A10
"number of sales" values are in B1:B10
To enter this formula press CTRL+SHIFT+ENTER.

426. Hala says:

Please I have range with many numbers i want to count with excluding any cell starting with 6.

Regards,

• Hi Hala,

=COUNTIF(A1:A5,"<>6*")
where the values are in A1:A5.

427. Shane says:

I have a range of Data from sap that I need to organise into work areas by centre.

I have used COUNTIFS to gather one area work orders by work center which looks like:

=COUNTIF('RAW DATA 1 LAST WEEKS Orders '!M7:M4394,"3001-320-3523*")+COUNTIF('RAW DATA 1 LAST WEEKS Orders '!M7:M4394,"3001-320-3563*")+COUNTIF('RAW DATA 1 LAST WEEKS Orders '!M7:M4394,"3001-320-3516*")

Now I need to count the work orders that belong in the formula above, but also have *TECO* in the J column of the same page?

• You should use the array formula to solve this task.
For example:
{=SUM(1 * (LEFT('RAW DATA 1 LAST WEEKS Orders '!M7:M4394, LEN("3001-320-3523"))="3001-320-3523") * (IF(ISERROR(FIND("TECO", 'RAW DATA 1 LAST WEEKS Orders '!J7:J4394)), 0, 1)))}
To enter this formula press CTRL+SHIFT+ENTER.

Hi,
I have Date and times for different days, eg
3/6/2016 10:35
3/6/2016 10:50
3/6/2016 10:59
3/7/2016 11:45
3/7/2016 11:50
3/7/2016 11:53
3/8/2016 9:09
3/8/2016 9:27
3/8/2016 9:56
3/8/2016 9:57.
All This is Row A. in Cells
C1 I have 3/6/2016
C2 I have 3/7/2016 and in cell
C3 Ihave 3/7/2016.
in D1, I want to Say " countif,range A:A contents a date like in C1".
i.e, count the cells in range A:A that contains the dates without time.
How can I possibly do this??

=COUNTIF(A1:A15, "<>*:*")
where the date and times are in A1:A15.

429. Mangalsinghthakur says:

Please all Excel Formula Example Send me

Thanks

Regards

Mangalsingh
9202267674

430. Yuni says:

Hello,
I'm looking for a way to count the occurrences in a column where two or more consecutive values are grater than 5.
for example:

Column A
Row 1 0.61
Row 2 0.62
Row 3 5.12
Row 4 6.34
Row 5 3.58
Row 6 5.8
Row 7 0.62
Row 8 13.62
Row 9 5.09
Row 10 7.65
Row 11 0.61

In this example the result from column A would be two (2). A3 and A4 is one and A8-A10 is another. Row 6 is not counted because the value right before or after is less than 5.

• Hi Yuni,

Looks like you need a VBA script for this task. Sorry we can't help you with this.

431. BHIE says:

How do I count from non-adjacent cells with multiple criterias?
I am working on our schedule per week, I want to know the headcount present every four hours in a day including the overtime (criterias are advance, extend, training, blank etc.)I used the countif formula but it is so long due to multiple criterias and non adjacent cells.

• Hi BHIE,

432. Jayakumar Krishnamoorthy says:

I looking for a formula which could meet multiple criterias. I looking for a specific manufacturer (say dell) on first column, if the condition meets, i'm looking for specific model (say dell lattitude 630) on second column, if the condition meets, i want to count the number of windows 7 machines which is present on the third column. I'm using sumproduct to total across the sheets for one criteria, now i want to use it for multiple criteria. We use filter to filter column by column to get that. Please help me.

• Hi Jayakumar Krishnamoorthy,

=COUNTIFS(A1:A10, "dell", B1:B10, "dell lattitude 630", C1:C10, "windows 7")
where
manufacturer values are in A1:A10
model values are in B1:B10
os version values are in C1:C10

433. Charmaine Barrett says:

I currently have a yes/no spreadsheet with approx 30 individual questions listed down the cells. 12 of these questions are mandatory and have a 0.3666 percentage weighting allocated to each (Total for all mandatory questions is 44%). The non-mandatory questions also have different percentages allocated to them totaling up to 56%.
If a client does not answer one of the mandatory questions, then the spreadsheet should show 'FAIL' against that company, but because of the percentage number against the other questions, the result is showing #value!
How do I get this field to say FAIL (text) if one of the mandatory field has 'no' in it and show a percentage (number) if all mandatory have 'yes' against them

Currently have -
=IF(COUNTIFS(R6C6:R43C6,"=Mandatory",R[2]C:R[39]C,"=No")>0,"fail",COUNTIFS(R6C6:R43C6,"=Mandatory",R[2]C:R[39]C,"=Yes")*"0.03666")+(COUNTIFS(R6C6:R43C6,1.5%,R[2]C:R[39]C,"=Yes")*0.015)+(COUNTIFS(R6C6:R43C6,3%,R[2]C:R[39]C,"=Yes")*0.03)+(COUNTIFS(R6C6:R43C6,4%,R[2]C:R[39]C,"=Yes")*0.04)

• Hi Charmaine Barrett,

434. Kingsley Odu says:

Hello,
My data set consist of several sales entries like; date, rep name,location,region,item and if sales was done at POS which is depicted with a "Y" or an "N". In my analysis, i need to show the number times the letter "Y" appears for each sales person using entry date and reps name.
E.g =countifs(b2:b50,12/03/16,D2:D50,JOHN,H2:H50,"Y").

Regards

• Hi Kingsley Odu,

=COUNTIFS(B1:B50,"12/03/16",D2:D50,"JOHN",H2:H50,"Y")

435. Cheryl says:

I am trying to use CountIF to determine from a data dump the number a user has from different categories of work. For example Column A has various names, and column D has a category of Expenses, and Column F has Food as a category.

I can't seem to get this to work it keeps giving me an error and the the totals I want are how many Barbara completed from Each category, then How many Sandra did from each category. The totals will be on a different worksheet in the workbook than where the action data is sort of a summary sheet.

• Hi Cheryl,

436. JINU says:

Hi,

I have 2 columns, say equipment model & location name . I need to count the location name by the equipment model.

model location
aa 1a
bb 2b
cc 3c
aa 4d
cc 1a
dd 2b
bb 1a
bb 2b

I require the result as count of 1a in aa = 1, 1a in bb = 1, 2b in bb = 2. can you help me with the correct formula.

437. Ulhas Balaji More says:

Quantity pcs/ctn Carton to be created Left over Quantity
725 30 24 5

What will be the formula used for "Carton to be created " and "Left over Quantity"?

438. Monas Haregot says:

Hi Renat!
Many thanks for your great help.
I have this formula posted by Jeff at Excel University Website. It works very fine for me and i found helpful since it uses columns header and structured data reference.Above all the formula is very reliable and consistent.However, I tried to copy modify same formula in another cell to provide also for countifs function i.e to calculate the number of value items added in the SUMIFS function by simply replacing the word " SUMIFS" in the formula with "COUNTIFS" but it doesn't work. Need your help with this, if u don't mind.

Monas

439. Arman khan says:

Dear Svetlana Cheusheva,

I have attached a .jpeg worksheet in this comment with vertical data in it. In the column PRODUCT ID, there is different numbers start with CL00 and they repeat multiple times in the same column on the same sheet and also on the different sheet of the same file.I want to count them in a new sheet and also want the sum of consumption column in the same row.

I have set an example in the attachment by manually.

440. Arman khan says:

Dear Svetlana Cheusheva,

I have attached a .jpeg worksheet in this comment with vertical data in it. In the column PRODUCT ID, there is different numbers start with CL00 and they repeat multiple times in the same column on the same sheet and also on the different sheet of the same file.I want to count them in a new sheet and also want the sum of consumption column in the same row.

I have set an example in the attachment by manually.

Sno. Orderid OrderDate BOM Productid Specid Consumption
1 564084 4/3/2016 2 CL0039911 944518 3.2
2 564084 4/3/2016 0.5 CL0038981 944519 3
3 564091 4/3/2016 0.5 CL0038981 944534 3
4 564093 4/3/2016 0.5 CL0038981 944536 3
5 564099 4/3/2016 2 CL0039911 944545 3.2

I have more than 30 sheets of the same data but with different consumption how to count them in like I said before.

441. cindy says:

how do I add numbers if a certain product is beside it

a1 b1
200 161
300 NC
900 2100

I want to be able to automatically add a1 if the square contains one of the products in B1 -how do I do that?

442. kanasoda says:

what is the formula if I want to get the number of invoice issued per saleperson and categories by date, like from Jan, Feb, March etc. the whole year.

thank you!

443. Dr Pushparaj Shetty says:

Dear Svetlana

I have cells with multiple data separated by commas, How to use countif or countifs fuction on this data. Like the student name has multiple student1,student 2etc separted by commas. How to use countif on them.

Lecturer Name Subject Student Names
Dr Raj Gross Anatomy Lecture Student 1, Student 3,
Dr Sreekanth Gross Anatomy Lecture Student 1, Student 2, Student 3, Student 5, Student 6
Dr Hannah Histology Lecture Student 1
Dr Stella Anatomy Lab Student 1, Student 2, Student 3, Student 4, Student 5, Student 6

444. Dr Pushparaj Shetty says:

I have cells with multiple data separated by commas, How to use countif or countifs fuction on this data. Like the student name has multiple student1,student 2etc sep