Excel COUNTIF Examples – Not Blank, Greater Than, Duplicate or Unique

(1)

Excel: featured articles

How to use Excel COUNTIFS and COUNTIF with multiple criteria VLOOKUP in Excel – tutorial with formula examples

Creating a drop down list: static, dynamic, from another

workbook

Excel formulas for conditional formatting based on another cell value

COUNTIF in Excel - count if not blank, greater than, duplicate or unique

Using IF function: formulas for numbers, text, dates, blank cells How to use Excel SUMIFS and SUMIF with multiple criteria Change background color based on cell value

INDEX & MATCH functions in Excel - a better alternative to VLOOKUP

Outlook: featured articles

How to sync Google Calendar with Outlook

How to fix "Cannot start Microsoft Outlook" error Merge duplicate contacts in Outlook

Remove duplicate emails in Outlook 2010 and 2013 quickly and safely

How to create and use e-mail templates in Outlook

Excel

Excel add-ins

Excel charts

Excel consolidation

Excel duplicates

Excel formatting

Excel fun

Excel functions

Jul

2

COUNTIF in Excel - count if not blank, greater than, duplicate or unique

by Svetlana Cheusheva Excel, Excel duplicates, Excel functions, Excel tips 223 Comments

Microsoft Excel provides several functions purposed for counting different kinds of cells, such as blanks or non-blanks, with number, date or text values, containing specific words or character, etc.

In this article, we will focus on the Excel COUNTIF function that is purposed for counting cells with the condition you specify. First, we will briefly cover the syntax and general usage, and then I provide a number of examples and warn about possible quirks when using this function with multiple criteria and specific types of cells.

In essence, COUNTIF formulas are identical in all Excel versions, so you can use the examples from this tutorial in Excel 2013, 2010, 2007 and 2003.

Excel COUNTIF function - syntax and usage Examples of how to use COUNTIF in Excel

COUNTIF formula for text and numbers (exact match) COUNTIF with wildcard characters (partial match) Count if blank or not blank

Count if greater than, less than or equal to COUNTIF formulas for dates

Excel COUNTIF with multiple criteria Count duplicates and unique values

Excel COUNTIF - frequently asked questions and issues

COUNTIF function in Excel - syntax and usage

Excel COUNTIF function is used for counting cells within a specified range that meet a certain criterion, or condition.

For example, you can write a COUNTIF formula to find out how many cells in your worksheet contain a number greater than or less than the number you specify. Another typical use of COUNTIF in Excel is for counting cells with a specific word or starting with a particular letter(s). The syntax of the COUNTIF function is very simple:

COUNTIF(range, criteria)

As you see, there are only 2 arguments, both of which are required:

range - defines one or several cells to count. You put the range in a formula like you usually do in Excel, e.g. A1:A20.

criteria - defines the condition that tells the function which cells to count. It can be a number,

text string, cell reference or expression. For instance, you can use the criteria like these: "10",

A2, ">=10", "some text".

And here is the simplest example of Excel COUNTIF function. What you see in the image below is the list of the best tennis players for the last 14 years. The formula

=COUNTIF(C2:C15,"Roger Federer") counts how many times Roger Federer's name is on the list:

(2)

Excel PivotTables Excel randoms

Excel tips

Excel Vlookup

Merge data in Excel

Microsoft Office Microsoft Word

new release

Office 365 tips

Office fun

Office news

Office tips

Outlook

Outlook add-ins Outlook BCC Outlook calendar Outlook duplicates Outlook templates Outlook tips PowerPoint Video

Sign in

Register Log in Note. A criterion is case insensitive, meaning that if you type "roger federer" as the criteria

in the above formula, this will produce the same result.

Excel COUNTIF function examples

As you have just seen, the syntax of the COUNTIF function is very simple. However, it allows for many possible variations of the criteria, including wildcard characters, the values of other cells, and even other Excel functions. This diversity makes the COUNTIF function really powerful and fit for many tasks, as you will see in the examples that follow.

COUNTIF formula for text and numbers (exact match)

In fact, we discussed the COUNTIF function that counts text values matching a specified criterion exactly a moment ago. Let me remind you that formula for cells containing an exact string of text: =COUNTIF(C2:C15,"Roger Federer"). So, you enter:

A range as the first parameter; A comma as the delimiter;

A word or several words enclosed in quotes as the criteria.

Instead of typing text, you can use a reference to any cell containing that word or words and get absolutely the same results, e.g. =COUNTIF(C1:C9,C7).

Similar COUNTIF formulas work for numbers as well as for text values. As you can see in the screenshot below, the formula =COUNTIF(D2:D9,5) perfectly counts cells with quantity 5 in Column D.

COUNTIF formulas with wildcard characters (partial match)

In case your Excel data include several variations of the keyword(s) you want to count, then you can use a wildcard character to count all the cells containing a certain word, phrase or letters as part of the cell's contents.

(3)

Suppose, you have a list of tasks assigned to different persons, and you want to know the number of tasks assigned to Danny Brown. Because Danny's name is written in several different ways, we enter "*Brown*" as the search criteria =COUNTIF(D2:D10, "*Brown*").

An asterisk (*) is used to find cells with any sequence of leading and trailing characters, as illustrated in the above example. If you need to match any single character, enter a question mark (?) instead, as demonstrated below.

Count cells beginning or ending with certain characters

You can use either wildcard character, asterisk (*) or question mark (?), with the criterion depending on which exactly result you want to achieve.

If you want to know the number of cells that start or end with certain text no matter how many other characters a cell contains, use these formulas:

=COUNTIF(C2:C10,"Mr*") - count cells that begin with "Mr". =COUNTIF(C2:C10,"*ed") - count cells that end with the letters "ed". The image below demonstrates the second formula in action:

If you are looking for a count of cells that start or end with certain letters and contain the exact number of characters, you use the Excel COUNTIF function with the question mark character (?) in the criteria:

=COUNTIF(D2:D9,"??own") - counts the number of cells ending with the letters "own" and having exactly 5 characters in cells D2 through D9, including spaces.

=COUNTIF(D2:D9,"Mr??????") - counts the number of cells starting with the letters "Mr" and having exactly 8 characters in cells D2 through D9, including spaces.

Tip. To find the number of cells containing an actual question mark or asterisk, type a tilde (~) before the ? or * character in the formula. For example, =COUNTIF(D2:D9,"*~?*") will count all cells containing the question mark in the range D2:D9.

Excel COUNTIF for blank and non-blank cells

These formula examples demonstrate how you can use the COUNTIF function in Excel to count the number of empty or non-empty cells in a specified range.

(4)

COUNTIF not blank

In some of other Excel COUNTIF tutorials, you may come across formulas for counting non-blank cells in Excel similar to this one:

=COUNTIF(range,"*")

But the fact is, the above formula counts all cells that contain any text, meaning that cells with dates and numbers will be treated as blank cells!

If you need a universal COUNTIF formula for counting all non-blank cells in a specified range, here you go:

=COUNTIF(range,"<>"&"")

This formula works correctly with all value types - text, dates and numbers - as you can see in the screenshot below.

COUNTIF blank

If you want the opposite, i.e. count blank cells in a certain range, you should adhere to the same approach - use a formula with a wildcard character for text values or another one (with the "" criteria) to count all empty cells.

Formula to count cells not containing any text: =COUNTIF(range,"<>"&"*")

Translated into plain English, the criteria used in the above formula ("<>"&"*") means to find cells not equal to *, i.e. not containing any text in the specified range.

Universal COUNTIF formula for blanks (all value types): =COUNTIF(range,"")

