by In our previous tutorial, we were looking at Excel If contains formulas that return some value to another column if a target cell contains a given value. Aside from that, what else can you do if a cell contains specific text or number? A variety of things such as counting or summing cells, highlighting, removing or copying entire rows, and more.
Excel 'Count if cell contains' formula examples
In Microsoft Excel, there are two functions to count cells based on their values, COUNTIF and COUNTIFS. These functions cover most, though not all, scenarios. The below examples will teach you how to choose an appropriate Count if cell contains formula for your particular task.
Count if cell contains any text
In situations when you want to count cells containing any text, use the asterisk wildcard character as the criteria in your COUNTIF formula:
Or, use the SUMPRODUCT function in combination with ISTEXT:
In the second formula, the ISTEXT function evaluates each cell in the specified range and returns an array of TRUE (text) and FALSE (not text) values; the double unary operator (--) coerces TRUE and FALSE into 1's and 0's; and SUMPRODUCT adds up the numbers.
As shown in the screenshot below, both formulas yield the same result:
=COUNTIF(A2:A10,"*")
=SUMPRODUCT(--(ISTEXT(A2:A10)))
You may also want to look at how to count non-empty cells in Excel.
Count if cell contains specific text
To count cells that contain specific text, use a simple COUNTIF formula like shown below, where range is the cells to check and text is the text string to search for or a reference to the cell containing the text string.
For example, to count cells in the range A2:A10 that contain the word "dress", use this formula:
=COUNTIF(A2:A10, "dress")
Or the one shown in the screenshot:
You can find more formulas examples here: How to count cells with text in Excel: any, specific, filtered cells.
Count if cell contains text (partial match)
To count cells that contain a certain substring, use the COUNTIF function with the asterisk wildcard character (*).
For example, to count how many cells in column A contain "dress" as part of their contents, use this formula:
=COUNTIF(A2:A10,"*dress*")
Or, type the desired text in some cell and concatenate that cell with the wildcard characters:
=COUNTIF(A2:A10,"*"&D1&"*")
For more information, please see: COUNTIF formulas with partial match.
Count if cell contains multiple substrings (AND logic)
To count cells with multiple conditions, use the COUNTIFS function. Excel COUNTIFS can handle up to 127 range/criteria pairs, and only cells that meet all of the specified conditions will be counted.
For example, to find out how many cells in column A contain "dress" AND "blue", use one of the following formulas:
=COUNTIFS(A2:A10,"*dress*", A2:A10,"*blue*")
Or
=COUNTIFS(A2:A10,"*"&D1&"*", A2:A10,"*"&D2&"*")
Count if cell contains number
The formula to count cells with numbers is the simplest formula one could imagine:
Please keep in mind that the COUNT function in Excel counts cells containing any numeric value including numbers, dates and times, because in terms of Excel the last two are also numbers.
In our case, the formula goes as follows:
=COUNT(A2:A10)
To count cells that DO NOT contain numbers, use the SUMPRODUCT function together with ISNUMBER and NOT:
=SUMPRODUCT(--NOT(ISNUMBER(A2:A10)))
For more examples, see Excel formulas to count cells with certain text.
Sum if cell contains text
If you are looking for an Excel formula to find cells containing specific text and sum the corresponding values in another column, use the SUMIF function.
For example, to find out how many dresses are in stock, use this formula:
=SUMIF(A2:A10,"*dress*",B2:B10)
Where A2:A10 are the text values to check and B2:B10 are the numbers to sum.
Or, put the substring of interest in some cell (E1), and reference that cell in your formula, as shown in the screenshot below:
To sum with multiple criteria, use the SUMIFS function.
For instance, to find out how many blue dresses are available, go with this formula:
=SUMIFS(B2:B10, A2:A10,"*dress*",A2:A10,"*blue*")
Or use this one:
=SUMIFS(B2:B10, A2:A10,"*"&E1&"*",A2:A10,"*"&E2&"*")
Where A2:A10 are the cells to check and B2:B10 are the cells to sum.
Perform different calculations based on cell value
In our last tutorial, we discussed three different formulas to test multiple conditions and return different values depending on the results of those tests. And now, let's see how you can perform different calculations depending on the value in a target cell.
Supposing you have sales numbers in column B and want to calculate bonuses based on those numbers: if a sale is over $300, the bonus is 10%; for sales between $201 and $300 the bonus is 7%; for sales between $101 and $200 the bonus is 5%, and no bonus for under $100 sales.
To have it done, simply multiply the sales (B2) by a corresponding percentage. How do you know which percentage to multiply by? By testing different conditions with nested IFs:
=B2*IF(B2>=300,10%, IF(B2>=200,7%, IF(B2>=100,5%,0)))
In real-life worksheets, it may be more convenient to input percentages in separate cells and reference those cells in your formula:
=B2*IF(B2>=300,$F$5,IF(B2>=200,$F$4,IF(B2>=100,$F$3,$F$2)))
The key thing is fixing the bonus cells' references with the $ sign to prevent them from changing when you copy the formula down the column.
Excel conditional formatting if cell contains specific text
If you want to highlight cells with certain text, set up an Excel conditional formatting rule based on one of the following formulas.
Case-insensitive:
Case-sensitive:
For example, to highlight SKUs that contain the words "dress", make a conditional formatting rule with the below formula and apply it to as many cells in column A as you need beginning with cell A2:
=SEARCH("dress", A2)>0
Excel conditional formatting formula: if cell contains text (multiple conditions)
To highlight cells that contain two or more text strings, nest several Search functions within an AND formula. For example, to highlight "blue dress" cells, create a rule based on this formula:
=AND(SEARCH("dress", A2)>0, SEARCH("blue", A2)>0)
For the detailed steps, please see How to create a conditional formatting rule with a formula.
If cell contains certain text, remove entire row
In case you want to delete rows containing specific text, use Excel's Find and Replace feature in this way:
- Select all cells you want to check.
- Press Ctrl + F to open the Find and Replace dialog box.
- In the Find what box, type the text or number you are looking for, and click the Find All
- Click on any search result, and then press Ctrl + A to select all.
- Click the Close button to close the Find and Replace
- Press Ctrl and the minus button at the same time (Ctrl -), which is the Excel shortcut for Delete.
- In the Delete dialog box, select Entire row, and click OK. Done!
In the screenshot below, we are deleting rows containing "dress":
If cell contains, select or copy entire rows
In situations when you want to select or copy rows with relevant data, use Excel's AutoFilter to filter such rows. After that, press Ctrl + A to select the filtered data, Ctrl+C to copy it, and Ctrl+V to paste the data to another location.
To filter cells with two or more criteria, use Advanced Filter to find such cells, and then copy the entire rows with the results or extract only specific columns.
This is how you manipulate cells based on their value in Excel. I thank you for reading and hope to see you on our blog next week!
Practice workbook
Excel If Cell Contains Then - examples (.xlsx file)
244 comments
I have a time sheet that has a job code that I would like excel to reference to add up the hours that each employee has worked for that job to come up with the total hours worked on that job for the week.
So, if there is a letter in one cell, I want excel to take the numbers from another cell and add them all together for every occurrence of that letter in the table.
Hello!
To calculate the amount by conditions, use the COUNTIFS function. You can find the examples and detailed instructions here: Excel COUNTIFS and COUNTIF with multiple AND / OR criteria.
I'm not sure that is the correct formula.
Jobsite Code Job Total
AB A
CD C
FG D
Employee Mon Job Tue Job Wed Job
John 6 A 8 C 7 D
Joe 9 C 7 A 8 C
Taking the table above, I want Excel to look for A and add the hours from the corresponding cell; so, there's an A in D7 and F8, I want Excel to add the hours in D6 (6 hours) and F7 (7) together for total hours worked on job A
Hi!
You can use SUMIF formula:
=SUMIF(C1:C10,"A",B1:B10)+SUMIF(E1:E10,"A",D1:D10)
Or SUM formula:
=SUM((B1:B10)*(--(C1:C10="A")),(D1:D10)*(--(E1:E10="A")))
I hope I answered your question.
What formula would I use (or is there one) to total the hours employees took to take certain staff training requirements (example table below):
A B C D E F G
1 Employee Name Course 1 Course 2 Course 3 Course 4 Course 5 Hours Completed
2 Course Length 3.25 1.25 1.50 1.00 0.75
3 Employee 1 10/17/22 10/12/22 10/12/22
4 Employee 2 10/16/22 10/04/22 10/12/22
5 Employee 3 10/17/22 10/13/22 10/13/22
6 Employee 4 10/17/22 10/17/22 10/11/22 10/11/22
Hi!
Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you use SUMPRODUCT function.
=SUMPRODUCT(B1:F1,--(B2:F2<>""))
Sorry the table didn't come across very well, but that's exactly the formula I needed. Thank you!
Is possible to sum this text?
Some text in cell. r=11
R=1,5. Some text in cell.
Sum all r=? and, that's in one row.
Hello.
Hello!
Here is the article that may be helpful to you: Extract number from text string.
Thank you very much
Hi i want to add following numbers which are present in a row
23,45,#N/A, 56,#N/A, 756,...
Please suggest the formula to solve it.
Hi!
I don't see any logic in your numbers. Try to look for a solution to the sequence of numbers in the article: SEQUENCE function in Excel - auto generate number series.
I've got the same issue I think Baiju is having, in that Excel isn't clear on how you add up a series of columns or rows if some of the cells in the range have #NA or #NUM instead of a number.
I've got someone's spreadsheet I'm trying to salvage. They're trying to track the total invoices they process each month, amount of days to process each one, the number that are late (>30 days), and the $ amount for the invoices that are late. So, I've been using =DATEDIF and =IF(AND and =COUNTIF formulas to take a stab.
It's cumbersome but would be working, except their spreadsheet includes all their invoices, including those that haven't cleared yet. As a result I get #NUM! or #NA in those cells. When I try to sum the monetary values for the month (either =SUM or =COUNTIF) it fails because of the #NUM and #NA in the range. I haven't been able to find an explanation on what to do when you've got non-numbers in a column you're trying to add, so any advice you can provide would be appreciated. Thanks.
Hello!
Use the IFERROR function in formulas to avoid getting an error message. To calculate the sum, you can use the AGGEGATE function instead of the SUM function. If the second argument to this function is 6, then errors will be ignored.
I hope it’ll be helpful.
Hi,
I need help to assign weights to text. For example, in the following response table,
Question No Response Weights
Q1 Red 1
Q2 Blue 2
Q3 Green 1
Q4 Red 2
Clearly, questions 2 and 4 are having higher importance. When commuting the response, it needs to read as
Red: (Q1*1+Q4*2) = 3
Blue: (Q2*2) =2
Green: (Q3*1) =1
Total =7 (not 4)
I'm trying to make a formula in which if an exact value is can get 4 different values in 4 different cells . Eg if A1=4 then B2=12, C2=199, D2 =122,E2=78
Hi!
We wrote many times that an Excel formula can change the value only in the cell in which it is written. You need to use a VBA macro.
Or use an IF formula in each cell.
Hi and thank you.
Stuck trying to work out formula for staff roster, whereby in a row of cells x 7 (one week) each shift worked (x 3 shifts, which equal 12 hours each) adds the sum of these shifts to a separate cell.
Eg.. Staff Name / Sum of Total Hrs / LD / / LD / LD
Should look like-
Staff name / 36 / LD / / LD / LD
This will actually be over 6- week roster, but just trying to get an idea of what to do in the above eg.
Thanks
Hi!
I’m not sure I got you right since the description you provided is not entirely clear. However, it seems to me that the formula below will work for you:
=COUNTIF(C1:I1,"LD")*12
Use the COUNTIF function to count the number of specific values in seven cells.
Hello,
I'd really appreciate help with a formula.
Column E is an IF statement and depending on a date range puts "YES" or "NO" in column E.
I'm trying to get a separate cell to provide the SUM of the number of "YES"s. SUM and SUMIF do not appear to work. For example, I have attempted the formula =SUMIF(E2:E20,"YES"). It incorrectly provides "0" as the answer. Is this because Column E is a separate formula itself. How do I get around this?
Your help is much appreciated.
Hello!
The SUMIF formula works with the results of formulas in the same way as with normal values. Your formula should work. Check what values are returned by your formulas in cells E2:E20. Perhaps there are extra spaces.
Hello,
I would appreciate your help on a formula.
I am creating a vertical employee absence calendar in Excel (dates at the top, names on the side) with leave entered as text: holiday (H), half day holiday (HH), sick leave (S), and half day sick leave (SH).
I would like to add a row total for each person.
With COUNTA all entries = 1
So for e.g. H + S + HH should add up to 2.5 but COUNTA shows 3.
How can Excel add up different values for each type of text? So that that H = 1, HH = 0.5, S=1 and SH=0.5?
Very grateful for your help
Valerie
Many thanks for your help!
Hi!
The COUNTA function counts the number of cells. So it cannot return a fractional number. Replace letters with numbers using the IF function:
=IF(A1="H",1,0)+IF(A2="HH",0.5,0)
Hi there. I am trying to write a formula where I need to count how much three people spent.
Name cost. Name total spent
Me 300 me. ?
You 150 you. ?
Her 200 Her. ?
Me 140
You 200
Her 500
Hi! I am not sure I fully understand what you mean.
I have to calculate a total cost per date range for each event. I have a sheet that has the daily rate for different pay scales. I am trying to get the total cost for example: Cell B8 has a drop down for different pay grades. Cell E8 has the start date. Cell F8 has the end date. Sheet 2 has the data to pull from. Sheet 2 column B has the pay grades. Column C has the daily pay rate. How do I get on Sheet 1 Cell H8 to calculate that?
Hi! I need to see your data to give you an exact formula. Perhaps this formula will help:
=(F8-E8)*B8*C8
Use the company's payroll manual.
How will it know what pay rate to use? I in sheet 2? What is the best way for me to send you the data
To find the value in column B on sheet 2 and get the corresponding data from column C, you can use the VLOOKUP function and these instructions: VLOOKUP between two worksheets.
Thank you that worked. Now how do I in the same formula tell it if there is an additional function to look up. So far I have: =xlookup(B$8:B$900,SHEET2!B2:B29,SHEET2!C2:C29*G8,FALSE). If this is their pay without a daily stipen how do I tell it to add the daily stipen to this formula? Sheet 1 Column D will have a dropdown yes or no for the stipen. sheet 2 will have the stipen for the correct pay grade. Sheet 2 column D will have a drop-down for yes or no. Sheet 2 column E has the pay grade and column F will have the daily stipen rate. Example An O3 has a stipen of $33.00 But an E3 will have a stipen of $8.00.
Hi! Try to use these instructions: XLOOKUP to return multiple columns or rows. If that's not what you need, create another XLOOKUP formula to extract the second number.
Thank you for this. I am still trying to learn xlookup. This was very helpful. I have been able to get most of what I need. Thank you again for all the help
Hi
how I sum if I had 3 table
1 vendor (Samsung or Huawei)
2 Cell mode (ex Samsung I9500 series)
3 count
I used below formula but it count other vendor model as well
=SUMIF(J2:J23068,"*I9500*",K2:K23068)
can you please support for the correction
Hi!
To calculate the sum of two criteria, use the SUMIFS function.
This should solve your task.
Hi
I need your support for correction of formula.
Please check below this formula is correct or not
=SUM((Sheet2!$D$8:$QC$11)*(--(Sheet2!$C$8:$C$11='Cleaning details'!$D6))*(--(Sheet2!$D$4:$QC$4='Cleaning details'!$C6))*(--(Sheet2!$D$3:$QC$3='Cleaning details'!$E$4)))+SUM(Sheet2!$D$12:$QC$15)*(--(Sheet2!$C$12:$C$15='Cleaning details'!$D6))*(--(Sheet2!$D$5:$QC$5='Cleaning details'!$C6))*(--(Sheet2!$D$3:$QC$3='Cleaning details'!$E$4))+SUM(Sheet2!$D$16:$QC$19)*(--(Sheet2!$C$16:$C$19='Cleaning details'!$D6))*(--(Sheet2!$D$6:$QC$6='Cleaning details'!$C6))*(--(Sheet2!$D$3:$QC$3='Cleaning details'!$E$4))+SUM(Sheet2!$D$20:$QC$23)*(--(Sheet2!$C$20:$C$23='Cleaning details'!$D6))*(--(Sheet2!$D$7:$QC$7='Cleaning details'!$C6))*(--(Sheet2!$D$3:$QC$3='Cleaning details'!$E$4))
Because this is not working in single cell.
Hi!
It is very difficult to understand a formula that contains unique references to your workbook worksheets. Hence, I cannot check its work, sorry.
Screenshot link to reference my sheet: https://gyazo.com/36d77c6c536984cfe9b56d1bc51b6e09
Hi, my business gives commissions to our drivers (called “captains” as shown in the Excel screenshot). Their commission is 10% of the sale price of a surcharge they sell.
I want to track their daily commissions per captain and then total each daily commission for their weekly total commission per captain.
I already have their daily tracking set up to automatically calculate their daily commissions based on the prices of surcharges and tally of quantity sold for each.
My issue now is making the formula that automatically searches row P5 to T5 for instances of their unique name being written. Each time their name is written in that row indicates one working day with commissions made (total for each daily commission is in row P30 to T30) in their own column.
I would then like the same formula to have N34 to N36 autofill if there are captain’s names found in P5 to T5 (without duplicating their names) and then totaling their daily commissions specific to each captain’s name in the weekly totals at O34 to O36.
I hope this makes sense. Let me know if you need clarification.
I’ve tried for several hours to figure it out with various formulas but I’m not too versed in Excel so I’m hoping someone can help me out. Thanks in advance!
Hi!
Sorry, there is no simple solution for your task. At least I do not know such :(
Hi,
SUMIF function is working for me but SUMIFS is not working even though I tried so many times. =SUMIFS(J2:J29,G2:G29,"Passive",G2:G29,"Connector") is the formula which I am trying in my excel. I also have used * prior to text values but no result and instead its showing 0. I can't understand why its showing 0. Kindly help.
Hello!
I can't see your data and can't check, but this formula should work
=SUMIFS(J2:J29,G2:G29,”*Passive*”) + SUMIFS(J2:J29,G2:G29,”*Connector*”)
I recommend reading this guide: Excel SUMIF with multiple criteria (OR logic).
Hope this is what you need.
I have an Excel sheet where in column A i have various dates for sales over a three year period and in column B I have another column which contains text relating to the type of sale, either "Direct" or "Indirect" to indicate if the sale was a direct sale or through another party. I want to be able to count how many Direct or Indirect sales were made in each year. Can you help? I have tried combining the COUNTIF and COUNTIFS formulas but it's not working. I can get the number of sales in each year by using the COUNTIFS formula but the problem starts when I try to extract the count in each year for each of Direct or Indirect by combining the two formulas. Thanks! Your website is amazing! I've managed to solve a lot of problems by checking your solutions.
Hello!
What formulas are you using? What's not working in them? You must use the COUNTIFS formula with two conditions - Date and Sales Type.
Please have a look at this article - Excel COUNTIFS and COUNTIF with multiple AND/OR criteria
If they don’t work for you, then please describe your task in detail, I’ll try to suggest a solution.
Hello,
What formula could I use to add up both numbers and letters in the same column? For example, I have 12 cells with values in the same column (the "x" respresents 1):
10
x
x
2
3
x
x
x
x
x
x
x
Before the numbers were added and we were only counting "x", we used this formula =COUNTIF(G73:G85,"=x")
Now that numbers are also included, I am having trouble finding a formula that adds both "x" as 1 and the rest of the numbers.
Thank you!
Hello!
If I understand your task correctly, the following formula should work for you:
=SUM(A1:A10)+COUNTIF(A1:A10,"x")
SUM function only adds numbers and ignores text.
Hi,
I have two tabs:
First tab:
Column A Name
Column B Yes ( or No)
For example :
Column A Column B
Anna Yes
Lily No
If column B is value is Yes, I want to add column A value (Anna) to the other tab A1.
If column B is value is No, I don't want to add column A value (Lily) into the other tab.
How to achieve that?
Thanks,
Hello!
You can use the guidelines and examples from the article: How to VLOOKUP multiple values in Excel with one or more criteria.
You can also use the FILTER function to get the values you want.
I hope my advice will help you solve your task.
I am having a really hard time trying to get the formula I need. The spreadsheet is for stock options. For easy example, column A are debits that it cost to open the contract. The adjacent cell in column B is the price I sell to close the contract. I have everything I need for the gain/loss and percentage taken. I am trying to figure out how I would calculate my true return? if for example I open a contract for -$500 and sell it for $750, I have a return of 50% on that specific row. However if I open another trade using that same $500 and then the $250 I captured for a trade as well, how would I calculate my overall return? It would want to calculate my return on $1250 instead of $750. Any help would be greatly appreciated.
Hi!
We are not stock specialists. If you have a specific question about the Excel formula, we will try to help.
Trying to make a formula using sumifs in B1 "lol" && C1 "wow
A1 blank cell (i want to put name text here which will be the reference of A2:A10)
B1 show the total lol
C1 show the total wow
A2:A10 Names
B2:B10 [sum_range]
C2:C10 text "Lol" or "wow"
I want to sum the value in B2:B10 if it meets the condition:
Sumif:
Condition 1 If A2:A10 is true (or same with the text I input in A1)
Condition 2 If C2:C10 "lol" or in "wow" category
e.q.
A1 "10/06"
B1 (if A1 is true A2;A10 sum the total 10/06 with lol
C1 sum the total 10/06 wow
Hello!
To find the sum of values for conditions, use the SUMIFS function.
If something is still unclear, please feel free to ask.
Hey Alex,
At the outset, very wonderfull site and informative.
I have the data in the following cols
Col A Col B
21-Sep-21 55
23-Sep-21 74
02-Oct-21 21
05-Oct-21 05
All I want to do is add the values apprearing for Sep and Oct as below
Sep'21 xxxxx
Oct'21 yyy
Hi!
I don't understand what you want to do. Explain in more detail.
I am trying to find a formula to calculate cum GPA but will subtract the grades that have "TR" by it in the semester column. What formula if any would work for this?
EXAMPLE
Grade Hrs Points Semester
A 3 12 SP20
B 3 9 TR
C 2 4 FA21
D 1 1 TR
Hi!
Try using the AVERAGEIF function to calculate the average with conditions.