My excel version doesn't have the Filter function...unfortunatelyDanilão
Please, modify Your thread's title as written in Forum Rules.
=FILTER(CHOOSE({1,2},A1:A52,D1:D52),LEFT(B1:B52,7) ="DEV TED")
In this case, if there is a DEV TED, it must add it up, giving the result $666,121.00.The only reason that row 44 is not included in the calculation is because its value in column D is not <0.
View attachment 87828
If it were -94000 (less than 0) it would make the calculation go the wrong way (it would show -R$ 854,121.00)
In words, what is this formula trying to do?
ps. Note that the MMULT part of your formula EXCLUDES rows which contain any of "TAR","01-*","INT*"
p45cal...Shoooowwwww !!!!try:
Code:=SUMPRODUCT(($A$2:$A$1544=F6)*($D$2:$D$1544<0)*(1-(MMULT(--(ISNUMBER(SEARCH({"TAR","01-*","INT*"},$B$2:$B$1544))=TRUE),COLUMN($B$2:$B$1544)+{0;0;0})>0)),$D$2:$D$1544)+SUMPRODUCT($D$2:$D$1544,ISNUMBER(SEARCH("DEV TED",$B$2:$B$1544))*($A$2:$A$1544=F6))