Hi all,
I'm working on a project at work, that looks at the movement of stock in and out of a warehouse. I'd only like to count a Pallet ID if the sum of its movements equals zero, else a zero must be shown. See below for an example:
Pallet No Movement Calculation 14555 -10 0 14555 20 0 14555 20 0 14555 -30 1 14777 10 0 14777 -10 1
Any ideas on how this would be achieved? Any help would be greatly appreciated.
Maybe this...
Data Range
This formula entered in C2 and copied down:
=--(SUMIF(A$2:A2,A2,B$2:B2)=0)
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Drag it down to C7. It'll look neater if you sort Column A
(copy pasta from Ford)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
Regards,
Lem
That worked perfectly Tony, thanks!
Could you please give me some detail into why that worked? I'm still a little new here.
The formula does a progressive SUMIF on each row of data.
SUMIF(A$2:A2,A2,B$2:B2)=0 returns either TRUE or FALSE.
Then the double unary minus -- converts that to 1 or 0.
--(SUMIF(A$2:A2,A2,B$2:B2)=0)
--TRUE = 1
--FALSE = 0
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks