| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
matching dupliclates and then looking up the corresponding amounts
I need to find the sum of an item for the month. For one month there are mutiple invoices. Each invoice can have the same item (In this example it will be a black goggle). On invoice #1 there is a black goggle and on invoice #2 there is a black goggle. A "count if" formula would result in counting 2 black goggles. Here is the dilema. invoice #1 has a black goggle, but it is for a quantity of 4 and on invoice #2 there is a black goggle but for the quantity of 3. So by doing the "count if" i get 2 black goggles and a "vlookup" for the black goggle would tell me that i have 4 goggles because it reads the first black goggle on on the list of data which is invoice #1. What i would like to create is a formula or macro, if needed, that would count up every black goggle item in the month and match them with the corresponding quantity. In my example the correct amount i want to find is 7 goggles. It counts the black goggles in invoice #1 as 4 and counts the black goggles in invoice #2 as 3, resulting in seven. below is a screen shot of what my data looks like. Invoice Item Unit Qty 1125 *TDM-SF-121 22.00 * 1126 ATT-PR-003 1.00 TDM-PIFD-121 1.00 TDS-MTW-126 1.00 TDS-MTBK-123 1.00 *TDM-SF-121 1.00 * TDS-PI-125 1.00 TSN-BLKM 1.00 BEANIE-BLK 1.00 TDM-CF-121 1.00 So TDM-SF-121 reads twice in the data dump, so i would like to create something that accounts for the duplicate item number and then accounts for the 23 ordered that month. -- Dklunchoo ------------------------------------------------------------------------ Dklunchoo's Profile: http://www.excelforum.com/member.php...o&userid=27215 View this thread: http://www.excelforum.com/showthread...hreadid=467266 |
|
#2
|
|||
|
|||
|
Just like =countif(), there's an =sumif() that sums a range.
=sumif(sheet2!b:b,"tdm-sf-121",sheet2!c:c) or =sumif(sheet2!b:b,A1,sheet2!c:c) If A1 contained that code. Dklunchoo wrote: I need to find the sum of an item for the month. For one month there are mutiple invoices. Each invoice can have the same item (In this example it will be a black goggle). On invoice #1 there is a black goggle and on invoice #2 there is a black goggle. A "count if" formula would result in counting 2 black goggles. Here is the dilema. invoice #1 has a black goggle, but it is for a quantity of 4 and on invoice #2 there is a black goggle but for the quantity of 3. So by doing the "count if" i get 2 black goggles and a "vlookup" for the black goggle would tell me that i have 4 goggles because it reads the first black goggle on on the list of data which is invoice #1. What i would like to create is a formula or macro, if needed, that would count up every black goggle item in the month and match them with the corresponding quantity. In my example the correct amount i want to find is 7 goggles. It counts the black goggles in invoice #1 as 4 and counts the black goggles in invoice #2 as 3, resulting in seven. below is a screen shot of what my data looks like. Invoice Item Unit Qty 1125 *TDM-SF-121 22.00 * 1126 ATT-PR-003 1.00 TDM-PIFD-121 1.00 TDS-MTW-126 1.00 TDS-MTBK-123 1.00 *TDM-SF-121 1.00 * TDS-PI-125 1.00 TSN-BLKM 1.00 BEANIE-BLK 1.00 TDM-CF-121 1.00 So TDM-SF-121 reads twice in the data dump, so i would like to create something that accounts for the duplicate item number and then accounts for the 23 ordered that month. -- Dklunchoo ------------------------------------------------------------------------ Dklunchoo's Profile: http://www.excelforum.com/member.php...o&userid=27215 View this thread: http://www.excelforum.com/showthread...hreadid=467266 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