The above formula correctly handles numbers, dates and text values. For example, the formula =COUNTIF(C2:C11,"") returns the number of all empty cells in the range C2:C11.

Note. Please be aware that Microsoft Excel provides another function for counting blank cells =COUNTBLANK(range). For instance, the below formulas will produce exactly the same results as the COUNTIF formulas you see in the screenshot above:

Count blanks: =COUNTBLANK(C2:C11)

Count non-blanks: =ROWS(C2:C11)*COLUMNS(C2:C11)-COUNTBLANK(C2:C11)

Also, please keep in mind that both =COUNTIF(range,"") and =COUNTBLANK(range) count cells with formulas that only look empty. If you do not want to treat such cells as blanks, use this formula instead: =ROWS(C2:С11)*COLUMNS(C2:С11)-COUNTIF(C2:С11,"<>"&""). For more information about counting blanks and not blanks in Excel, see the following tutorials: 3 ways to count empty cells in Excel and How to count non-empty cells in Excel.

COUNTIF greater than, less than or equal to

To count cells with values greater than, less than or equal to the number you specify, you simply add a corresponding operator to the criteria, as shown in the table below.

Please pay attention that in COUNTIF formulas, an operator with a number are always enclosed in quotes.

(5)

Criteria Formula Example Description

Count if greater than =COUNTIF(A2:A10,">5") Count cells where value is greater than 5.

