+ Reply to Thread
Results 1 to 15 of 15

COUNTIFS "l" appears in 1 row of data with a few variables

  1. #1
    Registered User
    Join Date
    08-23-2020
    Location
    Adelaide, Australia
    MS-Off Ver
    2016
    Posts
    6

    Unhappy COUNTIFS "l" appears in 1 row of data with a few variables

    I've attached a spreadsheet of my issue.

    There are several variations of outcomes for a patient. I'm wanting to count if an L (lowercase in the worksheet) is present in 1 of 3 columns, if so it counts as 1.

    Example if BAB1 = l & BAB2 = l then that counts as 1, not 2. Basically wanting to count if a patient has an l among 3 columns, if so it = 1.

    The answer should be 7 in the example spreadsheet I have attached.


    Please someone help!
    Attached Files Attached Files
    Last edited by Jatzbiscuits; 08-24-2020 at 12:50 AM. Reason: Admin told me off

  2. #2
    Spammer
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,237

    Re: Can't get my head around a counting issue! Please help!

    Not sure EXACTLY what you are trying to do but =IF(COUNTIF(B2:D2,"=l")>0,1,0) will give you your 1 or 0

    Then you could just sum the column you put that formula in.

  3. #3
    Registered User
    Join Date
    08-23-2020
    Location
    Adelaide, Australia
    MS-Off Ver
    2016
    Posts
    6

    Re: Can't get my head around a counting issue! Please help!

    Thanks croweater.
    I was hoping not to have to use a working column but this helps for the time being!

    Appreciate it!

  4. #4
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,327

    Re: Can't get my head around a counting issue! Please help!

    TRY

    {=SUM(--(MMULT(N(B2:D8="l"),{1;1;1})>0))}

  5. #5
    Registered User
    Join Date
    08-23-2020
    Location
    Adelaide, Australia
    MS-Off Ver
    2016
    Posts
    6

    Re: Can't get my head around a counting issue! Please help!

    Is there a way for me to utilise this formula with criteria as well? Change it to a SUMIFS?

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Can't get my head around a counting issue! Please help!

    Quote Originally Posted by Jatzbiscuits View Post
    ...
    Example if BAB1 = l & BAB2 = l then that counts as 1, not 2. Basically wanting to count if a patient has an l among 3 columns, if so it = 1.
    what about if both BAB2 and BAB3 or BAB1 and BAB3 both have l?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Can't get my head around a counting issue! Please help!

    So, you want to use it to , i.e, multiply with next column, i.e, rate?

    Please Login or Register  to view this content.
    Normal Enter.

    Replace E2:E8 with 1, if you want to count only.
    Attached Images Attached Images
    Quang PT

  8. #8
    Registered User
    Join Date
    08-23-2020
    Location
    Adelaide, Australia
    MS-Off Ver
    2016
    Posts
    6

    Re: Can't get my head around a counting issue! Please help!

    This all correlates with live births. BAB1 = Baby 1, BAB2 = Baby 2 & BAB3 = Baby 3. They are either l (live birth), s (still), n (not live)
    Single preg where 1 baby is born live = 1 live birth event
    Twin preg where either 1 or 2 babies are born live = 1 live birth event.
    Triplet preg where 1, 2 or 3 babies are born live = 1 live birth event.

    So as you can imagine there are a few scenarios for the twin and triplet babies as either the 1st 2nd or 3rd baby could be the only one born live which results in 1 live birth event.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,368

    Re: COUNTIFS "l" appears in 1 row of data with a few variables

    Try this:

    =IF(COUNTIF(B2:D2,"l")>0,1,0)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  10. #10
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,327

    Re: Can't get my head around a counting issue! Please help!

    Quote Originally Posted by bebo021999 View Post
    So, you want to use it to , i.e, multiply with next column, i.e, rate?

    Please Login or Register  to view this content.
    Normal Enter.

    Replace E2:E8 with 1, if you want to count only.
    Your formula can be shortened =SUMPRODUCT((MMULT(N($B$2:$D$8="l"),{1;1;1})>0)*$E$2:$E$8)

  11. #11
    Spammer
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,237

    Re: COUNTIFS "l" appears in 1 row of data with a few variables

    Quote Originally Posted by AliGW View Post
    Try this:

    =IF(COUNTIF(B2:D2,"l")>0,1,0)
    Excellent suggestion. Don't know why I didn't think of it.

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: COUNTIFS "l" appears in 1 row of data with a few variables

    Quote Originally Posted by Croweater View Post
    Excellent suggestion. Don't know why I didn't think of it.
    I take it that was a bit of sarcasm there lol? Seeing as that was pretty much what you suggested in post #2

  13. #13
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: COUNTIFS "l" appears in 1 row of data with a few variables

    Quote Originally Posted by FDibbins View Post
    I take it that was a bit of sarcasm there lol? Seeing as that was pretty much what you suggested in post #2
    Unless it is confirmed by OP
    I guess OP does not want go thru each row, but one formula to get counting 7. That is what wk9128's in #4 and mine in #7 does.

  14. #14
    Spammer
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,237

    Re: COUNTIFS "l" appears in 1 row of data with a few variables

    Quote Originally Posted by bebo021999 View Post
    I guess OP does not want go thru each row, but one formula to get counting 7. That is what wk9128's in #4 and mine in #7 does.
    Really? looks like the formula in #7 not only uses an extra column, but also gives the wrong answer.

  15. #15
    Spammer
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    1,237

    Re: Can't get my head around a counting issue! Please help!

    Quote Originally Posted by wk9128 View Post
    TRY

    {=SUM(--(MMULT(N(B2:D8="l"),{1;1;1})>0))}
    Very neat.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. League Table Based on Head to Head results
    By darkblueblood in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-16-2023, 07:10 AM
  2. Cant get my head around this date issue
    By DexterX in forum Excel General
    Replies: 16
    Last Post: 09-12-2016, 03:15 PM
  3. [SOLVED] Conditional formatting? if 3 teams head to head? Need highlight it
    By micope21 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-09-2016, 04:55 AM
  4. How to account for head to head matchup in Excel with other tie breakers
    By Biogeek1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-26-2013, 05:18 PM
  5. Calculate average of last two outcomes of last two head-to-head clashes
    By wishkey in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-07-2013, 08:17 AM
  6. Ranking a league table by head to head results
    By pajc72 in forum Excel General
    Replies: 1
    Last Post: 09-02-2011, 07:02 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1