Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking up information based on Columns and Rows
I have a spread sheet that looks like this
CO. Month Amt Apple 11 $5000 Apple 12 3000 Orange 11 500 Orange 11 300 Apple 11 50 Peach 11 6000 Apple 11 150 In a differnet spreadsheet, I want to look up each company based on the month it is found in and sum the amounts given. Example November Spreadsheet Co. Total Apple 5200 Orange 800 Peach 6000 December Spreadsheet Co. Total Apple 3000 Orange - Peach - This will continue throughout the 12 months. the first spreadsheet is the main one where information will be inputed. I need the formula to not only look at the month but who the company is as well. I used to SUMIF function when i was looking at just the month in general but now I need to add to the formula so it will only add those rows that have the correct company attached. Help will be much appreciated. Thanks, Sally |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking up information based on Columns and Rows
Assumptions:
Sheet1A1:C1 contains the column headers/labels for your source data Sheet1!A2:C8 contains your data for your source data Sheet2!A1:B1 contains the column headers/labels for your November results table Sheet2!A2:A4 contains the company name for your November results table Formula: Sheet2!B2, copied down: =SUMPRODUCT(--(Sheet1!$A$2:$A$8=A2),--(Sheet1!$B$2:$B$8=11),Sheet1!$C$2:$ C$8) Hope this helps! In article , "Sally J" wrote: I have a spread sheet that looks like this CO. Month Amt Apple 11 $5000 Apple 12 3000 Orange 11 500 Orange 11 300 Apple 11 50 Peach 11 6000 Apple 11 150 In a differnet spreadsheet, I want to look up each company based on the month it is found in and sum the amounts given. Example November Spreadsheet Co. Total Apple 5200 Orange 800 Peach 6000 December Spreadsheet Co. Total Apple 3000 Orange - Peach - This will continue throughout the 12 months. the first spreadsheet is the main one where information will be inputed. I need the formula to not only look at the month but who the company is as well. I used to SUMIF function when i was looking at just the month in general but now I need to add to the formula so it will only add those rows that have the correct company attached. Help will be much appreciated. Thanks, Sally |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking up information based on Columns and Rows
The data is in Sheet1 in A2:C101, the summary in Sheet2
In Sheet2 to sum Amts for Apples in November =SUMPRODUCT(--(Sheet1!A2:A101="Apple"),--(Sheet1!B2:B101=11),C2:C101) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Sally J" wrote in message ... I have a spread sheet that looks like this CO. Month Amt Apple 11 $5000 Apple 12 3000 Orange 11 500 Orange 11 300 Apple 11 50 Peach 11 6000 Apple 11 150 In a differnet spreadsheet, I want to look up each company based on the month it is found in and sum the amounts given. Example November Spreadsheet Co. Total Apple 5200 Orange 800 Peach 6000 December Spreadsheet Co. Total Apple 3000 Orange - Peach - This will continue throughout the 12 months. the first spreadsheet is the main one where information will be inputed. I need the formula to not only look at the month but who the company is as well. I used to SUMIF function when i was looking at just the month in general but now I need to add to the formula so it will only add those rows that have the correct company attached. Help will be much appreciated. Thanks, Sally |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking up information based on Columns and Rows
=SUMPRODUCT("'Sheet 1'!$A$8:$A$9000='Sheet 1'!A8","'Sheet
1'!$F$8:$F$9000=11,'Sheet 1'!$AJ$8:$AJ$9000") I have this entered into sheet 2. This return Value in the cell. I am not sure how far this information will go down hence the 9000 cell reference. I know this is probably a user error becuase I don't use SUMPRODUCT that often. So what am i doning wrong? "Domenic" wrote: Assumptions: Sheet1A1:C1 contains the column headers/labels for your source data Sheet1!A2:C8 contains your data for your source data Sheet2!A1:B1 contains the column headers/labels for your November results table Sheet2!A2:A4 contains the company name for your November results table Formula: Sheet2!B2, copied down: =SUMPRODUCT(--(Sheet1!$A$2:$A$8=A2),--(Sheet1!$B$2:$B$8=11),Sheet1!$C$2:$ C$8) Hope this helps! In article , "Sally J" wrote: I have a spread sheet that looks like this CO. Month Amt Apple 11 $5000 Apple 12 3000 Orange 11 500 Orange 11 300 Apple 11 50 Peach 11 6000 Apple 11 150 In a differnet spreadsheet, I want to look up each company based on the month it is found in and sum the amounts given. Example November Spreadsheet Co. Total Apple 5200 Orange 800 Peach 6000 December Spreadsheet Co. Total Apple 3000 Orange - Peach - This will continue throughout the 12 months. the first spreadsheet is the main one where information will be inputed. I need the formula to not only look at the month but who the company is as well. I used to SUMIF function when i was looking at just the month in general but now I need to add to the formula so it will only add those rows that have the correct company attached. Help will be much appreciated. Thanks, Sally |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking up information based on Columns and Rows
Sally - What I think you want to do is what "PIVOT TABLES" are made to accomplish. I have attached a small spreadsheet with a generated pivot table in it. You might have to do a little "trial and error" and review of "PIVOT TABLES" to get the results you want - but they can do a lot of things. Here is what the results can look like - Sum of Amount MONTH CO 11 12 Grand Total Apple 5200 3000 8200 Orange 800 800 Peach 6000 6000 Grand Total 12000 3000 15000 +-------------------------------------------------------------------+ |Filename: ExcelSample.zip | |Download: http://www.excelforum.com/attachment.php?postid=4128 | +-------------------------------------------------------------------+ -- wjohnson ------------------------------------------------------------------------ wjohnson's Profile: http://www.excelforum.com/member.php...o&userid=29640 View this thread: http://www.excelforum.com/showthread...hreadid=494243 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking up information based on Columns and Rows
Ok, I figured out what I was doing wrong! :) Thanks to everyone :)
"Bernard Liengme" wrote: The data is in Sheet1 in A2:C101, the summary in Sheet2 In Sheet2 to sum Amts for Apples in November =SUMPRODUCT(--(Sheet1!A2:A101="Apple"),--(Sheet1!B2:B101=11),C2:C101) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Sally J" wrote in message ... I have a spread sheet that looks like this CO. Month Amt Apple 11 $5000 Apple 12 3000 Orange 11 500 Orange 11 300 Apple 11 50 Peach 11 6000 Apple 11 150 In a differnet spreadsheet, I want to look up each company based on the month it is found in and sum the amounts given. Example November Spreadsheet Co. Total Apple 5200 Orange 800 Peach 6000 December Spreadsheet Co. Total Apple 3000 Orange - Peach - This will continue throughout the 12 months. the first spreadsheet is the main one where information will be inputed. I need the formula to not only look at the month but who the company is as well. I used to SUMIF function when i was looking at just the month in general but now I need to add to the formula so it will only add those rows that have the correct company attached. Help will be much appreciated. Thanks, Sally |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking up information based on Columns and Rows
I know a pivot table would be nice and easy to do. However there is more
information on Sheet 2 that is not found on Sheet 1. And as you know a pivot talbe can not be modified once it is set up. So the best answer is a fromula. But thanks for the post. "wjohnson" wrote: Sally - What I think you want to do is what "PIVOT TABLES" are made to accomplish. I have attached a small spreadsheet with a generated pivot table in it. You might have to do a little "trial and error" and review of "PIVOT TABLES" to get the results you want - but they can do a lot of things. Here is what the results can look like - Sum of Amount MONTH CO 11 12 Grand Total Apple 5200 3000 8200 Orange 800 800 Peach 6000 6000 Grand Total 12000 3000 15000 +-------------------------------------------------------------------+ |Filename: ExcelSample.zip | |Download: http://www.excelforum.com/attachment.php?postid=4128 | +-------------------------------------------------------------------+ -- wjohnson ------------------------------------------------------------------------ wjohnson's Profile: http://www.excelforum.com/member.php...o&userid=29640 View this thread: http://www.excelforum.com/showthread...hreadid=494243 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum based on criteria in rows and columns | Excel Discussion (Misc queries) | |||
How to swap rows and columns? | Excel Discussion (Misc queries) | |||
autofill information from rows to columns without using transpose | Excel Discussion (Misc queries) | |||
Clearing information in certain columns | Excel Discussion (Misc queries) | |||
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) |