Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Invoicing Problem
My Data Table looks like this:
Qty Buyer Seller ChargedSide 2 BOX980 BOX226 Buyer 30 BOX226 BOX917 Seller I am trying to create a new table like this: Charge Credit BOX980 Formula (2 x $50) Formula (0 x $25) BOX917 Formula (30 x 50) Formula (0 x $25) BOX226 Formula (0 x 50) Formula (2x25 + 30x25) So for each customer in ColA, look at the data table, determine whether the charder side is the buyer or seller, and then apply the formula to determine the Charge or Credit. Thank you in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Invoicing Problem
Using the ranges below:
A B C D Qty Buyer Seller ChargedSide <== Row 1 2 BOX980 BOX226 Buyer 30 BOX226 BOX917 Seller 5 BOX980 BOX226 Buyer 10 BOX226 BOX917 Seller A B C Charge Credit <== Row 8 BOX980 350 0 BOX917 2000 0 BOX226 0 1175 in B9: =(SUMPRODUCT(--($B$2:$B$5=$A9),--($D$2:$D$5=$B$1),($A$2:$A$5))+SUMPRODUCT(--($C$2:$C$5=$A9),--($D$2:$D$5=$C$1),($A$2:$A$5)))*50 in C9: =(SUMPRODUCT(--($C$2:$C$5=$A9),--($D$2:$D$5<$C$1)*($A$2:$A$5))+SUMPRODUCT(--($B$2:$B$5=$A9),--($D$2:$D$5<$B$1),($A$2:$A$5)))*25 I have allowed for multiple entries of a "Buyer"/"Seller" HTH "carl" wrote: My Data Table looks like this: Qty Buyer Seller ChargedSide 2 BOX980 BOX226 Buyer 30 BOX226 BOX917 Seller I am trying to create a new table like this: Charge Credit BOX980 Formula (2 x $50) Formula (0 x $25) BOX917 Formula (30 x 50) Formula (0 x $25) BOX226 Formula (0 x 50) Formula (2x25 + 30x25) So for each customer in ColA, look at the data table, determine whether the charder side is the buyer or seller, and then apply the formula to determine the Charge or Credit. Thank you in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Invoicing with Excel | New Users to Excel | |||
need help with invoicing | New Users to Excel | |||
Time and Invoicing | Excel Discussion (Misc queries) | |||
Invoicing | Excel Worksheet Functions | |||
credit invoicing | Excel Discussion (Misc queries) |