Running tally comparing two columns in unsorted list




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

If this post helps click Yes

Jacob Skaria

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


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

Headers as follows (A-F)





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


If this post helps click Yes


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...


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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question