Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting Based of Cells Based on Data Entry in anoth | Excel Discussion (Misc queries) | |||
Delete Rows Based on Words | Excel Discussion (Misc queries) | |||
split string based on a SET of words | Excel Discussion (Misc queries) | |||
Sumproduct based which also weights data based on date | Excel Worksheet Functions | |||
how do i insert words into a column without erasing the words | Excel Discussion (Misc queries) |