Danilão asks something about Bank Statement

[Danilão]

Good morning, everyone
I need help to include in this calculation the value of the following word : "DEV TED*" which is in Column B

My excel version : 2013

 

[vletm]

Danilã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")

 

[Danilão]

Danilã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")

My excel version doesn't have the Filter function...unfortunately

 

[vletm]

Danilão​

Okay, but then You should focus at least Your given Title.
Ps. Filtering could do manually too.

 

[p45cal]

The only reason that row 44 is not included in the calculation is because its value in column D is not <0.

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*"

 

[Danilão]

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*"

In this case, if there is a DEV TED, it must add it up, giving the result $666,121.00.
As for MMult, it does not count the values for the 3 registered texts

 

[p45cal]

try:

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

 

[Danilão]

try:

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

p45cal...Shoooowwwww !!!!
Formula worked perfectly.
Thank you very much!!!