I have a column with text symbols in range of cells. I need to count some, omit others from the count. the range of text is like this: D, DN, ATB, QAQC, DTSS, D3D/N, ETC. Also is there a way to count a particular item, say D3D/N, which shows up as a count of one right now, but in reality its a count of two...D3D and N. Thanks for your time.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
etaf
Well-known Member
- Joined
- Oct 24, 2012
- Messages
- 8,276
- Office Version
- 365
- Platform
- MacOS
why is D3D/N two counts - because it has a /
ETC - does that mean ETC or etc as there are many others in the list ?
you could add up a number of countif()
assuming the values are in the range A2:A200
countif(A2:A200,"D")+ countif(A2:A200,"DN")+ countif(A2:A200,"ATB")+countif(A2:A200,"QAQC")+countif(A2:A200,"DTSS")+countif(A2:A200,"D3D/N")+countif(A2:A200,"ETC")
OR
=SUMPRODUCT(COUNTIF(A2:A200,{"D";"DN";"ATB";"QAQC";"DTSS";"D3D/N";"ETC"}))
ETC - does that mean ETC or etc as there are many others in the list ?
you could add up a number of countif()
assuming the values are in the range A2:A200
countif(A2:A200,"D")+ countif(A2:A200,"DN")+ countif(A2:A200,"ATB")+countif(A2:A200,"QAQC")+countif(A2:A200,"DTSS")+countif(A2:A200,"D3D/N")+countif(A2:A200,"ETC")
OR
=SUMPRODUCT(COUNTIF(A2:A200,{"D";"DN";"ATB";"QAQC";"DTSS";"D3D/N";"ETC"}))
Last edited:
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
Somewhat faster:
=SUMPRODUCT(ISNUMBER(MATCH(A2:A200,{"D";"DN";"ATB";"QAQC";"DTSS";"D3D/N"},0))+0)
=SUMPRODUCT(ISNUMBER(MATCH(A2:A200,{"D";"DN";"ATB";"QAQC";"DTSS";"D3D/N"},0))+0)
Upvote
0
Similar threads
- Question
