How do I sum numbers based on other data

[Sal]

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.

 

[Mike H]

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.

 

[Roger Govier]

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

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

 

[Sal]

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]

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)*(Sheet1!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]

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)*(Transactions!E14:E10012)(Transactions!F2:F10000>=F10000)*(Transactions!F2:F10000<=F2))

 

[Roger Govier]

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

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)*(Transactions!E14:E10012)(Transactions!F2:F10000>=F10000)*(Transactions!F2:F10000<=F2))







__________ 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