How do I sum scattered entries on an Excel worksheet?

I would like to be able to locate all of one type of entry on a spreadsheet
and sum the accompanying entered values. Example: Users enter mutliple
entries for SMITH job in the NAME column(A) and a value of 1.5 in the HOURS
column(B) associated with it. They do this various times on this worksheet,
but not in sequence. How can I LOCATE all of the scattered SMITH entries in
the NAME column and SUM the associated VALUES in the HOURS column, giving me
all of the hours entered for the SMITH job?
EXCEL 2002 SP3

Hi

In column C you could have a formula like :
=if(a3="SMITH",+b3," "), copy the formula
down the column to the bottom, then at the bottom
of col C you could just sum the values of the
column. You could then use the next
column to add the values for another person
so you have a total in each column giving
you the total hours for each person.

Good luck.

Hi

Try something like this:
=SUMPRODUCT((A1:A1000="Smith")*--(B1:B1000))
With this function, you can't use full columns and the ranges must be the
same size.

Hope this helps.
Andy.



"bassmanfranc" <bassmanfranc@discussions.microsoft.com> wrote in message
news:4DB82830-F732-41E6-B06E-32BB418B9ABB@microsoft.com...
>I would like to be able to locate all of one type of entry on a spreadsheet
> and sum the accompanying entered values. Example: Users enter mutliple
> entries for SMITH job in the NAME column(A) and a value of 1.5 in the
> HOURS
> column(B) associated with it. They do this various times on this
> worksheet,
> but not in sequence. How can I LOCATE all of the scattered SMITH entries
> in
> the NAME column and SUM the associated VALUES in the HOURS column, giving
> me
> all of the hours entered for the SMITH job?
> EXCEL 2002 SP3

=SUMIF(A:A,"SMITH",B:B)

Please don't multi-post: cross-post if you must, but just post once....

HTH,
Bernie
MS Excel MVP


"bassmanfranc" <bassmanfranc@discussions.microsoft.com> wrote in message
news:4DB82830-F732-41E6-B06E-32BB418B9ABB@microsoft.com...
>I would like to be able to locate all of one type of entry on a spreadsheet
> and sum the accompanying entered values. Example: Users enter mutliple
> entries for SMITH job in the NAME column(A) and a value of 1.5 in the HOURS
> column(B) associated with it. They do this various times on this worksheet,
> but not in sequence. How can I LOCATE all of the scattered SMITH entries in
> the NAME column and SUM the associated VALUES in the HOURS column, giving me
> all of the hours entered for the SMITH job?
> EXCEL 2002 SP3

=SUMPRODUCT(('Dublin Level 3'!D3:D420=C6)*--('Dublin Level 3'!G3:G420))


The SUMPRODUCT Function also works if relates to multiple sheet ranges, see
attached.

This is just an excellent way of getting Pivot Totals instead on doing a
pivot table. You should also look at generating a Pivot Table from the Info
as this allows a drill down but the Pivot has to be refreshed all the time
but the SUMPRODUCT does not.



"Andy" wrote:

> Hi
>
> Try something like this:
> =SUMPRODUCT((A1:A1000="Smith")*--(B1:B1000))
> With this function, you can't use full columns and the ranges must be the
> same size.
>
> Hope this helps.
> Andy.
>
>
>
> "bassmanfranc" <bassmanfranc@discussions.microsoft.com> wrote in message
> news:4DB82830-F732-41E6-B06E-32BB418B9ABB@microsoft.com...
> >I would like to be able to locate all of one type of entry on a spreadsheet
> > and sum the accompanying entered values. Example: Users enter mutliple
> > entries for SMITH job in the NAME column(A) and a value of 1.5 in the
> > HOURS
> > column(B) associated with it. They do this various times on this
> > worksheet,
> > but not in sequence. How can I LOCATE all of the scattered SMITH entries
> > in
> > the NAME column and SUM the associated VALUES in the HOURS column, giving
> > me
> > all of the hours entered for the SMITH job?
> > EXCEL 2002 SP3
>
>
>

On Fri, 20 Jan 2006 04:58:03 -0800, bassmanfranc wrote:

> I would like to be able to locate all of one type of entry on a spreadsheet
> and sum the accompanying entered values. Example: Users enter mutliple
> entries for SMITH job in the NAME column(A) and a value of 1.5 in the HOURS
> column(B) associated with it. They do this various times on this worksheet,
> but not in sequence. How can I LOCATE all of the scattered SMITH entries in
> the NAME column and SUM the associated VALUES in the HOURS column, giving me
> all of the hours entered for the SMITH job?
> EXCEL 2002 SP3

Sub FindSMITH

row = Application.WorksheetFunction.Match("SMITH","A1:A200", 0)
total = total + Cells(row,2).Value
Cells(1,3).Value = total

End sub

Function Match is looking for "SMITH" in the range "A1:A200" (you can
change the range to your needs) and returns the row number ...
Next you add the value of the cell in that row and column B (column index
2) to the "total" variable
Third line adds the value of total to cell C1 ( Cells(1,3) ). You can
change this also and put the "total" value where you want ...