• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how to print (find) negative amount in column E

illias

Member
ABCDE
1Jan-19Feb-19Mar-19Apr-19Print Negative amount only
2306.85339.72328.77(19,517.81)
3(3,637.60)
4(11,813.22)
5(17,368.15)
6126.58140.14(14,644.32)
7165.93183.71(12,358.73)
8169.92188.12(20,842.24)
9421.54466.69(23,876.90)
10887.64982.74(56,639.78)
111,396.701,546.35(57,564.05)
12383.57(4,342.47)
13472.58(27,033.06)
14(20,107.97)
15(17,730.11)
16(25,178.63)
17(18,354.52)
18(18,205.60)
19(63,646.46)
20(22,085.59)
 
Just using IF worksheet function within a formula, at very beginner level just reading Excel help …​
 
Slightly more complicated
= SUMIFS( Table1[@], Table1[@], "<0" )
Any difference would only be apparent if there were either no negative values or multiple negative values.

Excel is still very limited when it comes to handling 2D arrays though; still stuck with single cell thinking!
= MMULT( IF(Table1<0, Table1, 0 ), {1;1;1;1} )
gives a 'proper' array result but is hardly the world's formula of choice.
= SEQUENCE( n, 1, 1, 0 )
will replace the array constant in time.
 
Back
Top