How do I sum numbers based on other data
 

I have a spreadsheet with several thousand entries. On one sheet, call it
sheet 1, there is One column with about 100 names, another column has number
amounts. My other sheet, call it sheet 2, has a list of names. What I would
like to do is for each name on sheet 2, sum up the number values from sheet
one for that particular name. So the end result would be a column on sheet
two that shows the totals taken from sheet 1 for each name. I keep adding new
entries on sheet one, so the totals on sheet 2 must always show the updated
totals. Any help would be greatly appreciated. I am using office 2007.

How do I sum numbers based on other data
 

Sal,

Try this on sheet 2

=SUMPRODUCT((A1=Sheet1!A:A)*(Sheet1!B:B))

drag down as required
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Sal" wrote:

I have a spreadsheet with several thousand entries. On one sheet, call it
sheet 1, there is One column with about 100 names, another column has number
amounts. My other sheet, call it sheet 2, has a list of names. What I would
like to do is for each name on sheet 2, sum up the number values from sheet
one for that particular name. So the end result would be a column on sheet
two that shows the totals taken from sheet 1 for each name. I keep adding new
entries on sheet one, so the totals on sheet 2 must always show the updated
totals. Any help would be greatly appreciated. I am using office 2007.

How do I sum numbers based on other data
 

Hi Sal

Try entering in B1 of Sheet2
=IF(A1="","",SUMIF(Sheet1!A:A,A1,Sheet1!B:B))
Copy down for the 100 or so rows that you need.
--
Regards
Roger Govier

"Sal" wrote in message
...
I have a spreadsheet with several thousand entries. On one sheet, call it
sheet 1, there is One column with about 100 names, another column has
number
amounts. My other sheet, call it sheet 2, has a list of names. What I
would
like to do is for each name on sheet 2, sum up the number values from
sheet
one for that particular name. So the end result would be a column on sheet
two that shows the totals taken from sheet 1 for each name. I keep adding
new
entries on sheet one, so the totals on sheet 2 must always show the
updated
totals. Any help would be greatly appreciated. I am using office 2007.

__________ Information from ESET Smart Security, version of virus
signature database 4801 (20100124) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 4801 (20100124) __________

The message was checked by ESET Smart Security.

http://www.eset.com

How do I sum numbers based on other data
 

Perfect Mike, thank you so much. I would like to add another part to that
equation. My entries are also listed by date. Is it possible to add to that
equation to include a certain date range?

Sal

"Mike H" wrote:

Sal,

Try this on sheet 2

=SUMPRODUCT((A1=Sheet1!A:A)*(Sheet1!B:B))

drag down as required
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Sal" wrote:

I have a spreadsheet with several thousand entries. On one sheet, call it
sheet 1, there is One column with about 100 names, another column has number
amounts. My other sheet, call it sheet 2, has a list of names. What I would
like to do is for each name on sheet 2, sum up the number values from sheet
one for that particular name. So the end result would be a column on sheet
two that shows the totals taken from sheet 1 for each name. I keep adding new
entries on sheet one, so the totals on sheet 2 must always show the updated
totals. Any help would be greatly appreciated. I am using office 2007.

How do I sum numbers based on other data
 

Sal,

This assumes on sheet 1 we have

Col A = Names
Col B = values to sum
Col C = dates

On sheet 2
C1 = early (start) date
D1 = later (End) date

=SUMPRODUCT((A1=Sheet1!A:A)*(Sheet1!C:C=C1)*(Shee t1!C:C<=D1)*(Sheet1!B:B))

Note that while you can use full columns if you can I would shorten the
ranges to speed things up a bit
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Sal" wrote:

Perfect Mike, thank you so much. I would like to add another part to that
equation. My entries are also listed by date. Is it possible to add to that
equation to include a certain date range?

Sal

"Mike H" wrote:

Sal,

Try this on sheet 2

=SUMPRODUCT((A1=Sheet1!A:A)*(Sheet1!B:B))

drag down as required
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Sal" wrote:

I have a spreadsheet with several thousand entries. On one sheet, call it
sheet 1, there is One column with about 100 names, another column has number
amounts. My other sheet, call it sheet 2, has a list of names. What I would
like to do is for each name on sheet 2, sum up the number values from sheet
one for that particular name. So the end result would be a column on sheet
two that shows the totals taken from sheet 1 for each name. I keep adding new
entries on sheet one, so the totals on sheet 2 must always show the updated
totals. Any help would be greatly appreciated. I am using office 2007.

