Forum Discussion
Excel Formula Help: Running Count in a Target Cell
I have 3 columns that I am working with a Date (A:A), requested (B:B), and, shipped (C:C). The problem I am running into is the following: I'd like to add the sum of column B and subtract the sum of column C. However, there are times when items are shipped (C) without being requested (B). That puts the target cell (H2) at a negative. Then, when a request does come in, the target cell becomes inaccurate because its adding from a negative number.
Current Formula: =SUM(B:B)-SUM(C:C)
Example of the Problem: B1 is 10 and C1 is 12. Target cell displays -2. Request for 10 more come in B2 is 10 and nothing in C2. Target cell displays 8. I need the target cell to display 10 because that is the running number of items requested.
Is this even possible or should I find a workaround?
3 Replies
- try (assuming you have excel 365:
SUM((B:B-C:C)*(B:B>C:C))- Thanks for the reply. Unfortunately, I am on Professional Plus 2016 (work computer and account). The above formula does not seem to be working for me.
- try replacing with SUMPRODUCT:
SUMPRODUCT((B:B-C:C)*(B:B>C:C))
alternatively try using CSE (ctrl-shift-enter when you enter the formula)