Count if less than =COUNTIF(A2:A10,"<5") Count cells with values less than 5. Count if equal to =COUNTIF(A2:A10,"=5") Count cells where value is equal to 5. Count if not equal to =COUNTIF(A2:A10,"

<>5")

Count cells where value is not equal to 5.

Count if greater than or equal to

=COUNTIF(C2:C8,">=5") Count cells where value is greater than or equal to 5.

Count if less than or equal to

=COUNTIF(C2:C8,"<=5") Count cells where value is less than or equal to 5.

You can also use all of the above formulas to count cells based on another cell value, you will just need to replace the number in the criteria with a cell reference.

Note. In case of a cell reference, you have to enclose the operator in quotes and add an ampersand (&) before the cell reference. For example, to count cells in the range D2:D9 with values greater than a value in cell D3, you use this formula =COUNTIF(D2:D9,">"&D3):

If you want to count cells that contain an actual operator as part of the cell's contents, i.e. the characters ">", "<" or "=", then use a wildcard character with the operator in the criteria. Such criteria will be treated as a text string rather than a numeric expression. For example, the formula =COUNTIF(D2:D9,"*>5*") will count all cells in the range D2:D9 with contents like this "Delivery >5 days" or ">5 available".

Using Excel COUNTIF function with dates

If you want to count cells with dates that are greater than, less than or equal to the date you specify or date in another cell, you proceed in the already familiar way using formulas similar to the ones we discussed a moment ago. All of the above formulas work for dates as well as for numbers. Let me give you just a few examples:

Criteria Formula Example Description

Count dates equal to the specified date.

=COUNTIF(B2:B10,"6/1/2014") Counts the number of cells in the range B2:B10 with the date 1-Jun-2014. Count dates greater than or

equal to another date.

=COUNTIF(B2:B10,">=6/1/2014") Count the number of cells in the range B2:B10 with a date greater than or equal to 6/1/2014.

Count dates greater than or equal to a date in another cell, minus x days.

=COUNTIF(B2:B10,">="&B2-"7") Count the number of cells in the range B2:B10 with a date greater than or equal to the date in B2 minus 7 days.

(6)

specific Excel Date and Time functions such as TODAY() to count cells based on the current date.

Criteria Formula Example

Count dates equal to the current date. =COUNTIF(A2:A10,TODAY()) Count dates prior to the current date, i.e.

less than today.

=COUNTIF(A2:A10,"<"&TODAY()) Count dates after the current date, i.e.

greater than today.

=COUNTIF(A2:A10,">"&TODAY()) Count dates that are due in a week. =COUNTIF(A2:A10,"="&TODAY()+7) Count dates in a specific date range. =COUNTIF(B2:B10,

">=6/7/2014")-COUNTIF(B2:B10, ">6/1/2014")

Here is an example of using such formulas on real data (at the moment of writing today was 25-Jun-2014):

Excel COUNTIF with multiple criteria

In fact, Excel COUNTIF function is not exactly purposed for counting cells based on multiple criteria. There is another function, COUNTIFS, especially designed for this (it is the topic of the next week's article). However, you can use COUNTIF to count values based on two or more criteria by adding or subtracting several COUNTIF functions.

Example 1. COUNTIF with two conditions for numbers

One of the most common applications of Excel COUNTIF function with 2 criteria is counting numbers within a specific range, i.e. less than X but greater than Y. For example, you can use the following formula to count cells in the range B2:B9 where a value is greater than 5 and less than 15.

(7)

Example 2. COUNTIF with multiple conditions for text values

Let's consider one more example of the COUNTIF function that will count 2 different text values. Suppose you have a shopping list and you want to find out how many soft drinks are included. So, the following formula works a treat:

=COUNTIF(B2:B13,"Lemonade")+COUNTIF(B2:B13,"*juice")

Please pay attention to the wildcard character (*) in the second criterion, it is used to count all kinds of juice on the list.

In the same manner, you can write a COUNTIF formula with several conditions. Here is an example of the COUNTIF function with multiple conditions that counts lemonade, juice and ice cream:

=COUNTIF(B2:B13,"Lemonade") + COUNTIF(B2:B13,"*juice") + COUNTIF(B2:B13,"Ice cream")

You can find plenty more examples for text, numbers and dates in this tutorial - Excel COUNTIFS and COUNTIF with multiple criteria.

Using COUNTIF function to find duplicates and unique

values

Another possible usage of the COUNTIF function in Excel is for finding duplicates in one column, between two columns, or in a row.

Example 1. Find and count duplicates in 1 column

For example, this simple formula =COUNTIF(B2:B10,B2)>1 will spot all duplicate entries in the range B2:B10 while another function =COUNTIF(B2:B10,TRUE) will tell you how many dupes are there:

(8)

If you have two separate lists, say lists of names in columns B and C, and you want to know how many names appear in both columns, you can use Excel COUNTIF in combination with the SUMPRODUCT function to count duplicates:

=SUMPRODUCT((COUNTIF(B2:B1000,C2:C1000)>0)*(C2:C1000<>""))

We can even take a step further and count how many unique names there are in Column C, i.e. names that do NOT appear in Column B:

=SUMPRODUCT((COUNTIF(B2:B1000,C2:C1000)=0)*(C2:C1000<>""))

Tip. If you want to highlight duplicate cells or entire rows containing duplicate entries, you can create conditional formatting rules based on the COUNTIF formulas, as demonstrated in this tutorial - Excel conditional formatting formulas to highlight duplicates.

Example 3. Count duplicates and unique values in a row

If you want to count duplicates or unique values in a certain row rather than a column, use one of the below formulas. These formulas might be helpful, say, to analyze the lottery draw history.

Count duplicates in a row:

=SUMPRODUCT((COUNTIF(A2:I2,A2:I2)>1)*(A2:I2<>"")) Count unique values in a row:

=SUMPRODUCT((COUNTIF(A2:I2,A2:I2)=1)*(A2:I2<>""))

Excel COUNTIF - frequently asked questions and

issues

I hope these examples have helped you to get a feel for the Excel COUNTIF function. If you've tried any of the above formulas on your data and were not able to get them to work or are having a problem with the formula you created, please look through the following 5 most common issues. There is a good chance that you will find the answer or a helpful tip there.

1. COUNTIF on a non-contiguous range of cells

Question: How can I use COUNTIF in Excel on a non-contiguous range or a selection of cells? Answer: Excel COUNTIF does not work on non-adjacent ranges, nor does its syntax allow

specifying several individual cells as the first parameter. Instead, you can use a combination of several COUNTIF functions:

(9)

Wrong: =COUNTIF(A2,B3,C4,">0")

Right: =COUNTIF(A2,">0") + COUNTIF(B3,">0") + COUNTIF(C4,">0")

An alternative way is using the INDIRECT function to create an array of ranges. For example, both of the below formulas produce the same result you see in the screenshot:

=SUM(COUNTIF(INDIRECT({"B2:B8","D2:C8"}),"=0")) =COUNTIF($B2:$B8,0) + COUNTIF($C2:$C8,0)

2. Ampersand and quotes in COUNTIF formulas

Question: When do I need to use an ampersand in a COUNTIF formula?

Answer: This is probably the most arcane part. I personally find the syntax of the COUNTIF

function very confusing, though if you give it some thought, you'll see the reasoning behind it. An ampersand and quotes are needed to construct a text string for the argument. So, you can adhere to these rules:

If you use a number or a cell reference in the exact match criteria, you need neither ampersand nor quotes, e.g. or =COUNTIF(A1:A10,10) or =COUNTIF(A1:A10,C1). If your criteria includes text, wildcard character or some operator with a number, you enclose it in quotes, e.g. =COUNTIF(A2:A10,"lemons") or =COUNTIF(A2:A10,"*") or =COUNTIF(A2:A10,">5")

If you use an expression with a cell reference or another Excel function, you have to use the quotes ("") to start a text string and ampersand (&) to concatenate and finish the string off. For example, =COUNTIF(A2:A10,">"&D2) or =COUNTIF(A2:A10,"<="&TODAY()).

If you are in doubt whether an ampersand is needed or not, try out both ways. In most cases an ampersand works just fine, e.g. the formulas =COUNTIF(C2:C8,"<=5") and =COUNTIF(C2:C8," <="&5) work equally well.

3. COUNTIF for formatted (color coded) cells

Question: How do I count cells by fill or font color rather than by values?

Answer: Regrettably, the syntax of the Excel COUNTIF function does not allow using formats as the condition. The only possible way to count or sum cells based on their color is using a macro, or more precisely an Excel User-Defined function. You can find the code working for cells colored manually as well as for conditionally formatted cells in this article - How to count, sum and filter cells by color in Excel.

4. #NAME? error in the COUNTIF formula

Issue: My COUNTIF formula throws a #NAME? error. How can I get it fixed?

Answer: Most likely, you have supplied an incorrect range to the formula. Please check out point 1 above.

5. Excel COUNTIF formula not working

Issue: My COUNTIF formula is not working! What have I done wrong?

Answer: If you have written a formula which is seemingly correct but it does not work or

produces a wrong result, start by checking the most obvious things such as a range, conditions, cell references, use of ampersand and quotes.

(10)

Please follow us on Google+:

Want to find more inventive tips, tricks and tutorials?

Follow 1.5k

223 Responses to "COUNTIF in Excel - count if not blank,

greater than, duplicate or unique"

Be very careful with using spaces in a COUNTIF formula. When creating one of the formulas for this article I was on the verge of pulling my hair out because the correct formula (I knew with certainty it was right!) wouldn't work. As it turned out, the problem was in a measly space somewhere in between, argh... For instance, look at this formula =COUNTIF(B2:B13," Lemonade"). At first sight, there is nothing wrong about it, except for an extra space after the opening quotation mark. Microsoft Excel will swallow the formula just fine without an error message, warning or any other indication, assuming you really want to count cells containing the word 'Lemonade' and a leading space.

If you use the COUNTIF function with multiple criteria, split the formula into several pieces and verify each function individually.

And this is all for today. In the next article, we will explore several ways to count cells in Excel with multiple conditions. Hope to see you next week and thanks for reading!

Karo says:

July 22, 2014 at 4:44 pm

I want to group or count values that start the same. In this short example YTOP.125 should be grouped together - or counted. I can work with either. So I want to be able to say "if the first, for example 8 characters are the same then...

For example ColumnA YTOP.125SP HYEP.556M YTOP.648 MNTK.593 HYEP.257 YTOP.125LO

Can you help? Is this possible? Reply

Svetlana Cheusheva says:

July 23, 2014 at 12:26 pm

Hi Karo,

Yes, it is possible, but you will need to add a helper column to your table. If this is acceptable, then create an additional column and copy the following formula across that column, which will extract the first 8 characters =LEFT(A2,8). After that, you can proceed in 2 ways.

Way 1 – using Subtotal:

- Sort your table by Column A 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 the first 8 characters.

For more info about using subtotals please see this article - Using Subtotals in Microsoft Excel

Way 2 – using a pivot table:

- 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 column A to the 1

(11)

Values section. Reply

Richard says:

July 24, 2014 at 12:12 pm

Hello,

I want to formulate the following:

IF "cell value A" greater than(>) "cell value B", THEN "cell value A" = "cell value B" and therefore the result "cell value C" is equal to zero

if this is doable can it be applied to multiple "cells"? Reply

July 25, 2014 at 8:07 am

Hi Richard,

I understand your condition but cannot figure out what result you want to achieve : ) If you want a formula to add a zero to column C if a value in cell A in the same row is greater than in cell B, you can use the following IF formula: =IF($A2>$B2,0,"") where 2 is your first row with data. Since we use a relative row reference (without the $ sign) the formula with compare values in each individual row when copied across multiple cell. If you are looking for some other result, please clarify.

Reply 2

Kelly-Ann says:

July 31, 2014 at 4:32 pm

Hi There,

I am trying to record attendance at my office and I am putting together an excel spreadsheet to store the data. I need to be able to sum up the totals of vacation days, personal days, business days, and sick days. The countif works well if I put 'V' for vacation, 'B' for business and so forth, but how can I calculate half days? Is there a way that I can set up a second Countif function so when I put in something like 'VH' in the range it counts it as 0.5 rather than one? Pretty much I am wondering if I can change the amount that the countif function counts by.

Thanks Reply

August 1, 2014 at 7:14 am

Hi Kelly-Ann,

I think you can just multiply the result of COUNTIF for "VH" by 0.5 to count half days, e.g.: =COUNTIF(RANGE, "V") + 0.5*COUNTIF(RANGE, "VH") Reply 3 Clare says: August 4, 2014 at 10:05 am Hi,

I have a project list where activity is shown in a timeline. I need to count up if a column 'project stage' shows activity in any given month. I have set the countif for picking up activity but now need to make it so that it only counts that activity if column 'project stage' is a particular word.

Any idea?

BTW the above is great! Reply

August 4, 2014 at 1:01 pm

Hi Clare, 4

(12)

You need to use the COUNTIFS function that allows setting multiple criteria in a formula. Something similar to this:

=COUNTIFS(C2:C9, ">=6/1/2014", C2:C9, "<=6/30/2014", D2:D9, "SpecialWord") Where C is the column with dates and D is the "Project Stage" column. Please see the following article for more info:

http://www.ablebits.com/office-addins-blog/2014/07/10/excel-countifs-multiple-criteria/ Reply

Ruth says:

Hi,

I need to combine an IF statement with a COUNTIF that will count 3 out of 5 text entry options for a single column. If the COUNTIF =0, it needs to show as a dash. This is the original formula that is working

=IF(COUNTIF(B1:B10000,C1&D1)=0,"-",COUNTIF(B1:B10000,C1&D1))

I need to add another criteria to the COUNTIFs, where there are 5 possible entries in column E and I need it to count 3 of these (Allocated, Declined, Rejected). Any suggestions is gratefully appreciated.

Ruth Reply

Hello Ruth,

Let me check if I understand the task correctly. Do you want to count values in column B only if column E has any of these values: Allocated, Declined, Rejected? Or, are you looking for something different? It will be very helpful if you can paste the values from a couple of rows in columns B-E and the result you expect a formula to return.

Reply 5

Dean BROWN says:

August 11, 2014 at 9:37 pm

Hi There,

I am wanting to extract some values and I thought COUNTIF might be able to do it. I have a cell that contains some data eg: ABC-BBC-ABC-SII-BBC-ABC

I get COUNTIF to count how many times BBC appears in that cell, seems it only counts the FIRST instance of BBC.

How do I get it check the entire contents of the cell and correctly record the result eg: BBC appears twice in that cell

Thanks! Reply

August 12, 2014 at 8:49 am

The COUNTIF function cannot count specific text or characters inside a cell, it deals with the entire contents of a cell, as if you selected the option "Match entire cell contents" when performing search in Excel.

Please use this formula instead:

=(LEN(A2)-LEN(SUBSTITUTE(A2,"BBC","")))/LEN("BBC")

Where A2 is the cell containing "ABC-BBC-ABC-SII-BBC-ABC", and "BBC" is the search text. Instead of "BBC", you can enter a cell reference with the search text, e.g. B1, sometimes it is a more convenient way to work with formulas.

Reply 6

Robert says:

August 12, 2014 at 12:41 pm

I'm after something similar to what you were asking Ruth please.

I've set up the formula to count up all the instances of "*banananas*" in column B. I then have a list of places in column A. So say the total amount of banananas is 300 I want 7

(13)

to know how many banananas I have for each of the individual places. So it only counts the banananas entries that are next to specific text in column A.

Thanks. Reply

August 13, 2014 at 12:51 pm

Hi Robert,

In this case, you'd better use the COUNTIFS function rather than COUNTIF. It allows you to apply several criteria to different cell ranges. You can find more information about the COUNTIFS function in this article:

http://www.ablebits.com/office-addins-blog/2014/07/10/excel-countifs-multiple-criteria/ As for your task, you can use the following formula:

=COUNTIFS(B:B,"*bananas*",A:A,"PlaceName") Reply

lara says:

August 13, 2014 at 8:03 am

Hi

Im also after something similar to Robert.

In column A i have a number of different status ie. "On Programme", "Rejected", "Completed" etc. and in column B i have a number of programmes ie. "Cleaning", "Customer Service", etc and i want to could how many for example On programme cleaning there are?

Ive tried and other formulas similar to this but none seem to be counting what i want. =SUM((COUNTIF(A2:A32,"On Programme"))*OR((COUNTIF(B2:B32,"CLEANING")))) Can you help?

Reply

August 14, 2014 at 7:08 am

Hello Lara,

The same as Robert, you should also use the COUNTIFS function rather than COUNTIF, because COUNTIFS can count cells based on multiple criteria. The formula is as follows: =COUNTIFS($A2:$A32,"On programme",$B2:$B32,"CLEANING")

You can find the full details in this tutorial:

http://www.ablebits.com/office-addins-blog/2014/07/10/excel-countifs-multiple-criteria/ If you want to use the SUM function, you should enter the following array formula (remember to press Ctrl + Shift + Enter to complete it):

{=SUM(($A2:$A22="On Programme")*($B2:$B32="CLEANING"))} Reply

8

V Sorensen says:

August 18, 2014 at 2:18 pm

HELP! I'm trying to create a list of email addresses, based on wireless carrier. So if one column has their wireless number, next column has carrier, I then want to combine wireless number with the appropriate text email. Where these are the extensions I want to add: Verizon = @vtext.com AT&T = [email protected] Sprint = [email protected] Tmobile = [email protected] So if someone lists 5556667777 Verizon 6667778888 Sprint

How do I create a cell that says If A6=Verizon, then [email protected]?? It's a list of a few hundred, so I'd rather no do manually. Thanks so much for the assistance!

Reply 9

(14)

August 19, 2014 at 10:23 am

Hello Virginia,

To fulfill your task, you need to create a lookup table "CarrierName = Domain" in another worksheet. Then, you will be able to pull a domain by the Carrier name using the below formula below and concatenate it with the telephone number. Here is the formula:

=IF(AND(A2<>"", B2<>"", MATCH(B2,WirelessCarriersData[Name], 0)>0), A2 & VLOOKUP(B2,WirelessCarriersData,2,FALSE),"")

Where A2 is the first cell in the Phone column not including headers, B2 is the first cell in the Carrier column, and WirelessCarriersData is the name of the lookup table. We have created an example for you, feel free to download it using the link below: http://www.ablebits.com/_img-blog/_comments/[email protected] Reply

Dawn Weber says:

August 19, 2014 at 2:06 pm

I am trying to create an auto Vacation equation template. I have completed the template and it is working well except, I would like it to do something like this.

Example:

=COUNTIF(D3:J20, "BBB-V") Currently the template is counting one for each one it sees, but what I would like it to is to equate 1 with a (NUMBER) like "10" to allow for actual hours employee is being deducted from balance the employee has. So if it sees one I would like it to see it is 1=10 and display the number in the cell as 10... Have I made this clear? Reply

August 20, 2014 at 10:54 am

Hello Dawn,

I am not sure I fully understand your task. You can try one of the following solutions: Solution 1 – you can just multiply the result by a certain number, say 10, othe formula will be as follows:

=COUNTIF(D3:J20, "BBB-V")*10

If you want to pull the number from a certain cell, replace "10" with a cell reference. Solution 2 – if you want to show 10 for COUNTIF=1, and for the other cases you want to do something different (e.g. multiply by 9):

=IF(COUNTIF(D3:J20, "BBB-V") = 1, 10, COUNTIF(D3:J20, "BBB-V") * 9 )

If neither solution work for you, please send a sample workbook with your data at [email protected] and we will try to help.

Reply 10

Rod says:

August 20, 2014 at 9:43 pm

Hi Svetlana!, I hope you can help me...

I am making a sample-test exam where the correct answer will be hidden in column C and column B will be where the person who is taking the exam will place his/her answer. I need a formula to tell me how many correct answers were given. For example: -X1- = test takers' answer

-X2- = correct answer -X1- -X2-A B C C A D D D B B

I need a formula that will tell me that the test taker answered 3 questions correctly and then divide that value by the number of questions, in this case 5 questions. FYI: The answers will be side-by-side, however many blank rows will separate each answer (to 11

(15)

facilitate the question and multiple answers in column A). Thank you,

Rod Reply

August 22, 2014 at 6:42 am

Hi Rod!

You can use the following array formula. Change 30 to the number of your last row with answers, and remember to press Ctrl + Shift + Enter to complete the formula:

=SUM(NOT(ISBLANK(C2:C30))*(B2:B30=C2:C30))

Then you can divide the result by the number of questions.

BTW, please be aware that one can easily see the content of a hidden column. More details are in this blog post:

http://www.ablebits.com/office-addins-blog/2014/07/30/excel-unhide-columns/#disable-unhide-columns Reply Uldis says: April 7, 2015 at 1:23 pm Hi Svetlana!

I tried to use this sample You provided for Rod, but it doesn't seem to work, and I don't get what's wrong...

1) I entered some values in B and C columns

