total of cells that are next to a cell that contains specific text
Exporting a batch from a database into Excel daily. Batch contains money
posted to certain line item numbers. There are multiple rows of identical text (line item numbers) with a dollar amount in the cell next to it. There are also a number of different line items numbers per sheet. I need to create a formula on another sheet in the workbook that will run a sum total of all the money associated with text that contains the same characters. For example: Line item = Gifts 04325 cell next to line item = $100 another cell contains "Gifts 04325" cell next to line item = $50 another cell contains "Gifts 04325" cell next to line item = $75 A cell on another sheet contains a formula that finds all cells containing text "Gifts 04325" and totals the cell next to each of them. In this example the number that displays in the cell is $225. On the same sheet: Line item = Pledges 10335 cell next to line item = $1000 another cell contains "Pledges 10335" cell next to line item = $500 another cell contains "Pledges 10335" cell next to line item = $750 A cell on another sheet contains a formula that finds all cells containing text "Pledges 10335" and totals the cell next to each of them. In this example the number that displays in the cell is $2250. Can anyone help? |
total of cells that are next to a cell that contains specific text
use SUMPRODUCT:
Sheet1 columns A & B contain line items and amounts. On Sheet2 column A is list of line items e.g A2 contains "Gifts 04325" , so in B2 put: =SUMPRODUCT((Sheet1!A2:A100=A2)*(Sheet1!B2:B100)) Copy down as required. HTH "suziQ" wrote: Exporting a batch from a database into Excel daily. Batch contains money posted to certain line item numbers. There are multiple rows of identical text (line item numbers) with a dollar amount in the cell next to it. There are also a number of different line items numbers per sheet. I need to create a formula on another sheet in the workbook that will run a sum total of all the money associated with text that contains the same characters. For example: Line item = Gifts 04325 cell next to line item = $100 another cell contains "Gifts 04325" cell next to line item = $50 another cell contains "Gifts 04325" cell next to line item = $75 A cell on another sheet contains a formula that finds all cells containing text "Gifts 04325" and totals the cell next to each of them. In this example the number that displays in the cell is $225. On the same sheet: Line item = Pledges 10335 cell next to line item = $1000 another cell contains "Pledges 10335" cell next to line item = $500 another cell contains "Pledges 10335" cell next to line item = $750 A cell on another sheet contains a formula that finds all cells containing text "Pledges 10335" and totals the cell next to each of them. In this example the number that displays in the cell is $2250. Can anyone help? |
All times are GMT +1. The time now is 03:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com