Forum Discussion
Need help counting occurrences of text only when adjacent cell has numbers
Need help with formula to count the number of times "ABC" occurs if the adjacent cell has numbers in it. (i.e. not counting occurrence when adjacent cell is blank). The below spreadsheet show total a count of 2. I've tried count, counta, countif, countifs, sum, etc. but I cannot seem to get the formula to calculate 2.
| ABC | 123 |
| ABC | 12 |
| DEF | 456 |
| DEF | |
| DEF | |
| DEF | |
| DEF | |
| ABC | |
| ABC |
Let's say the first column is A2:A10.
=COUNTIFS(A2:A10, "ABC", B2:B10, "<>")
or if the numbers will always be positive
=COUNTIFS(A2:A10, "ABC", B2:B10, ">0")
- Thank You!
=SUM(N(IF(ISNUMBER(B1:B9),A1:A9="ABC")))You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
- Thank you!