2) entered formula exactly as it is in Your sample and pressed Ctrl+Shift+Enter as a result I see that formula as a text, and no result... why is that so? What am I doing wrong?

Here is the screenshot how it looks: http://screencast.com/t/QRsaafuSvzw Hope, You can help!

P.S. This is a really nice and helpful blog post! Reply

Uldis says:

April 7, 2015 at 1:48 pm

OK, I gound what's wrong... i didn't know that brackets {} are added automatically and I don't have to enter them manually :)

Reply

April 8, 2015 at 11:53 am

Hi Uldis,

It's great that you sorted this out! Those brackets might be confusing indeed, I added them to show that is an array formula. But may be I'd better remove them not to confuse someone else :)

Reply

Samantha says:

August 21, 2014 at 5:42 pm

I tried using the countif(range,""&"") to count the cells within a range in my sheet that have something written in them and it keeps counting all of the cells selected. If I tried counting all the cells that are blank instead and write countif(range,""&"*") it brings me to 0.

Any idea of why it wont count my cells that have data? Reply

August 22, 2014 at 12:47 pm

(16)

Hi Samantha,

Please try using the formulas described in the Excel COUNTIF for blank and non-blank cells section. They should work.

Reply

Samantha says:

August 26, 2014 at 10:11 am

Hi Svetlana,

