Results 1 to 8 of 8

20131119, 01:50 #1
 Join Date
 Nov 2013
 Posts
 8
 Thanks
 2
 Thanked 0 Times in 0 Posts
How to count text values from multiple cells in one cell
Hi All,
I am trying to count multiple doses of vaccine into one cell in a single arrow recorded for one child; I have not been able so far to do so. Any help is appreciated.
The targeted cells have a text value for either a valid vaccine as "TRUE" or invalid vaccine as "FALSE" or for no vaccine as empty cell. I want to count the valid doses of a vaccine in a single cell.
Regards,

20131119, 01:59 #2
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,570
 Thanks
 44
 Thanked 73 Times in 69 Posts
If your TRUE, FALSE and blank cells are in the range A14:L14, for example, you want to COUNTIF within that range there is a match with the word "TRUE".
=COUNTIF(A14:L14,"TRUE")

20131119, 02:21 #3
 Join Date
 Nov 2013
 Posts
 8
 Thanks
 2
 Thanked 0 Times in 0 Posts
Thanks a lot,
This worked. however within the range there are some "TURE" that I don't want to count. Please advise how to exclude those.
Regards,

20131119, 04:00 #4
 Join Date
 Nov 2013
 Posts
 8
 Thanks
 2
 Thanked 0 Times in 0 Posts
Dear Kweaver,
any idea on my question above?
I would appreciate that
Regards,

20131119, 09:54 #5
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,570
 Thanks
 44
 Thanked 73 Times in 69 Posts
=COUNTIF(A14:L14,"TRUE") this formula will count ONLY the word "TRUE"
If "TURE" was a typing error and you wanted to count is as "TRUE"
=COUNTIF(A14:L14,"TRUE")+COUNTIF(A14:L14,"TURE")Last edited by kweaver; 20131119 at 10:38.

20131119, 13:18 #6
 Join Date
 Nov 2013
 Posts
 8
 Thanks
 2
 Thanked 0 Times in 0 Posts
Dear Kweaver, you are genius! Thank you very much for teaching me all the way through. I am learning a lot.
I fixed earlier concerns which were with counting valid doses of vaccine for a child;
Now I need to count all cells in a row that have Either a "TRUE" or A "FALSE" value; but should not count cells that are empty. The objective is to find out how many children are fully immunized ( have gotten a full schedule of 8 doses of vaccines) no matter if they are valid "TRUE" (given within prescribed interval) or invalid "FALSE" (given but not within a prescribed interval) doses. So if a dose is missing then that should not be counted as fully immunized.
I tried below syntax it works for counting the "TRUE" and "FALSE" values but it does not exclude empty cells.
=COUNTIFS(DR13,TRUE,DI13,TRUE,CZ13,TRUE,CR13,TRUE, BZ13,TRUE,BQ13,TRUE,BI13,TRUE,BB13,TRUE)+OR(DR13,F ALSE,DI13,FALSE,CZ13,FALSE,CR13,FALSE,BZ13,FALSE,B Q13,FALSE,BI13,FALSE,BB13,FALSE)
I appreciate if you could help on this
Thank you

20131119, 13:49 #7
 Join Date
 Jan 2001
 Location
 La Jolla, CA
 Posts
 1,570
 Thanks
 44
 Thanked 73 Times in 69 Posts
You're experiencing a problem many have counting TRUE and FALSE because of the way Excel treats those words. They're treated as functions not as text strings.
This combination statement using wild cards will count the number of TRUEs:
=COUNTIF(A14:L14,"*TRUE")COUNTIF(A14:L14,"*?TRUE")
You can now do a similar thing with the FALSE count and combine the two:
=COUNTIF(A14:L14,"*TRUE")COUNTIF(A14:L14,"*?TRUE")+COUNTIF(A14:L14,"*FALSE" )COUNTIF(A14:L14,"*?FALSE")
P.S. If you (could) change the TRUE/FALSE to 1s and 0s, you could just sum and avoid all this nonsense.Last edited by kweaver; 20131119 at 13:55.

20131119, 13:57 #8
 Join Date
 Nov 2013
 Posts
 8
 Thanks
 2
 Thanked 0 Times in 0 Posts
Dear Kweaver,
thank you for your continued help. I will try this and hope the problem gets solved.
Thank you very much