How do I sum numbers based on other data
 

I tried this using the F1 and F10000 as the upper and lower dates and it
didn't work. Can I just put in my own dates, like F=12/31/09

=SUMPRODUCT((A1=Transactions!G14:G10012)*(Transact ions!E14:E10012)(Transactions!F2:F10000=F10000)*( Transactions!F2:F10000<=F2))





"Mike H" wrote:

Sal,

This assumes on sheet 1 we have

Col A = Names
Col B = values to sum
Col C = dates

On sheet 2
C1 = early (start) date
D1 = later (End) date

=SUMPRODUCT((A1=Sheet1!A:A)*(Sheet1!C:C=C1)*(Shee t1!C:C<=D1)*(Sheet1!B:B))

Note that while you can use full columns if you can I would shorten the
ranges to speed things up a bit
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Sal" wrote:

Perfect Mike, thank you so much. I would like to add another part to that
equation. My entries are also listed by date. Is it possible to add to that
equation to include a certain date range?

Sal

"Mike H" wrote:

Sal,

Try this on sheet 2

=SUMPRODUCT((A1=Sheet1!A:A)*(Sheet1!B:B))

drag down as required
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Sal" wrote:

I have a spreadsheet with several thousand entries. On one sheet, call it
sheet 1, there is One column with about 100 names, another column has number
amounts. My other sheet, call it sheet 2, has a list of names. What I would
like to do is for each name on sheet 2, sum up the number values from sheet
one for that particular name. So the end result would be a column on sheet
two that shows the totals taken from sheet 1 for each name. I keep adding new
entries on sheet one, so the totals on sheet 2 must always show the updated
totals. Any help would be greatly appreciated. I am using office 2007.

How do I sum numbers based on other data
 

Hi Sal

Is there any reason why you are using different ranges 2:10000 and 14:10012?
I agree they are the same dimensions, but just wondered why they are
different.

Your formula probably doesn't work because you are looking at cells F2 and
F10000 on your report sheet, NOT on your Transactions sheet.

Yes you can hard code a date into the formula either by forcing the text
date to be numeric with the double unary minus
F2:F10000=--"12/31/09"
or
F2:F10000=Date(2009,12,31)

--
Regards
Roger Govier

"Sal" wrote in message
...
I tried this using the F1 and F10000 as the upper and lower dates and it
didn't work. Can I just put in my own dates, like F=12/31/09

=SUMPRODUCT((A1=Transactions!G14:G10012)*(Transact ions!E14:E10012)(Transactions!F2:F10000=F10000)*( Transactions!F2:F10000<=F2))





"Mike H" wrote:

Sal,

This assumes on sheet 1 we have

Col A = Names
Col B = values to sum
Col C = dates

On sheet 2
C1 = early (start) date
D1 = later (End) date

=SUMPRODUCT((A1=Sheet1!A:A)*(Sheet1!C:C=C1)*(Shee t1!C:C<=D1)*(Sheet1!B:B))

Note that while you can use full columns if you can I would shorten the
ranges to speed things up a bit
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Sal" wrote:

Perfect Mike, thank you so much. I would like to add another part to
that
equation. My entries are also listed by date. Is it possible to add to
that
equation to include a certain date range?

Sal

"Mike H" wrote:

Sal,

Try this on sheet 2

=SUMPRODUCT((A1=Sheet1!A:A)*(Sheet1!B:B))

drag down as required
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis
that
introduces the fewest assumptions while still sufficiently answering
the
question.


"Sal" wrote:

I have a spreadsheet with several thousand entries. On one sheet,
call it
sheet 1, there is One column with about 100 names, another column
has number
amounts. My other sheet, call it sheet 2, has a list of names.
What I would
like to do is for each name on sheet 2, sum up the number values
from sheet
one for that particular name. So the end result would be a column
on sheet
two that shows the totals taken from sheet 1 for each name. I keep
adding new
entries on sheet one, so the totals on sheet 2 must always show
the updated
totals. Any help would be greatly appreciated. I am using office
2007.

__________ Information from ESET Smart Security, version of virus
signature database 4802 (20100124) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 4802 (20100124) __________

The message was checked by ESET Smart Security.

http://www.eset.com