Unfortunately when using both functions to count non blank cells it still counts all the cells for me.

The way I input the function is : =COUNTIF(E21:E27,""&"") Is that a correct way?

I only have test1 in 1 of the cells written yet that function comes out as 7. Any ideas of what might be going on in the excel?

Reply

August 26, 2014 at 1:12 pm

Hi Samantha,

This is the correct formula to count non-blank cells. =COUNTIF(E21:E27,"<>"&"")

Does it work for you? Reply

Lance says:

August 26, 2014 at 6:09 pm

Trying to calculate the number of times certain words appear in a column. Some of the cells works others don't and I am using the exact same formulas for both. Even when I click in on the fx button the total is correct in the formula, but it won't replicate it to the cell. The cell just shows the formula an not the number. I have all the cells formatted to general, even the ones that are working correctly.

Reply

August 27, 2014 at 10:22 am

Hello Lance,

It's hard to pin down the cause of the problem without seeing your data. If you can send you sample workbook at [email protected], we'll try to figure it out.

Reply 13

Bilal says:

August 27, 2014 at 2:24 am

Is there any site where we can get online support for MS office (Excel) I mean through chat...

Reply

August 27, 2014 at 10:18 am

Hi Bilal,

I am sorry, I do not know any website that provides free online consultancy / support for Microsoft Office.

Reply 14

Gayle says:

August 29, 2014 at 1:19 pm

I need to count the number of names in a column, but exclude any that have been struck through.

Reply 15

(17)

Shams says:

September 4, 2014 at 11:09 am

Greetings

Making an attendance sheet Want 5 T=Tardies to couny as a 1

Ex. 28 periods a week one has 12 Tardies which means 2 absents so 112 periods a month in average so how or which formula can help me count 5 T=Tardies as 1

Thanks in advance Reply

16

Sam says:

September 4, 2014 at 3:42 pm

Not sure if its possible, but can you Count the Blank spaces in One Column (say Column A) if there is specific text in Column B that corresponds to the blank area. For example, I need to know how many have responded to my invitation (blanks)in column A, but have

informed me they are available (Word: Available)in column B. I have tried to combine a =CountBlank with a =COUNTIF but it doesn't seem to be working for me.

Thank you! Reply 17 Alex says: September 9, 2014 at 8:19 am Hi I want to be able:

If column A = certain text, then I want all the Values in Column B(same row) be summed together: For example: Profession Mark Nurse 5 Doctor 2 Careworker 2 Doctor 6 Doctor 7 Nurse 8 Nurse 5

Nurse Doctor Careworker (sum) (sum) (sum) Reply

18

Alex says:

Hi

Never mind, I figured it out. thank you Reply 19 Tommy says: September 10, 2014 at 10:16 am Hello,

I want to count the number of blank cells from a specific character.

The problem is that the range is extended with one cell, each day. Can you automatically extend that range each day?

Also, eventially the specific character will appear again in later cells, then I want it to start the count of blank cell from this "new" specific character.

Example:

The letter P is in cell B2 the range is B2:G2, hence 5 blank cells. Two days after I want the range to be B2:I2 i.e. 7 blank cells.

Another three days later the the letter P appears in cell K2 (range is now B2:L2), hence it should start the count from K2 i.e. 1 blank cell.

(18)

Thank you

Hope I didn't complicate the explaination?! =) Thank you

Reply

Heather says:

I am trying to identify and account for duplicates. I would like the COUNTIF formula to tell me if there is a duplicate anywhere else in that column. If a unique value it would result in a "1" and if a duplicate is found it will result in a "0." However, I would also like the 1st occurance of a value to result in a "1" and all other duplicate values to be a "0." How can I get that 1st occurance of a value to result in a "1" separate from its duplicates?

Reply Ange says: February 4, 2015 at 10:47 pm =IF((COUNTIF($A$1:$A1))=1,1,0) Reply 21 Pina L C says: September 16, 2014 at 12:08 pm

This is so useful Svetlana, and your answers to the questions here helped a lot today at work. Thanks!

Reply 22

Carol says:

Hi. i have 6 sheet tabs. The 5 sheet tabs are the summary report of 5 different branches and the other sheet tab is for my summary report. All column A of 5 sheet tabs refers to different sports while column B refers to number of votes for different sports. I need to get the top 3 sports for all branches.

For example,

In Branch 1, the top 3 sports listed are basketball, football and volleyball with 85, 74 and 65 votes. For branch 2, the top 3 listed are tennis, basketball and badminton with 65, 54 and 32 votes and so on. How will i get the top most sports from 5 different branches that will also add the votes if for example all branches have basketball in their list? Hope you can help me with this.

Thank you Reply 23

Al Imran Monju says:

Hi,

I have seen this. When i feel that i have need more than i will find out. But for this time it is very helpful to me. Thank you.

Reply 24

Al+Imran+Monju says:

Hi,

I want to know from you that how can I count the two cell data at a time like: ColA Col B 1 M 2 F 3 F 6 M 2 F 3 M

I want to know for 1 how many M of F and for 2 how many M or F Like ---25

(19)

Reply

Ashish says:

June 15, 2015 at 12:00 pm

If you have got answer pls. share with me. Even I also need solution. Reply

Karla says:

Can you please tell me what the formula is if I want when if a # is less than 35 then add $5.75 if it's greater than 35 add $12.75 please help thanks

Reply

Hi Karla,

I am not sure I can exactly follow you. Do you want to enter a number in some cell based on a value in another cell? For example, if a number in cell A1 is less than 35, than put 5.75 in cell B2, if it's greater than 35, than put 12.75? And what if it is equal to 35? Reply

26

Rick says:

Does the COUNTIF function only compare the first 15 characters? I use it to check for duplicate values. It flags 2 of my cells as being duplicates. The only difference is the 16th character.

Reply

Rick says:

I tested this theory of mine using the right 15 characters of a concatinated string. That fixed the problem.

Reply

Hello Rick,

Thank you for the information and the update.

To my best knowledge, the COUNTIF function has 255 characters limitation since Excel 2007. And one can bypass this limitation by using the Exact function in array

formulas.

The issue you describe may occur either in a very old version of Excel, or more likely, if your sheet contains only numeric data and Excel treats it as numbers. As you

probably know, Excel has a 15 digit limitation when it comes to numbers, and after the first 15 digits, it replaces everything with 0's. Please check out the following Microsoft answer for more details:

http://answers.microsoft.com/en-us/office/forum/office_2007-excel/15-digit-number-limitation-non-text-workaround/a4974853-7c3c-4830-8562-2e88369d981b

