Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count text then add results
I wish to add a row of cells that contain x or xx or xxx etc this I can
do with:- =SUM(COUNTIF(C47:G47,"x")+COUNTIF(C47:G47,"xx")+CO UNTIF(C47:G47,"xxx")+COUNTIF(C47:G47,"xxxx")+COUNT IF(C47:G47,"xxxxx")) The above gives me a count of the number of cells containing my criteria:- However I wish to count the occurrences, contents of the cells: of how many x in the range? e.g. c d e e f x xx x xxx = 7 I am new to this nay help would be gratefully received! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count text then add results
If there is nothing but "x"s in the range, then
=SUM(LEN(C47:G47)) entered as an array formula (Control Shift Enter). If there are other letters apart from the "x"s, then =SUM(LEN(C47:G47))-SUM(LEN(SUBSTITUTE(C47:G47,"x",""))) again as an array formula. Note that in your formula you didn't need SUM(), as you were adding the COUNTIFs with the + operator, and it doesn't need SUM as well as +. -- David Biddulph "TonyH" wrote in message ... I wish to add a row of cells that contain "x" or "xx" or "xxx" etc this I can do with:- =SUM(COUNTIF(C47:G47,"x")+COUNTIF(C47:G47,"xx")+CO UNTIF(C47:G47,"xxx")+COUNTIF(C47:G47,"xxxx")+COUNT IF(C47:G47,"xxxxx")) The above gives me a count of the number of "cells" containing my criteria:- However I wish to count the occurrences, contents of the cells: of how many "x" in the range? e.g. c d e e f x xx x xxx = 7 I am new to this nay help would be gratefully received! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count text then add results
Tony,
Try this. Adjust the range to suit. Note this does both Upper and Lower case X. If you want only one the delete the one you don't want from the formula =SUMPRODUCT(LEN(A1:A20)-LEN(SUBSTITUTE(A1:A20,{"x","X"},""))) Note this is an array and must ben entered by pressing CTRL+Shift+Enter NOT just enter. If you do it correctly then Excel will put curly brackets around the formula {}. You can't type these yourself. Mike "TonyH" wrote: I wish to add a row of cells that contain x or xx or xxx etc this I can do with:- =SUM(COUNTIF(C47:G47,"x")+COUNTIF(C47:G47,"xx")+CO UNTIF(C47:G47,"xxx")+COUNTIF(C47:G47,"xxxx")+COUNT IF(C47:G47,"xxxxx")) The above gives me a count of the number of cells containing my criteria:- However I wish to count the occurrences, contents of the cells: of how many x in the range? e.g. c d e e f x xx x xxx = 7 I am new to this nay help would be gratefully received! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count text then add results
Thanks David , works OK the second time I tried, forget the Control Shift Enter first time!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count only visible results of formula | Excel Discussion (Misc queries) | |||
need to compare dates and count results | Excel Worksheet Functions | |||
How can I count the results ??? | Excel Worksheet Functions | |||
count occurence and present results | Excel Worksheet Functions | |||
how do I set the status bar to count results from filter? | Setting up and Configuration of Excel |