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!

In article <[email protected]>,
"Sally J" <[email protected]> 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

[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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Sally J" <[email protected]> wrote in message
news:[email protected]...
>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

[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

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



"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 <[email protected]>,
> "Sally J" <[email protected]> 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
>

[Sally J]

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" <[email protected]> wrote in message
> news:[email protected]...
> >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
>
>
>

[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.

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