Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How can I add up how often certain text occurs?
Hi everyone.
My problem is to add up how often certain text occurs in a column. Doing this when the cells I want to use are sequential eg B2 - B12, is easy. I use =COUNTIF(B2:B12,"E"). What I can't do is add up when the cells aren't sequential eg B2 - B5, B7, B10 - B12. I try =COUNTIF(B2:B5,B7,B10:B12,"E"), but excel tells me I've entered too many arguements. Using colons and commas in this way works when I add numbers, but not when I add occurences of text, and I don't know why! Any help would be gratefully recieved. Many thanks, Martin |
#2
|
|||
|
|||
Is there a reason why you can use the cells sequentially? Unless the cells
you're omitting contain the result you're looking for, it shouldn't count them right, or am I missing something? "Martin M" wrote: Hi everyone. My problem is to add up how often certain text occurs in a column. Doing this when the cells I want to use are sequential eg B2 - B12, is easy. I use =COUNTIF(B2:B12,"E"). What I can't do is add up when the cells aren't sequential eg B2 - B5, B7, B10 - B12. I try =COUNTIF(B2:B5,B7,B10:B12,"E"), but excel tells me I've entered too many arguements. Using colons and commas in this way works when I add numbers, but not when I add occurences of text, and I don't know why! Any help would be gratefully recieved. Many thanks, Martin |
#3
|
|||
|
|||
Try...
=SUMPRODUCT(--(ISNUMBER(MATCH(ROW(B2:B12),{2,3,4,5,7,10,11,12},0 ))),--(B2 :B12="E")) OR =SUMPRODUCT(COUNTIF(INDIRECT({"B2:B5","B7","B10:B1 2"}),"E")) Hope this helps! In article , "Martin M" wrote: Hi everyone. My problem is to add up how often certain text occurs in a column. Doing this when the cells I want to use are sequential eg B2 - B12, is easy. I use =COUNTIF(B2:B12,"E"). What I can't do is add up when the cells aren't sequential eg B2 - B5, B7, B10 - B12. I try =COUNTIF(B2:B5,B7,B10:B12,"E"), but excel tells me I've entered too many arguements. Using colons and commas in this way works when I add numbers, but not when I add occurences of text, and I don't know why! Any help would be gratefully recieved. Many thanks, Martin |
#4
|
|||
|
|||
Hello Martin You already have the answer using =COUNTIF(A1:A12,"E") will add only E's even if you have other letters within the range. Hope this helps -- robmeister ------------------------------------------------------------------------ robmeister's Profile: http://www.excelforum.com/member.php...o&userid=24364 View this thread: http://www.excelforum.com/showthread...hreadid=380158 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting based on Text within Text | Excel Discussion (Misc queries) | |||
SUMPRODUCT vs Text??? | Excel Worksheet Functions | |||
Autofitting a row | Excel Discussion (Misc queries) | |||
Sort or Filter option? | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) |