Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sally J
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bernard Liengme
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sally J
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
wjohnson
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sally J
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sally J
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sum based on criteria in rows and columns EstherJ Excel Discussion (Misc queries) 1 November 1st 05 11:28 AM
How to swap rows and columns? [email protected] Excel Discussion (Misc queries) 5 September 21st 05 08:07 AM
autofill information from rows to columns without using transpose Rayce Excel Discussion (Misc queries) 1 September 4th 05 01:44 AM
Clearing information in certain columns jolly_lolly Excel Discussion (Misc queries) 1 April 22nd 05 02:41 AM
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns foofoo Excel Discussion (Misc queries) 1 April 2nd 05 12:02 AM


All times are GMT +1. The time now is 12:53 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"