offset to start from a cell containing 3 specific letters

[toony]

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),"")))

[NBVC]

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?

[toony]

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

[NBVC]

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?

[toony]

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

[NBVC]

In E2, try:

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

copied down.

[toony]

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

[NBVC]

Wow, I never have seen my name spelled that way before .

Do you mean?

=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.

[toony]

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.