Reply 27

Johann Sebastian says:

I have a spreadsheet where I input my current weekly sales in Column C and last year's weekly sales is already typed in Column B; I want to make a REAL month-to-date weekly comparison at my weekly manager's meeting.

To do this I would need the weekly sales from last year in Column B to count ONLY when if I input the weekly sales figure in Column C; otherwise I need Column B "blank" or to count as "0".

Note: please keep in mind that last years weekly sales in Column B is taken from last year's monthly sales in Column A divided by 4 (accounting for four weeks in a month) 28

(20)

whereby the value of cell B1 is "=A1/4"

So, I need Column B to display last year's weekly sales ONLY when I input last week's sales in Column C.

Can someone please help me? Thank You,

Johann Sebastian Reply

Hello Johann,

Enter the below formula in cell B1: =If($C1<>"",$A1/4,"")

And then copy/paste it to the whole column. Reply

Dushyant Dodiya says:

I have one problem in my work sheet. Pls any one help me.

I have total no. of data. Like Year Total 1 2 3 4 5 of Cell 1993 5 136 591 888 960 1006 1994 3 107 177 347 1995 5 408 1238 1352 1521 1581 3 2215 2414 2444 1996 5 112 1093 1144 1298 1351 1997 5 122 238 388 458 485 4 1104 1105 1432 1479 1998 5 77 88 464 467 574 5 1165 1182 1190 1374 1430 1999 5 421 480 531 844 900 4 1913 1935 2005 2019 2000 5 16 36 79 95 97 5 572 574 655 656 674 5 1209 1275 1280 1481 1507 3 1834 1851 1875

i want the when ever year combined that time total of cell sum. Reply

Hello Dushyant,

I am sorry, your data posted in the comment got distorted. For us to be able to assist you better, please send your workbook to [email protected] and include the result you expect to get. We'll try to help.

Reply 29

Vamsi says:

October 5, 2014 at 6:09 pm

HELLO,

Please help me in this problem

Formula: =IFERROR(LOOKUP(C2,Sheet1!$A$30:$A$39,Sheet1!$B$30:$B$39), "")

Required condition: If C2 cell in not equal to A2 " additional conditon to be added in above formula with the existing.

Reply 30

(21)

Hello Vamsi,

Please specify what exactly additional condition you want to add, and in what part of the formula you want to add it.

Reply

Vamsi says:

Hello,

DESCRIPTION OF FORMULA MADE IN EXCEL WORKSHEET. C2 WILL BE ENTERED BY USER.

In D2 the entered value of user will be searched (LOOKUP)in other worksheet with in specified range of column (Sheet1!$A$30:$A$39) and output will be the specified range in other column (Sheet1!$B$30:$B$39).

If there no entry in the cell (C2) by user the cell (D2) will remain Blank. Here my requirement:

In addition to above condition the value entered in C2 should not be equal to Value of A2, if equal then it should be an error message (N/A).

PARAMETERS OF THE CELLS in worksheet 2: A2 DUTY CODE NORMAL

C2 DUTY CODE OVERTIME D2 TIMING OF DUTY

Sheet1!$A$30:$A$39 - Worksheet 1 A30 to A39 are duty codes Sheet1!$B$30:$B$39 - Worksheet 1 Duty timing

---END---Reply

Hello Vamsi,

If you can send us your sample workbook at [email protected] and we'll try to find a solution. Reply 31 Rachana says: October 8, 2014 at 2:20 pm Hi Svetlana,

What formula can i use if i want a value "N" in 1st coulmn based on blank cells that appear in 2nd column and want value "Y" in 1st column based on non-blank cells that appear in 2nd column. Please advise.

Y/N Names N

Y abc Thanks Reply

October 10, 2014 at 12:09 pm

Hello Rachana,

You can use the following formula for your 1st column: =if(B2<>"", "Y","N)

Where B is your second column. Reply

Rachana says:

October 15, 2014 at 11:15 am

Thank you Swetlana!!! 32

(22)

One more help i would need on a formula.

I have one column "A" with names e.g. Mike, Tony etc goes till A12, and the names are repeated.

other column "B" having some numbers with % sign e.g. 90%, 80% etc.. goes till B12 Now i want to count non blanks ones in B1:B12 for Mike in B1:B12

I tried as below , but it didnt work. Please can you advise which forula fits better.. =countifs('Block Inspections'!B1:B12,'Block Inspections'!A12:A150,"=Mike") Thanks, Rachana Reply Amber says: October 8, 2014 at 4:11 pm Hi Svetlana,

I have three columns that are populated via a pivot table (in tabular format). They are site (a), manager (b) and rep (c). Only one cell populates per row across the three columns. I'm trying to build 1 formula that can be applied to all rows in one column (d), that counts the # of items entered in a given month for either site, manager or rep...depending on which column (a-c) is populated in that row.

I recently built the below formula that looks at the rep column first. If it is populated, it counts the # of times that reps name shows up in column b on the data tab. If it is blank, then it knows it needs to refer to column b for the manager name and then count the # times that managers name shows up on the data tab in column f.

=IF(C6="",COUNTIF(DATA!$F$3:$F$3000,B6),COUNTIF(DATA!$B$3:$B$3000,C6))

I need help adding an additional (nested if or and) function that says if both column b and c are blank, refer to column a and then search the # of times that site shows up in column d of the data tab. Let me know if I am not explaining this well enough. I appreciate your help in advance!

Thanks! Reply

October 10, 2014 at 12:06 pm

Hi Amber,

I think we will be able to help you better if we can have a look at your data. I possible, please send your sample workbook at [email protected].

Reply 33

Vamsi says:

Hellow Svetlana Cheusheva, Forwarded the workbook file by mail for your guidance and support. Hope you can help me.

Thanks Reply 34

COUTNING says:

October 11, 2014 at 11:26 am

I need a formula that says:

if column I = cell A1 count it, but if column I is blank then go to column g only count it if column g = cell A1

Reply 35

Satheesh says:

October 14, 2014 at 4:10 am

If a sheet arranged as bellow Col A col B col C

Name rank age 36

(23)

Arun dy co 25 Philips si 30 Joseph dy co 27 Vinod dy co 29 stphen si 26

Then how can count how many dy co become between the age 25 to 30 by using excel formula.

Please help Reply

October 14, 2014 at 11:21 am

Hello Satheesh,

You can do this using the following COUNTIFS formula, that returns the count of "dy co" between the age 25 to 30, inclusive:

=COUNTIFS(B:B,"=dy co",C:C,">=25",C:C,">=30")

The below one returns the count of "dy co" between the age 25 to 30, not including 25 and 30:

=COUNTIFS(B:B,"=dy co",C:C,">25",C:C,">30")

For more info about using the COUNTIFS function with multiple criteria, please see this tutorial:

https://www.ablebits.com/office-addins-blog/2014/07/10/excel-countifs-multiple-criteria/ Reply

Rachana says:

October 15, 2014 at 11:17 am

One more help i would need on a formula.

I have one column "A" with names e.g. Mike, Tony etc goes till A12, and the names are repeated.

other column "B" having some numbers with % sign e.g. 90%, 80% etc.. goes till B12 Now i want to count non blanks ones in B1:B12 for Mike in B1:B12

I tried as below , but it didnt work. Please can you advise which formula fits better.. =countifs('Block Inspections'!B1:B12,'Block Inspections'!A12:A150,"=Mike")

Thanks, Rachana Reply 37 Rachana says: October 15, 2014 at 11:18 am Hi Swetlana,

Just to add on above query, i am counting them on another master sheet. Thanks, Rachana R Reply 38 Rachana says: October 15, 2014 at 11:35 am Hey Swetlana,

I just tried as below as it worked :)

