Running tally comparing two columns in unsorted list

[OperationsNETTC15]

Hi,

I am trying to count unique inventory transactions in order to get a running
value for Quantity on Hand for each item # we stock.

I have a list sorted by date, old to new, but I am comparing item #'s which
are unsorted, in order to find a running tally of QOH(Quantity on Hand). A is
items, B is store #, C is transaction type, D is julian date, E is
transaction qty. I want F to be my running tally.

Col A Col B Col C Col D Col E Col F

40321 5 7 039808 2 (formula I am working
on)
55321 3 T 039808 4
66353 7 F 039809 2
40321 5 T 039820 3
40321 3 9 039821 5

If I sort by item #, my formula is easy, because I'll just add values row by
row, adjacently....but now that my item #'s in column A can be non-adjacent,
I need a condition that says "if item # is 40321, then depending on the
transaction type (+ or -), and store #, then update total quantity on hand.

My formula currently is:

=IF($B2=5, IF(OR($K2="7", $K2="T", $K2="9"), 0+$E2+$F1, 0-$E2+$F1), 0+$F1)

Which works fine, but is not smart enough to distinguish between item #'s...

Right now it will return a running Tally of 5 for $F4, but if I change $A4
to any other item number, it stills says 5 when it should be 2.

I cannot figure this out if the item #'s are unsorted, suggestions?

 

[Jacob Skaria]

To get the number of unique entries within A2:A10 use the below formula

Adjust the range to suit your requirement
=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&""))

If this post helps click Yes

 

[Jacob Skaria]

Oops' please ignore the below post..Suggest you to put headers to your table...

 

[OperationsNETTC15]

Hahah I stumped a Silver Badge! J/K thanks for the replies!

Headers as follows (A-F)

HITEM, HSTK, HRX, HDAT, HQTY, Running Tally

Regards,

OperationsNettc15

 

[OperationsNETTC15]

FYI, I turned off calculated column so that my top row does not return a
#VALUE. The formula for my top row is slightly different in that I do not
add the line above it(since it is the header).

 

[Jacob Skaria]

You should be able to do that with SUMIF() but really sorry; I'm struggling
to understand the entries in Transaction Type column (7,F,T) and when you
mention "depending on the transaction type (+ or -)"

=SUMIF(A:A,A2,E:E)

If this post helps click Yes

 

[OperationsNETTC15]

7 = Inventory Receipt, so add QTY to running tally (+)
T = Transfer from another store , add QTY to running tally (+)
F = Transfer to another store, subtract QTY from running tally (-)

I tried the SUMIF you gave me, but I suspect it must be nested somehow.

 

[Jacob Skaria]

In F2 I have applied the below formula and copied that down...

=SUMPRODUCT(--($A$2:A2=A2),$E$2:E2)-SUMPRODUCT(--($A$2:A2=A2),--($C$2:C2="F"),$E$2:E2)

Now what this formula does is count number of items (all types) until that
date and deduct the same items under with type "F". Again I am not sure what
the type 9 is...I assume that is to be added to the running total. If so the
above formula would suit your requirement ; or else adjust to suit...Try and
feedback

Items Sote# Type Date Qty Running Total
40321 5 7 39808 2 2
55321 3 T 39808 4 4
66353 7 F 39809 2 0
40321 5 T 39820 3 5
40321 3 9 39821 5 10


If this post helps click Yes