Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula Help (XP)

    Jeez I'm glad you're back! That said - help, please.

    See attached - the person who has asked me to make this post wants to know if the formulas can be altered to include the proviso that if any cell in the range C4:AG4 contains the letter "S" that the whole countif business begins AFTER that entry . . . another way to say it (she says to me) is can the formula be written to exclude any days prior to the appearance of that "S".

    Again - sooooooooo glad you're back.

    Aunt Linda
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula Help (XP)

    Hi Linda

    Glad you are back too <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    How about a work around using a hidden row 5 as an idea

    In cell C5 type this formula

    =IF(B4="S",SUM(C$4:$AG$4),"")

    and then copy it across
    Jerry

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formula Help (XP)

    The formula in H2 counts (among other things) the number of occurrences of "S" in C2:AG2 (I don't think the C4:AG4 in your post is correct). Does she want to skip the first occurrence (if any) of "S", or all occurrences? If the latter, the count of "S" will be 0 by definition, so why include it in the formula?

    It might help if you explained what the formula is intended to do. This is absolutely unclear from the workbook you attached.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Help (XP)

    Have decided to erase most of the word "CHUMP" off my forehead so I just forwarded your reply to the person who should be doing this herself. I'll let you know what she says.

    It's been awful not having access to ya'll!

    Thanks

    Later

    Aunt Linda

    PS: Always a pleasure to deal with another Steven Wright fan!

    My friend Sam has one leg. I went to his house. I couldn't go up the stairs.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Help (XP)

    Hi Hans . . . .

    I don't have a clue either - so, as I posted to Jerry - I'll just send your reply along as well and she what she does with it.

    Glad to hear from you again as well!

    Aunt Linda

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Help (XP)

    She says won't work - didn't ask why. Read your profile - may be seeking your assistance in database design . . . you can help??

    Aunt Linda

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Help (XP)

    Asked her - the S represents the date the child began preschool - she doesn't want anything (I guess the preceeding cells aren't really empty) prior to the child starting school to be counted.
    She did say to say "thank you" - I guess she'll try to figure it out.

    Thank you from me, as well.

    And - welcome back.

    Aunt Linda

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Formula Help (XP)

    <P ID="edit" class=small>(Edited by macropod on 14-Sep-07 14:17. Error in offset - replaced '27' with '31')</P>Hi AuntLinda,

    In AH2, try:
    =IF(ISBLANK(B2),"",IF(ISERROR(MATCH("S",$A2:$AG2,0 )),COUNTIF($A2:$AG2,"")+COUNTIF($A2:$AG2,"D")+COUN TIF($A2:$AG2,"E")+COUNTIF($A2:$AG2,"R")+COUNTIF($A 2:$AG2,"U"),COUNTIF(OFFSET(C2,0,MATCH("S",$A2:$AG2 ,0),31-MATCH("S",$A2:$AG2,0)),"")+COUNTIF(OFFSET(C2,0,MAT CH("S",$A2:$AG2,0),31-MATCH("S",$A2:$AG2,0)),"D")+COUNTIF(OFFSET(C2,0,MA TCH("S",$A2:$AG2,0),31-MATCH("S",$A2:$AG2,0)),"E")+COUNTIF(OFFSET(C2,0,MA TCH("S",$A2:$AG2,0),31-MATCH("S",$A2:$AG2,0)),"R")+COUNTIF(OFFSET(C2,0,MA TCH("S",$A2:$AG2,0),31-MATCH("S",$A2:$AG2,0)),"U")))
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Help (XP)

    OMG - that's some formula! Glad it's not my worksheet . . . I've forwarded your reply to the person I'm doing this for (apparently joining the lounge and making her own post was much too difficult) - now the ball, as they say, is in her court.

    Thank you so very much for your help.

    G'day

    Aunt Linda

Posting Permissions

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