=COUNTIFS('Block Inspections'!$A$12:$A$150,"Mike",'Block Inspections'!$D$12:$D$150,"") Thanks, Rachana Reply 39 Rachana says: October 15, 2014 at 11:36 am

Oops i tried as below with the non blank ones and it worked. :) 40

(24)

=COUNTIFS('Block Inspections'!$A$12:$A$150,"Mike",'Block Inspections'!$D$12:$D$150,"") Reply

Jeremy says:

Hi i am trying to come up with a formula that I can find the duplicate order ID in one column and have it subtract the dates in another column so I can figure out how long it took to ship.

Reply

October 28, 2014 at 11:20 am

Hello Jeremy,

I am sorry, your task is not very clear. If you can send your sample to our support team at [email protected], we'll try to help.

Reply 41

Rachana says:

Hey Swetlana,

Please can you advise on below 2 quesries.

1) I have A column that shows a start date e.g. 10-1-2014 and the task needs to be completed by 6 weeks. So i want the B column to auto calculate the date of completion that is 6 weeks from the start date. Please can you advise how can formulate this one. And like also want to calculate for other target dates i.e. after 26 weeks in another column and 52 weeks in another column.

Col A(start date) Col B(6 weeks target date) 10-1-2014 ? (How to auto populate date)

1) Now I have two columns as below, please can you advise.

Column B shows - target date to be completed and the column C is showing actual date for the task completed. Now i have column D where i want value Y or N based on date of completion is greater or lesser than the target date. So if completion date is greater than target date than it should show N on Column D and similarly Y where the date is equal or lesser than the target date. Please can you advise how can set a formula for this one. Col A Col B Col C Col D

start date 6 weeks target date Actual date Y or N ?(auto populate) 01-09-2014 ? Auto populate 20/10/2014 N - Value to auto populate Thanks,

Rachana Reply

October 28, 2014 at 11:19 am

Hello Rachana,

Here's the formula for column B (cell B2): =A2 + 6*7 And this one is for column D: =IF(C2<=B2, "Y", "N") Reply

Rachana says:

Thank you Svetlana :) It worked

Now when i use =IF(C2<=B2, "Y", "N") and along with that i want to add one more criteria that if the completion date column is blank then the Y/N column should remain blank. Please can you advise.

Thanks, Rachana Reply 42

(25)

Rachana,

Assuming that your "completion date column" is column C, here is the formula: =IF(C2="","",IF(C2<=B2,"Y","N"))

Reply

Rachana says:

November 5, 2014 at 1:05 pm

Hey Svetlana,

Lets say an A column has Start date that is blank and i want to add a formula on C stating that if A1 is blank then C1 should be blank but A1 has a start date then it should calculate A1+6*7 for the date to auto populate on the 6 weeks date. Please can you advise.

From your previous reply i see we can use =IF(A1="","") for having blank cell but not sure how to further add the criteria as above. Please advise me.

Thanks, Rachana Reply

Rachana says:

Thank you Svetlana for formula =IF(C2="","",IF(C2<=B2,"Y","N")) It worked as desired. :)

I would need your help on below to get a formula.

I have a A column with start dates in the format e.g. A1= "01/01/2014", A2= 1/3/2014, A3= 1/2/2014. Now i want column B to auto enter the value in B1 = January, B2= March, B3= February..

Just to highligh that the Start date is not in order hence the months are not in order too. please advise which formula can be applied to have respective months value in B column based on Start date in A column.

Thanks, Rachana Reply

Rachana,

You can simply enter the formula =A1 in cell B1, and then copy it down to other cells of column B. This will populate column B with dates corresponding to column A. Then select column B, right click and click Format Cells > Number tab > Custom, and type mmmm in the "Type" box. mmmm is the date format that displays months only.

Reply Syed Bukhari says:

Hello Svetlana,

I'm looking to count the number of occurrences of text in column A while checking the value in column B to be >0.

Let's say if my Text repeated for 5 days a week having value higher than 0. I need 5 in front of that text.

Appreciate your help! Syed Reply 43 Ashish says: October 28, 2014 at 4:41 pm 44

(26)

Hi, Is there any formula to capture data from other cell if a particular cell is blank, for example A1 and B1 are the cell,if A1 is blank then data should be capture from B1. Regards

Ashish Reply

October 29, 2014 at 12:33 pm

Hi Ashish,

Try this formula: =IF($A1="",$B1,"") Reply

Ashish says:

October 30, 2014 at 10:39 am

thanks Svetlana,it worked and saved my almost 10 mins from daily my work :). Reply

45

handri says:

please help me Svetlana. I have a problem about sequence of numbers that is a lot to be able to read just one in a column. eg: column A there are numbers

9,1,2,3,4,2,2,5,6,2,7,5,4,8,9,2,10, then I would like to read into 1,2,3,4,5,6,7 , 8,9,10. how the formula? thank you very much

Reply

Hello Handri,

I am sorry, it is not very clear what you want to get. If you can send the source data and the expected result to [email protected], our support team will try to help. Reply

46

Zack says:

I am trying to use the count unique values function as part of a COUNTIFS equation, I need to count the amount of 'P's in one column, that have a unique number in another column. I can't get the unique equation to work though. Please help.

=COUNTIFS(E$7:R$42,">0",F$7:S$42,"P",E$7:R$42,"=1") Where did I go wrong? Reply

Hello Zack,

You need a helper column that will show whether the number in another column is unique or not.

The formula can be as follows: =if(countif(D:D,D2)=1,1,0)

Then you can use a value from this helper column as one of the conditions for your COUNTIFS.

As I can see in your formula, several columns are used for each range. Is this correct? Reply

Zack says:

Yes that is correct, the way that we have the page formatted is largely for viewing ease.

Reply 47

(27)

Khalid says:

November 3, 2014 at 8:45 am

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 | Thanks in Advance, Khalid Reply 48 Rachana says: November 5, 2014 at 5:09 pm Hi Svetlana,

Lets say an A column has Start date that is blank and i want to add a formula on C stating that if A1 is blank then C1 should be blank but A1 has a start date then it should calculate A1+6*7 for the date to auto populate on the 6 weeks date. Please can you advise. From your previous reply i see we can use =IF(A1="","") for having blank cell but not sure how to further add the criteria as above. Please advise me.

Thanks in advance Rachana Reply 49 Rachana says: November 6, 2014 at 5:31 pm Hi Svetlana,

I am using formula =IF(K5>=$A$1, "True", "False") where A1 is 01/04/2014. Now I want to add additional criteria to it as below, please can you advise.

I have A1= 01/01/2014 and B1=31/03/2014

I want C1 to calculate if D1 cell has the date that is >= A1 and <=B1 then put value True otherwise false.

Thanks in advance Rachana

Reply

Rachana,

You don't need the COUNTIF function for this task. Just add one more IF statement to your formula:

(28)

=IF(D1>=$A$1, IF(D1<=$B$1, "True", "False"), "False") Reply

Anonymous says:

Thank you again Svetlana!!! Please help on another query.

I am using a formula - =COUNTIFS('6-26-52 Weeks'!$F$4:$F$52,"=Mike",'6-26-52 Weeks'!$I$4:$I$52,"=April",'6-26-52 Weeks'!$J$4:$J$52,"=True",'6-26-52

Weeks'!$Q$4:$Q$52,"=Y")

I needed the total count of Ys avilable in sheet 1 provided it matches the criteria that is - Col F to find the name e.g. Mike, Col I to check the month e.g. April, Col J to count True and then Count Y from Col Q.

For some reason it doesnt give me any formula error neither it counts Y in the mastter sheet. Regards, Rachana Reply Rachana says: November 26, 2014 at 10:22 pm

Thank you Svetlana! Thats quite helpful:) Reply

