Results 1 to 9 of 9

Thread: offset to start from a cell containing 3 specific letters

  1. #1

    offset to start from a cell containing 3 specific letters



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have added the offset function to the if formula which sums weekly figures. Other than starting from the first day of the week, I want the offset to be the first cell on the day that contains 3 letters *sub* which can be in any cell in the week column, but it tells me that I have too many arguments. To be honest I have never used offset before and not sure if there is may be another function that works better to get it to start from the first yes of the week like may be the find function? Much appreciated

    =OFFSET(ISNUMBER(SEARCH("sub",E1916),"YES",IF(A1916="","",IF(COUNTIF(A$2:A1916,A1916)=1,SUMIF($A$2:$A$11999,$A1916,S$2:$S12354),"")))

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,468
    Articles
    0
    Excel Version
    Excel 2016
    I think I somewhat understand what you are looking for...

    You seem to want to find the first cell in column E that contains the string "sub". Then you either want to start there or end there, in order to perform your summation.

    What I don't know is what is in column A that you are counting, and why is your sum range different than your count range in the SUMIF() function and why is the column A range in the SUMIF different than that in the COUNTIF. These don't make sense.

    Can you elaborate?


  3. #3
    I may not be structuring properly. A is the column that contains the dates from January all the way down to today's; E is the column that contains "sub" (Sub doesn't change, and it is part of, ie, "PG (Sub)"); AJ is a column that contains daily codes assigned to different jobs and the formula is to group duplicated codes; finally, S the column with the values to sum up. Hope that this makes it clearer and can help me with it. I think that I may have messed up the structure? I've tried different ways but don't get the results. Thanks

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,468
    Articles
    0
    Excel Version
    Excel 2016
    So you want to sum column S, based on column A? The ranges starts at the first occurrance of "Sub"? Or ends at last occurance of "Sub" in column E?

    Any way you can post a sample of what you've got?


  5. #5
    Hi,

    Basically I need that the column E (daily drops) starts from the first YES up of the day other than the top

    DATE DRIVER SUB? DROPS DAILY DROPS RUN NO
    01/01/2014 Max NO 53
    01/01/2014 Smith (Sub) YES 2 3 54
    01/01/2014 Nortor (Sub) YES 54
    01/01/2014 Custis (Sub) YES 1 55
    02/01/2014 Barlo NO 56
    02/01/2014 Conie (Sub) YES 1 1 57

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,468
    Articles
    0
    Excel Version
    Excel 2016
    In E2, try:

    Code:
    =IF(COUNTIFS(A$2:$A2,$A2,$C$2:$C2,"YES")=1,SUMIFS(D:D,A:A,A2,C:C,"YES"),"")
    copied down.


  7. #7
    Many thanks NVCD. It was perfect. However, how could I make it to start from the bottom first instance instead of the top? Tried different ways but I can't get it right. Thanks for your kind help

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,468
    Articles
    0
    Excel Version
    Excel 2016
    Wow, I never have seen my name spelled that way before .

    Do you mean?

    Code:
    =IF(COUNTIFS(A$2:$A2,$A2,$C$2:$C2,"YES")=COUNTIFS(A:A,$A2,C:C,"Yes"),SUMIFS(D:D,A:A,A2,C:C,"YES"),"")
    copied down.


  9. #9
    Thanks there. Sorry to be a pain, but what I need is actually 2 things;
    1. In column E I need to sum column S, based on column A and also on column G with the 2 digit codes, with the ranges starting at the first occurrence of "Sub"
    2. In column F I need to count E, based on column A and also on column G with the 2 digit codes, with the ranges starting at the first ocurrance of "Sub".
    2. I also would need another formula to do the same as the on in point 2, but with the ranges starting at the last occurrence of "Sub"

    I hope it is clearer now.....though not sure how to call now. Happy you found it funny.

Tags for this Thread

Posting Permissions

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