total of cells that are next to a cell that contains specific text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
Back
Top