The sum of data based on words
 

I am working on a spreadsheet that has multiple pages within the workbook. It
is set up so that I can keep track of various information along with data.
What I am trying to do is calculate the sum of the data that is associated
with a word/phrase from two different cells Each sheet within the workbook is
different based on the month, and so some words/phrases may not appear each
month. For instance:


January (Sheet 1) February (Sheet 2)
A1 B1 A1 B1
Name Total Name Total
Pizza 2 Hot Dog 3
Hot Dog 5 Sandwich 2

I am wanting to create a sum total for each item without having to go
through each sheet and individually select the cells for a sum. Because the
cells could change or be different. I want the totals to be calculated in
occurance to the words that they are associated with.

Is there a possible formula to do this?

The sum of data based on words
 

Use a sum of SUMPRODUCTS:

=SUMPRODUCT((Sheet1!A1:A300="pizza")*(Sheet1!B1:B3 00))+SUMPRODUCT((Sheet2!A1:A300="pizza")*(Sheet2!B 1:B300))+SUMPRODUCT((Sheet3!A1:A300="pizza")*(Shee t3!B1:B300))

--
Gary''s Student - gsnu200816


"Flying Ace" wrote:

I am working on a spreadsheet that has multiple pages within the workbook. It
is set up so that I can keep track of various information along with data.
What I am trying to do is calculate the sum of the data that is associated
with a word/phrase from two different cells Each sheet within the workbook is
different based on the month, and so some words/phrases may not appear each
month. For instance:


January (Sheet 1) February (Sheet 2)
A1 B1 A1 B1
Name Total Name Total
Pizza 2 Hot Dog 3
Hot Dog 5 Sandwich 2

I am wanting to create a sum total for each item without having to go
through each sheet and individually select the cells for a sum. Because the
cells could change or be different. I want the totals to be calculated in
occurance to the words that they are associated with.

Is there a possible formula to do this?

The sum of data based on words
 

Enter sheet names in a range of your target sheet, e.g. K1:K12

=SUMPRODUCT(SUMIF(INDIRECT(K1:K12&"!A:A"),"Pizza", INDIRECT(K1:K12&"!
B:B")))

HTH
Kostis Vezerides

On Nov 25, 9:16*pm, Flying Ace <Flying
wrote:
I am working on a spreadsheet that has multiple pages within the workbook.. It
is set up so that I can keep track of various information along with data..
What I am trying to do is calculate the sum of the data that is associated
with a word/phrase from two different cells Each sheet within the workbook is
different based on the month, and so some words/phrases may not appear each
month. For instance:

January (Sheet 1) * * * * * February (Sheet 2)
A1 * * * * *B1 * * * * * * * * * *A1 * * * * * *B1
Name * * Total * * * * * * * * Name * * * Total
Pizza * * * 2 * * * * * * * * * * Hot Dog * *3
Hot Dog * 5 * * * * * * * * * * Sandwich * 2

I am wanting to create a sum total for each item without having to go
through each sheet and individually select the cells for a sum. Because the
cells could change or be different. I want the totals to be calculated in
occurance to the words that they are associated with.

Is there a possible formula to do this?

The sum of data based on words
 

Hi,

You may try Data Consolidate. Please read up on the same in Excel's Help
menu.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Flying Ace" <Flying wrote in message
...
I am working on a spreadsheet that has multiple pages within the workbook.
It
is set up so that I can keep track of various information along with data.
What I am trying to do is calculate the sum of the data that is associated
with a word/phrase from two different cells Each sheet within the workbook
is
different based on the month, and so some words/phrases may not appear
each
month. For instance:


January (Sheet 1) February (Sheet 2)
A1 B1 A1 B1
Name Total Name Total
Pizza 2 Hot Dog 3
Hot Dog 5 Sandwich 2

I am wanting to create a sum total for each item without having to go
through each sheet and individually select the cells for a sum. Because
the
cells could change or be different. I want the totals to be calculated in
occurance to the words that they are associated with.

Is there a possible formula to do this?