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)
248 comments
Hi! I'd like to ask if is it possible to have a formula like the example using the sumifs, however, the categories are within a single cell?
Then the categories will be search on another column?
For example: (like your visualization in Sum if cell contains text)
The category on the E1 is Dress, & Size.
Then the return value will be sum of all cells that contains "dress" and "size", not "dress & size".
Thank you
Hello Jassy!
The SUMIF and SUMIFS functions cannot use a part of the value from a cell as a criterion.
Split the “Dress, & Size” text string into two separate cells using any of the methods described in this article: How to split text string in Excel by comma, space, character or mask. You can then use each of these two values as criteria as described in the article above.
i need Sum : CELL 1 ,CELL 3 ,CELL 5..... like more CELLS , so can i know about any formula.
thanks.
Hi! To sum the values in the odd-numbered rows (for example, from 1 to 51) in Excel, you can use the SUMPRODUCT function along with a condition to filter for odd rows.
Assuming your data is in column A, you can use the following formula:
=SUMPRODUCT((MOD(ROW(A1:A51),2)=1)*A1:A51)
This formula checks if the row number is odd by using the MOD function, which returns the remainder of the division of the row number by 2. If the remainder is 1, the row number is odd.
Hello,
I'm trying to sum the amount of values before a specific text in a cell.
for example: 6@48x48x81, 1@48x48x81
formula I am using: =LEN(ref. column)-LEN(SUBSTITUTE(ref. column,"@",""))
This formula returns 2 as it is counting the amount of times @ is found.
I need this to return 7.
Hi! Find the position of "@" character using SEARCH function.
If the "@" character is not found, return 0 using IFERROR function.
Extract all digits from the text before the position of this character using LEFT function.
Convert the extracted characters to numbers using VALUE function.
Sum the numbers using SUM formula.
=SUM(VALUE(IFERROR(LEFT(A1:A10,SEARCH("@",A1:A10)-1),0)))
Hello,
The values in that example are comma separated in the same cell. I tried the formula given, but the result returns 6.
I'm working with a large data set that has numerous different criteria like the original example. For reference, another example would be:
5@50x45x86, 1@50x45x86, 1@50x45x89, 1@50x45x68
I would need this to return 8.
Hi! Split text into cells using any of methods suggested in this article: How to split cells in Excel: Text to Columns, Flash Fill and formulas. Then use the previously recommended formula.
To split text by cells, you can also use the TEXTSPLIT function.
=SUM(VALUE(IFERROR(LEFT(TEXTSPLIT(A1,,", "),SEARCH("@",TEXTSPLIT(A1,,", "))-1),0)))
Detailed instructions here: TEXTSPLIT function in Excel: split text strings by delimiter.
I have been looking through IF formula's but can't find what I want, I want to be able to ask IF (one cell contains certain letters say HJP), then multiply another cell by 0.1. IF not score "0". I have tried =IF(A2="HJP", "E2*0.1", "0")- this got the 0 right but just copied text for calculation part. Thankyou for your help.
Hello! I don't quite understand which cell you want to compare with the number 0. If the second condition is dependent on the first condition, then try using this instruction: Nested IF in Excel – formula with multiple conditions.
thanks. I feel like I can't get it to do a calculation based on the if. What i am trying to do is record payments. some need to have gst and some dont. so IF (one option needing gst) then calculating 10 % of another cell, but if (other options) then can just record '0'.
Hi Kristin!
I don't have any idea what kind of condition you have, or what "gst" is. I think the recommendations from the article above will be helpful. If they don't help, give an example of the source data and the desired result.
I tried using SUBTOTAL(Function,Range to count). Excel provides you an option to Select the operation (Add/Subtract/Divide etc..) under Function and range of values where you need to count and list in another cell. My requirement was to Sum the value in a range based on filter selected.
Hi! The answer to your question can be found in this article: Excel SUBTOTAL function with formula examples.
Argument Function 101 - 111 ignore all hidden cells - filtered out and hidden manually. For example:
=SUBTOTAL(109,C2:C20)
I have a spreadsheet with quotes, quote amount, PO and then finaly invoice number. I want to calculate on the lines that has a inv number on the quote amount. In other words what has been invoiced.
Hi! To find the total amount for a specific invoice number, you can use the COUNTIF function. If you need to find a sum for more than one condition, use these instructions: How to use Excel COUNTIFS and COUNTIF with multiple criteria.
Hi,
I have a sheet of our business with various branches in our region. I pull a report daily from our ERP system of all the products with stock codes starting with "F" i.e. "Fxxxxxxx"
The branch name is in column A, the Stock codes are in Column B and the sales value of each product is in column E.
I need a formula to extract the total sales value for each branch on stock items with F codes. Can anyone help?
Hi! You can use the SUMIF formula to determine the value of sales for a specific branch. Read more: How to use SUMIF function in Excel with formula examples. Use these instructions if you have multiple conditions to summarize: Excel SUMIFS and SUMIF with multiple criteria. I hope it’ll be helpful. If something is still unclear, please feel free to ask.
using this formula
=SUMIFS(C1:C5,A1:A5,D1,B1:B5,D2)
if my DATA is this
Chocolate || _____ || 4 |Chocolate
Brown Sugar || 1kg || 3 || ______
if my data for D1 is chocolate why it will show "0" but if my Data in D1 is Brown Sugar 1kg it will count? what is my error?
Hi! The SUMIFS formula cannot use an empty cell as a criterion. If 0 is written in the cell, the formula will work.
My sheet contains drop down lists. Cell C5 has 4 options and Cells D7:M7 have 3. I need a formula that will add D9:M9 if Cell C5 contains "Summit", or "ARTC", or "Empower" AND if D7:M7 contains "SL 1:1". I have been trying to figure out how to do this for 2 days now and would really appreciate some help.
Hi! If I understand your task correctly, this guide may be helpful: Nested IF with OR/AND conditions.
I can't offer you an example formula, as I don't understand what "formula that will add D9:M9" and "D7:M7 contains "SL 1:1"" mean. But I think that with the help of these instructions you will be able to solve the problem.
Hi! I was wondering if you could help me formulate a cell. I'm trying to formulate a cell so that when the sum of other cells (B2-B10) is equal or less than 14 the word 'low' will populate, and if the sum is equal or greater than 15 the word 'high' will populate.
Thank you so much!
Hi! Calculate the sum of the cells using the SUM function. If these cells contain integers, you can use the IF formula.
=IF(SUM(B2:B10)<=14,"low","high")
If the sum can be a fractional number, try the nested IF formula.
=IF(SUM(B2:B10)<=14,"low",IF(SUM(B2:B10)>=15,"high",""))
Hello Sir,
I want to compute the number of sold in an item with multiple variants.
Example:
A B C
1 = Melon || Big || 3
2 = Apple || Small || 2
3 = Melon || || 2
Some item has no variants so. my formula should read or count the particaular amount of item if it has a variants and if no varians it will base in the item only. i dont know if i explain it correcti=ly :) thanks
Hi! To calculate the sum of items sold according to two criteria, use the SUMIFS function. This formula count the amount of item "melon" if it has a variants:
=SUMIFS(C1:C5,A1:A5,"melon",B1:B5,"<>")
If this is not what you wanted, please describe the problem in more detail.
Example:
A B C
1 = Melon || Big || 3
2 = Apple || Small || 2
3 = Grapes ||___ || 2
4 = Melon ||Big|| 6
5 = Melon ||___ || 4
When i tried the formula it will not count the grapes if there is no value in it
Hi! Your explanations are not very clear to me. If you want to count only by product name, then remove the second condition from the formula.
=SUMIFS(C1:C5,A1:A5,"melon")
You can use the COUNTIF function to count by one condition.
If this is not what you wanted, provide me with an example of the source data and the expected result.
I will explain it clearly again since i think my response is broad :)
when i have an item that has no variant it will still count it. in the formula if put melon and no variant it will not count. it will return 0
It is already good but if my chosen variant is small it calculates all small. if i want to calculate the variant for a particular item only.
Hi, I have a spreadsheet with 5 columns (A-E) containing date, machine, asset ID, litres, hours/km. I need to calculate the monthly fuel usage for each machine (12 machines). What is the formula I need to use? Machines are refuelled daily, sometimes twice daily, and some are refuelled once every couple of months. The result I'm after is Month, Asset ID, Month Total Litres for each machine, i.e. March, Menard, 4164. March, EX2301, 3059. etc.
Data as follows:
Date Machine Asset ID Litres Hours
16-Feb-24 Volvo Menard 222
16-Feb-24 Hyundai EX2301 74 2177
16-Feb-24 G200-02 195 458
16-Feb-24 JCB TH3101 15 1468
16-Feb-24 Ute 1XE1II 22 7924
16-Feb-24 Ute L35HV 11 1526
19-Feb-24 Volvo Menard 424
19-Feb-24 Hyundai EX2301 140 2192
19-Feb-24 G200-02 145 477
28-Feb-24 Volvo Menard 351
28-Feb-24 Hyundai EX2301 107 2204
28-Feb-24 G200-02 139 491
28-Feb-24 Ute L35HV 14 1601
28-Feb-24 Ute 1XE1II 46 8317
29-Feb-24 Volvo Menard 303
29-Feb-24 Hyundai EX2301 126 2213
Thank you for your assistance.
Hi! To find the sum for multiple criteria, you can use these guidelines: Excel SUMPRODUCT function with multiple criteria and Excel SUMIFS and SUMIF with multiple criteria – formula examples.
Based on this information, the formula could be as follows:
=SUMPRODUCT((A1:A10>=DATE(2024,2,1)) * (A1:A10<DATE(2024,3,1)) * (C1:C10=H1) * D1:D10)
or
=SUMIFS(D1:D10, A1:A10, ">=1/2/2024", A1:A10,"<1/3/2024", C1:C10, H1)
H1 = machine (for example, "Menard")
Thank you very much :)
Simple formula, one of the problems when you overthink things when the brain is tired - you miss the simple approach.
Have a sort of complex question about formulas…
G4 – T4 contain numbers. These numbers are serving as a “point value”. G4 is worth 1 “point” so 1 is entered in the cell, M4 is worth 3 “points” so 3 is entered in the cell, etc.
I have names entered into column F (F8 – F40). Each Row contains a different name.
I am attempting to create a matrix where I can enter a date of completion into cells G8:G40 – T8:T40. Once that date is entered, I want to have it add the “points” from each column.
Example:
Bill Kapri has a date of completion entered into cell G10, M10, and R10.
Column G has a “point value” of 1 (number entered into cell G4), Column M has a “point value” of 3 (number entered into cell M4), and Column R has a "point value” of 1 (number entered into cell R4).
I’d like the total of those “points” from row 4 to add together, only for the columns with a date of completion entered.
--
What formula would I need to accomplish such a task? Is this possible?
Hi! If I understand your task correctly, the following formula should work for you:
=SUMPRODUCT(($G$4:$T$4)*(G8:T8<>""))
Read more: Excel SUMPRODUCT function with multiple criteria.
Thank you! Really appreciate your help and the link to additional information.
Hi there,
I found this explanation very useful, but I haven't been able to find the right formula for what I am trying to do yet. I have a table with two columns: one with the name of the month and year in a MMMYY format ('Jan20, 'Feb20, etc.) and another with numbers (=total amount for each month). Is there a formula that would allow me to select/write down two of the first column values and get the sum of all the totals in that range. For example:
Jan20 --- 100
Feb20 --- 250
Mar20 --- 75
Apr20 --- 89
May20 --- 183
In my summary table, writing "Feb20" in one cell (let's say, B3) and "May20" in the next one (C3) would produce a result of "597" in the cell where the formula resides (D3).
Do you think it's doable? Let me know if you need any more details. Thank you!
Hi! If I understand your source data correctly, you have text in the first column, not a date. Therefore, you cannot calculate the sum in the time interval. If the date is written, you can use SUMIF function and this instruction: How to use Excel SUMIF with dates.
Hi,
I have four columns, each have text in them. I want to count the number of cells in a row (B1-D1) that contain a specific text based on if the cell in the first column (A1) says "Zoom" or "Office." Thank you!
A B C D Zoom Total Office Total
1 Zoom Individual Individual Group 3
2 Office Group Book Club Individual 3
3 Zoom Book Club Group 2
Hi! Unfortunately, this information is not enough to recommend a formula to you. To count cells by multiple conditions, use the COUNTIFS function and this guide with examples: How to use Excel COUNTIFS and COUNTIF with multiple criteria.
Hi,
Sorry about that. I'm trying to write a formula for if a cell in a row contains a particular text, then I want to count the number of adjacent cells in the row that are not blank. So, if A1="Zoom", then count B1, C1, D1 if they are not blank and return that answer in E1 (answer would be a number between 0 and 3).
Hi! If you want to count non-empty cells by condition in a single row, try this formula:
=SUM((A1="Zoom")*((B1<>"")+(C1<>"")+(D1<>"")))
In range:
=SUM((A1:A3="Zoom")*((B1:B3<>"")+(C1:C3<>"")+(D1:D3<>"")))
I hope my advice will help you solve your task.
It worked - thank you so much!
Is it possible to use sum and countif to count/sum no of transactions? I am using countifs/sum but I want it to not count swap as 2 transactions.
I.e the formula to minus 1 count as duplicate when cross adjacent of the cell is same value, no other criteria
This is because FX SWAP contains 2 rows, i.e cell A2: USD50, cell B2: EUR40, cell A3: EUR40, cell: B3: USD50. And also USD will always be the same value.
Anyone having the same issue ?
Hi! I’m sorry, but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. For example: "USD50" - text or number format? What are the criteria for summarizing? Please specify what you were trying to find, what formula you used. Give an example of the source data and the expected result.
Hi.. i need to sum a row of cells with numbers and text but ignore the text. apparently i tried varies formula i cant get the total sum to remain as it will minus the amt when i input a text in one of the row.. pls help
eg. i hv a row for Feb (1 feb - 29 Feb)
in the row below each date, ive input either a OFF or 1
eg. 1 Feb - OFF, 2 Feb - 1, 3 Feb - 1 <- i use =COUNTIF(I80:K80,"1")*2.2 = 4.4
BUT when i insert a text in 2 FEB-RED, it will minus the text and the sub-total number = 2.2
Hi! Your formula only counts cells with 1 in them. It is difficult to understand from your explanation what result you need. Maybe this article will be helpful: COUNTIF function in Excel - count if not blank, greater than, duplicate or unique.
Thank you for this page.
I have a report with 10 columns and 18139 lines. In the B column are product codes (ie RTPGH07304) and the H column has a quantity. The product codes may be repeated several times down the B column. I want to figure out the total quantity overall for a particular product code. For instance, the RTPGH07304 product code may appear three separate times in the B column. In the H column, there is a quantity of 2, 4, and 6 for those separate lines. I want to be able to return a total of 12 to show how many copies of that product were sold. I believe the idea is like the ‘Sum if cell contains text’ section on this page, but I cannot figure it out.
Thank you for your help with this.
Hello! To calculate the total quantity for a specific type of product, use the SUMIF function. For example:
=SUMIF(B$2:B$20000,B2,H$2:H$20000)
You can copy this formula down along the column.
Try to use the recommendations described in this article: How to use SUMIF function in Excel with formula examples. I hope it’ll be helpful.
Thank you so much... it worked!! (and I will, thx!)
Thanks for the detailed explanations!
I have two tables with data. Table 1 has a column with a list of items, for example, the text "milking cows."
In another table, I have a list of ingredients, one of which is "milk".
I want to search for "milking cows" in the second table, and when Excel finds the word "milk," it should return the value from the column after "milk."
To start, I would search for 3 of the same consecutive values, but I can't figure out how to combine all this logic.
To recap, column 1 has a long list of items each cell has multiple words.
I want to find partial matches in column two, not only one of the words but also search for parts of the words.
If there is a match in column 2 then pull data from column 3.
I would be very thankful if you have any way to help with this.
Thanks!
Hello! Your description of the data is not quite accurate or clear. The value "milking cows" is written in the first table, but you are searching in the second table. It is also not clear whether you are searching for "milking cows" or "milk". To understand what you want to do, give an example of the data and the desired result.
Maybe this article will be helpful: How to find substring in Excel
Hey, I am trying to create a an absenteeism tracker and would like to be able to track the number of Overtime hours - I have row D4 to AH4 with the days of the months - I want to be able to code overtime as OT## and get a sum of the number of OT hours each employee works in AM4 - - Can I total the number beside the code OT in the row?
Hi! To identify cells containing the text 'OT###', use the ISNUMBER and SEARCH functions. The cells containing 'OT###' will have the value 1 in the resulting array. Before performing any mathematical operations, remove 'OT' from these cells using the SUBSTITUTE function. Finally, the two arrays are multiplied and the result is summed.
Try this formula:
=SUM(SUBSTITUTE(D4:AH4,"OT","") * ISNUMBER(SEARCH("OT",D4:AH4)))
I hope this will help.