1. ## Formula Help (XP)

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

2. ## 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

3. ## 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. ## 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.

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. ## 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. ## 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. ## 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. ## 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")))

9. ## 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
•