Thanks a lot wickedchew !! Am sorry for reposting earlier...I was desperately seeking some help. your formula works great..However when I try to use it in my sheet for some strange reason am getting value error ! I tried your formula in a seperate sample sheet and it works great..Problem comes when i try using it in my original sheet :(
this is the formula am using : Please let me know if you can find any errors in this !
=SUMPRODUCT(--(OFFSET($D$3,0,0,COUNTA($D:$D)-1,1)=$A$87),--(OFFSET($D$3,0,0,COUNTA($D:$D)-1,1)<=$A$88),--(OFFSET($G$3,0,0,COUNTA($G:$G)-1,1)=$A$89))
Assuming am using column D for dates and G for priorities and entering inputs in A87,A88 and A89. I verified using the color code and nothing seems to be wrong ! but when i hit enter it gives value error exclamation in the answer cell.
Quote:
Originally Posted by wickedchew
Yes! That is possible.
In this picture, cell E5 contains this formula:
=SUMPRODUCT(--(OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)=$E$1),--(OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)<=$E$2),--(OFFSET($B$2,0,0,COUNTA($B:$B)-1,1)=$E$3))
This would make your search criteria dynamic.
NOTE:
You need not to do a double post.
|