Farhana says:

Hi

I have Excel File with more than 1000 columns and rows and in some rows/columns, there are email addresses. I want to collect all emails only in one column. Can you please guide Reply

51

Lydia Noble says:

I have sheet 1 "South", contains a range of criteria.

In Column A lists codes containing N or M followed by a number ie N2, M4 etc. In Column C lists New Birth, 0-11 age, 11+.

The data above I want to both be calculated so how many New Births in N or M (any code with N "N*", are there. No mater what the number is)

with in a specific date range ie 1/4/14-30/6/14, which I know would equal QTR 1. The question I want to ask are:

How many New Births in N (Newtown) were there in QTR 1 (1/4/14-30/6/14)

I ideally this new data I would like to be linked to a new work book, but that calculates and updates when the data changes in work book 1.

Many thanks in advance Reply

52

Lydia Noble says:

I have work book 1 which collates a range of data.

Column A - codes N2, N17, M4, M2 etc(only N and M are used, although the following numbers vary)

Column C - New Birth, 0-4 years, 5+ years Column F - shows dates of data entry. My question to ask is:

How many N or M (column a) have Newbirths (Column c) between the dates 1/4/14 to 53

(29)

30/6/14 using the dates in column F

Then how many N,M 0-4 yrs in above dates etc

There will also be 3 further date ranges, 01/07/14 to 30/09/14, 01/10/14 to 31/12/14 and finally 01/01/15 to 31/3/15.

hope this makes sense

Ideally I would like to ask additional questions for calculations possibly in a new work book, but with all data linked so it would be constantly updated. I have briefly learnt how to link 2 workbooks together.

Reply

Lydia Noble says:

apologies I have sent the same question Reply

Hi Lydia,

Your second comment made the task clearer.

Since you have to sum by several conditions you will have to use the COUNTIFS function rather than COUNTIF. The formula can be as follows

=COUNTIFS(A:A, F2&"*", B:B, F3, C:C, ">="&F4, C:C, "<="&F5) Where:

F2 - the cell containing either N or M F3 - the cell with "New Birth" etc.

F4 - the sell with the "start date", e.g. 1/4/14 F5 - the cell with the "end date", e.g. 30/6/14

Naturally, you can put the above conditions directly into the formula, but in this case you will have to re-write it for each set of conditions.

You can also use the above formula in another worksheet, by adding the worksheet's name before the ranges, like this:

=COUNTIFS([Book1.xlsx]Sheet1!$A:$A,F2, [Book1.xlsx]Sheet1!B:B,F3, [Book1.xlsx]Sheet1!C:C,">="&F4, [Book1.xlsx]Sheet1!C:C,"<="&F5) Reply

Lydia+Noble says:

Thank you so much Svetlana, you are a a life saver, thank you so much for your time. I am trying the first part then I will venture on to the answers going in to another worksheet. But I am guessing or hoping IO can use the paste link to do this! Thanks again Reply 54 Steve says: November 10, 2014 at 8:00 pm Hi

there-I was wondering if there-I can use the COUNTthere-IF function with a very specific conditional test. there-I have a set of data by date where for one date in column A, there might be 4 corresponding numbers in column B. Example:

9/8/2013 43 9/8/2013 45 9/8/2013 36

Is there a way to use COUNTIF to only count the appearance of a number greater than x once per date? For the above, I would want it to only count once if the criteria were ">35". Thanks,

Steve Reply 55

(30)

Hi Steve,

Since you want to count with two conditions, you have to use COUNTIFS rather than COUNTIF: =COUNTIFS(A:A, "9/8/2013", B:B, ">35") Reply jai says: November 11, 2014 at 3:38 am Hi there

I want my excel spreadsheet to count letters in a row but with a unique condition behind each letter i.e A=1, B=2, C=3, D=4, E=5, F=6 G=7

so if the cell in row F has b and row G has E total will 7 is there a way to do this?

jai Reply 56

Matt says:

I have an excel spreadsheet that lists the different subjects that a student has undertaken. Example (cells N2:N9): Information Technology Physics Advanced Maths General Maths English Research English Critical English Academic Computer Applications

I would like to insert a formula underneath this (in cell N10), to look at the subjects the student has undertaken, and return a result based on this search. I need to identify if a student has undertaken all 3 English subjects listed. I have tried multiple formulas, but cannot seem to get the right outcome, as I am searching on a range of cells (N2:N9). Thanks

Matt Reply

Hello Matt,

Please try the following formula.

=IF(COUNTIF(N2:N9, "*engligh*")>=3, "Pass", "")

If it is not what you are looking for, please describe the result you expect to get in more detail.

Reply 57

Steven says:

I'm having an issue with compiling certain data.

I'm trying to find an overall percentage of participating users for certain questionnaires they have answered. For example, I have:

=(COUNTIF('Sheet 1'!$C4:$Z4, "*CHI*")+COUNTIF('Sheet 2'!$C4:$Z4, "*CHI*")+COUNTIF...and so on for 32 sheets.

While that's fine and all for those who participated in each questionnaire: 58

(31)

...+COUNTIF('Sheet 32'!$C4:$Z4, "*CHI*"))/(COUNTIF('Sheet 1'!$C$1:$Z$1, "*CHI*")+COUNTIF(...up to sheet 32.

$C$1:$Z$1 being my headline, line 4 being this particular individual's responses (where "CHI" might not exist in certain sheets)

The issue I am having is that there are many individuals who couldn't complete all the questionnaires (we're talking hundreds). I don't want to have to go sheet by sheet, line by line to find and specifically eliminate which sheets they didn't complete to get the proper percentage (they didn't do sheets 12, 14, 20-22, 25 for example, and I would have to delete those COUNTIF segments from the formula, just for that one cell so those extra 6 aren't mistakingly reducing the individual's percentage).

Is there an IF or COUNTIF formula that can count an entire row only if there is a timestamp (proof they filled the questionnaire) next to the individual's name?

Reply

Omar says:

I want to count how many names appear in range C5:C11 from a list of names that I have. That list I use in drop down menus in the range C5:C11. My names are Reid, Tyrone, Chris, and Paul. I can select those names from the drop down menu. I want to count how many names from the list are in that range. I tried countifs with not sucess

Reply

Hello Omar,

You can use one of the following formulas:

=countif($C$1:$C$5," Reid") or =countif($C$1:$C$5,F2) Where cell F2 contains the name Reid.

Reply 59

Meg says:

Hi - I'm trying to compare two worksheets to see if an email address on sheet 1 is in a range of cells in Column A in sheet 2. I've tried using vlookup and ifcount formulas but neither are working. I removed all formatting from both sheets and I'm using Excel 2013. Can you please suggest a formula or point me in the right direction? Thank you!! Reply

Hi Meg,

Please see this article:

https://www.ablebits.com/office-addins-blog/2013/09/05/compare-two-columns-remove-duplicates/

Alternatively, you can use the COUNTIF function. The formula will be as follows: =IF(COUNTIF(Sheet2!$A:$A,$A2)>0,"Duplicate","Unique")

Reply 60

Kimball High says:

Just wanted to say a big THANK YOU for this article. Buried in the article is a short paragraph about how to use the symbols as text in a countif(s) and it has really helped. Thank you for taking the time to publish this comprehensive explanation.

Reply 61

ALi says:

need to distinct sum ,distinct count,Distinct Average 62