Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If two cells have specific text to count as 1 | Excel Worksheet Functions | |||
total cells with text and number EX: GS-5 | Excel Worksheet Functions | |||
How do i total cells in excel that contain specific text? | Excel Discussion (Misc queries) | |||
getting a total for a row of cells where some are text | Excel Discussion (Misc queries) | |||
counting total cells with text | Excel Discussion (Misc queries) |