Try,
=SUMPRODUCT((0+LEFT(Sheet1!B2:B27,FIND(" ",Sheet1!B2:B27)-1)>7)* Sheet1!D2:D27)
Or,
=SUMPRODUCT((0+LEFT(Sheet1!B2:B27,FIND(" ",Sheet1!B2:B27)-1)>30)* Sheet1!D2:D27)
Try,
=SUMPRODUCT((0+LEFT(Sheet1!B2:B27,FIND(" ",Sheet1!B2:B27)-1)>7)* Sheet1!D2:D27)
Or,
=SUMPRODUCT((0+LEFT(Sheet1!B2:B27,FIND(" ",Sheet1!B2:B27)-1)>30)* Sheet1!D2:D27)
If the Date column is dragged down, this formula will extract the last cell content. In fact, I want the date value in the same row.
Or, try to use INDIRECT function :
=INDIRECT("JCB!B"&MATCH(9^9,OFFSET(INDEX(JCB!1:1,INDEX(MATCH(Display!$I$3&Summary!$M$8&Summary!$M$6,JCB!1:1&JCB!2:2&JCB!3:3,0),0)),,,60000)))
.. to extract one more value from the second column cell (B1134)..........................
To extract B1134 date value in Offset from the cell number AP1134 :
=LOOKUP(9^9,OFFSET(INDEX(JCB!1:1,INDEX(MATCH(Display!$I$3&Summary!$M$8&Summary!$M$6,JCB!1:1&JCB!2:2&JCB!3:3,0),0)),,2-INDEX(MATCH(Display!$I$3&Summary!$M$8&Summary!$M$6,JCB!1:1&JCB!2:2&JCB!3:3,0),0),60000))
This is a 2 ways Lookup, using Index + Match function
Try to use this modified formula instead :
=INDEX(source!$B$4:$GZ$521,MATCH($A15,source!$A$4:$A$521,0),MATCH(1,INDEX((L$14=source!$B$3:$GZ$3)*(L$13=source!$B$2:$GZ$2),0),0))
Regards
Try formula solution ,
1] In "Sheet1" E4, formula copied right to F4 and all copied down :
=IFERROR(INDEX(A$4:A$24,AGGREGATE(15,6,ROW($A$1:$A$21)/((TEXT($A$3:$A$23,"0;;0;\0")="0")),ROW($A1))),"")
2] In "Sheet1" G4, array (confirm pressing with Ctrl+Shift+Enter 3 keystrokes altogether) formula copied down :
=IF(F4="","",TEXTJOIN(", ",,IF(Table1_2[Column2]=F4,Table1_2[Column3],"")))
Try to use Index+Match function to return the first entry date, as in :
=IFERROR(INDEX($B$1:$IM$1,MATCH(TRUE,INDEX(B2:IM2<>"",0),0)),"")
Regards
Try,
1] "RF Last 5 Games", in cell J2 CSE formula copied down :
=AVERAGE(INDEX(IF($A$2:$A$866=I2,$C$2:$C$866,0)+IF($B$2:$B$866=I2,$D$2:$D$866,0),N(IF(1,AGGREGATE(14,6,ROW(A$2:A$866)-ROW(A$1)/(IF($A$2:$A$866=I2,$C$2:$C$866,0)+IF($B$2:$B$866=I2,$D$2:$D$866,0)),ROW($A$1:$A$5))))))
2] "RA Last 5 Games", in cell K2 CSE formula copied down :
=AVERAGE(INDEX(IF($A$2:$A$866=I2,$D$2:$D$866,0)+IF($B$2:$B$866=I2,$C$2:$C$866,0),N(IF(1,AGGREGATE(14,6,ROW(A$2:A$866)-ROW(A$1)/(IF($A$2:$A$866=I2,$D$2:$D$866,0)+IF($B$2:$B$866=I2,$C$2:$C$866,0)),ROW($A$1:$A$5))))))
3] "RF Last 10 Games", in cell L2 CSE formula copied down :
=AVERAGE(INDEX(IF($A$2:$A$866=I2,$C$2:$C$866,0)+IF($B$2:$B$866=I2,$D$2:$D$866,0),N(IF(1,AGGREGATE(14,6,ROW(A$2:A$866)-ROW(A$1)/(IF($A$2:$A$866=I2,$C$2:$C$866,0)+IF($B$2:$B$866=I2,$D$2:$D$866,0)),ROW($A$1:$A$10))))))
4] "RA Last10 Games", in cell M2 CSE formula copied down :
=AVERAGE(INDEX(IF($A$2:$A$866=I2,$D$2:$D$866,0)+IF($B$2:$B$866=I2,$C$2:$C$866,0),N(IF(1,AGGREGATE(14,6,ROW(A$2:A$866)-ROW(A$1)/(IF($A$2:$A$866=I2,$D$2:$D$866,0)+IF($B$2:$B$866=I2,$C$2:$C$866,0)),ROW($A$1:$A$10))))))
Attached file :
One last question.....if I have only monthly totals instead of daily how would my formula change. I used the existing one and it returned nothing.
Attached file for example.....
Palchy
Since your header range is changed to B1:N1, the Offset 5th argument must changed to 13 in match with the size of range,
So,
The D6 formula changed to >>
=IFERROR(1/(1/ROUND(AVERAGEIFS(OFFSET($B$1,MATCH($C6,$A$2:$A$3,0),,,13),$B$1:$N$1,">="&A$6,$B$1:$N$1,"<="&B$6),1)),"")
Very Perfect ...Thnx
one more quick question ......How can I get offset value....
e.g. This formula is extracting value from the cell number AP1134... how can i get it from the third offset column
that is cell number AR1134
Just add 2 in the OFFSET 3rd argument, and the formula become >>
=LOOKUP(9^9,OFFSET(INDEX(JCB!1:1,INDEX(MATCH(Display!$I$3&Summary!$M$8&Summary!$M$6,JCB!1:1&JCB!2:2&JCB!3:3,0),0)),,2,60000))
Based on your attached file.
The revised formula :
=LOOKUP(9^9,OFFSET(INDEX(JCB!1:1,INDEX(MATCH(Display!$I$3&Summary!$M$8&Summary!$M$6,JCB!1:1&JCB!2:2&JCB!3:3,0),0)),,,60000))
The above formula return the position of the desire column
and this formula return the last non-empty cell value in the range of JCB!$C$5:$CB$5 :
=LOOKUP(9^9,JCB!$C$5:$CB$5/(Display!$I$3&Summary!$M$8&Summary!$M$6=JCB!$C$1:$CB$1&JCB!$C$2:$CB$2&JCB!$C$3:$CB$3))
Regards
Maybe try,
=LOOKUP(9^9,COLUMN(JCB!$C$1:$CB$1)-COLUMN(JCB!$C$1)+1/(Display!$I$3&Summary!$M$8&Summary!$M$6=JCB!$C$1:$CB$1&JCB!$C$2:$CB$2&JCB!$C$3:$CB$3))
Change your D16 formula >>
From this :
=ROUND(AVERAGEIFS($C$2:$OG$2,$C$1:$OG$1,">="&A16,$C$1:$OG$1,"<="&B16),1)
Into this :
=IFERROR(1/(1/ROUND(AVERAGEIFS(OFFSET($C$1,MATCH($C16,$B$2:$B$7,0),,,395),$C$1:$OG$1,">="&A$16,$C$1:$OG$1,"<="&B$16),1)),"")
Then,
copied to I16, and all copied down
Regards