# Running tally comparing two columns in unsorted list

O

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

J

#### Jacob Skaria

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

=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&""))

If this post helps click Yes

J

O

#### OperationsNETTC15

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

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

Regards,

OperationsNettc15

O

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

J

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

O

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

J

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