Looking up information based on Columns and Rows

[Sally J]

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

 

[Domenic]

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!

 

[Bernard Liengme]

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

 

[Sally J]

=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?

 

[wjohnson]

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 |
+-------------------------------------------------------------------+

 

[Sally J]

Ok, I figured out what I was doing wrong! Thanks to everyone

 

[Sally J]

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.