Results 1 to 8 of 8
  1. #1
    New Lounger
    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,

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 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")

  3. #3
    New Lounger
    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,

  4. #4
    New Lounger
    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,

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 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; 2013-11-19 at 10:38.

  6. #6
    New Lounger
    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

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 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; 2013-11-19 at 13:55.

  8. #8
    New Lounger
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •