I need some help

[PFS12]

Branch	Sell Price	Qty
2	772.50	1
2	750	-1
2	4995	1

<tbody>
</tbody>

I need a formula that will sort a group of numbers based on the branch, and then total them based on the quantity. The problem is I need it to say 5767.50 and it reads 5017.50 when using a SUMPRODUCT array. Basically what I am doing is tracking inventory, but there are returns once in a while. I need to just minus the dollar values, not actually do the equation that adds a negative value, when it does the equation it seems to double the negative number and screws up my Total. I know this is vague but I am hoping to at least get a conversation going about it. Thanks in advanced!

 

[Weazel]

I'm not sure I fully understand what you mean but maybe something like....

Excel 2012

	A	B	C
1	Branch	Sell Price	Qty
2	2	772.5	1
3	2	750	-1
4	2	4995	1
5
6		5767.5

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet7​

Worksheet Formulas Cell Formula B6 =SUMPRODUCT((A2:A4=2)*(C2:C4>0),B2:B4) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[PFS12]

Close, but I can't ignore the value of B. I am bringing it back in as an "in house" charge, so it does have to reflect a dollar amount being taken off the total I just can't ignore it. Almost like I have to turn the dollar amount that has a negative quantity into a negative dollar amount. And also I need to be able to adjust for the quantity, so if the value of the part is 2 dollars for one and I sell 2 of them the number that gets totaled is 4. I think I have been staring at it so long I can't explain it very well sorry!

 

[PFS12]

OK so I still need help on this, I have been on this all day and just when I thought I had it, I reconstructed my whole table, and then found out that I am not referencing the actual value of the quantity. So if I have sell price 750 and I have (QTY) 2 it should be 1500. the problem still lies with these FREAKIN negative numbers, for what ever reason this sheet either doubles the value of the loss so if the above were -2 qty it would subtract 3000 from my totals...I don't know why! I need to be able to have my totals correct, multiply by the correct number and account for a sale or a return. In this case the returns are labeled in a (-) value on my tables. So it is a return to inventory. Please see below. I would like to Total the Actual inventory dollars, factoring in QTY, and sorting by BR (Branch) This is the formula that I am using but it doesn't factor in quantity. =SUMIFS($L$14:$L$2009,$C$14:$C$2009,"2",$K$14:$K$2009,">0") where L is actual, C is branch, and K is QTY. Doing it with the formula above doesn't calculate correctly over time. I don't know I have stared at this thing for so long I forgot why it isn't working again. can I replace the ">0" for something that will pay attention to the value of the QTY and multiply accordingly?

Br	Vendor / Invoice #:	Description:	Date:	Average Cost	Price:	Order Status:	Sell Price:	Qty:	Actual Inventory Dollars Values:
2	P19543	SMS BASIC	20140113	$632.27	$765.00	N	$772.65	1	$772.65
2	W01930	UPGRADE	20140116	$577.50	$750.00		$750.00	1	$750.00
2	W01930	KIT ?	20140116	$3,846.15	$4,995.00		$4,995.00	1	$4,995.00
2	W01930	KIT	20140116	$1,151.15	$1,495.00		$1,495.00	1	$1,495.00
2	W01930	KIT	20140116	$920.15	$1,195.00		$1,195.00	1	$1,195.00
2	W01930	BRIDGE-M, VER	20140116	$1,450.00	$2,175.00		$2,150.00	1	$2,150.00
2	W01930	KIT ?	20140123	$3,846.15	$4,995.00	N	$4,995.00	1	$4,995.00
2	W01793	AFS PARTS & PAC	20140121	$111.65	$145.00		$146.45	-1	-$146.45

<COLGROUP><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1243" width=34><COL style="WIDTH: 125pt; mso-width-source: userset; mso-width-alt: 6107" width=167><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5376" width=147><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3693" width=101><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4498" width=123><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3474" width=95><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 134pt; mso-width-source: userset; mso-width-alt: 6546" width=179><TBODY>
</TBODY>