I have a SUMIF statement that adds and subracts positive and negative numbers and works great for keeping a running total of what I have on hand. My statement is below.
=SUMIF(Processing!D:D,Inventory!E2,Processing!B:B)
Can I somehow make it where it only adds the positive numbers in the "B" column based on the other criteria?
Last edited by cjwanat; 03-10-2010 at 04:24 PM.
You mean you want same summing but only when column D is greater than 0?
Try
=SUMPRODUCT(--(Processing!$D$1:$D$100=Inventory!E2),--(Processing!$D$1:$D$100>0),Processing!$B$1:$B$100)
note: In XL2003 and earlier you cannot use whole column references with this formula.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Not quite...
My inventory sheet "E2" is a model name. lets call it "screw-x".and I ordered 1000 of them. "E3" on my inventory list is say "screw-y" and I ordered 1000 of them also.
Now on my processing sheet column "D" I also have model names of items coming and going and in column "B" I have the quantity of those items.for instance on my processing sheet on 3/3/2010 I recieve 400(in column "B") "screw-x'(in cloumn "D")parts and on 3/4/2010 I recieve another 400(in column "B") "screw-x"(in column "D")parts and on 3/5/2010 I shipped -800(in column "B") "screw-x"(in column "D")parts.
Using my SUMIF statement =SUMIF(Processing!D:D,Inventory!E4,Processing!B:B) I will show to have 0 on hand in my "on hand " column because they are all gone. So I need a way to only add up the positive numbers in the "B" column so I can show that 800 have come in with zero on hand and 200 left to recieve.
I hope that explains it a little better.
Similar Sumproduct statement:
=SUMPRODUCT(--(Processing!$D$1:$D$100=Inventory!E2),--(Processing!$B$1:$B$100>0),Processing!$B$1:$B$100)
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
That equation only returns "#N/A" in the cell
Are there any #N/A in the ranges?
Maybe post a downscaled sample file...
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
In my attachment you can see that the "QTY ON HAND" works just fine but I need a way to only add the positive numbers in the "QTY RECEIVED" column. This will also help catch the double ships and over shipped items.
I entered this formula:
=SUMPRODUCT(--(Processing!$C$1:$C$100=Inventory!A2),--(Processing!$A$1:$A$100>0),Processing!$A$1:$A$100)
and copied down.
You might want to adjust the 100's in the formula to what might be the largest number of records you will ever see in Processing...
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
I see my problem from the last time I entered that formula into my main spreadsheet and you asked if I had any cells populated with #N/A. I have a Vlookup table in column C that returns a #N/A until another cell populates and I use an INDIRECT formula in conditional formating to make the font white in column "C" until it populates .
Can you make your formula not look at or calculate the #N/A's in column "C"?
Change the Vlookup() formula to return a blank instead of #N/A
=IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...))
replace the VLookups with your Vlookup formulas..
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Thanks that got it!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks