SUMIF based on data in adjacent row

[jcpotwor]

Hello,

I have a spreadsheet used almost like a timesheet, which I use to compare
projected hours spent against acutal hours spent. The list of names is
fairly long and can have the same name twice so I would like to create a
summary report which groups the total hours spent for an idividual person.

Example: In column 'A' I have each staff member billing their time to a
different project. Column 'B' is the expected hours they would spend over a
two day period, Column 'C' and 'D' are the actual hours spent in those two
days and Column 'E' is the total of the actual hours spent by that person on
that project.

A B C D E
1 ABC 16 8 7 15
2 DEF 16 8 8 16
3 GHI 16 8 8 16
4 ABC 16 12 9 21
5 JKL 16 8 8 16
6 MNO 16 12 8 20
7 DEF 16 8 8 16

I would like to take this data and create a summary table that looks like
this:

A B C D E
1 ABC 32 20 16 36
2 DEF 32 16 16 32
3 GHI 16 8 8 16
4 JKL 16 8 8 16
5 MNO 16 12 8 20

Which is sort of the same as above, however, all of the time spent by 'ABC'
is now consolidated into one row. My list is very long and I do not want to
use filters or program in miles of "IF" statements so I was wondering if
there was a certain type of "SUMIF" formula that would work. Please let me
know. THANKS!

 

[Dave O]

Try a search on this newsgroup
http://groups.google.com/group/microsoft.public.excel.misc
using search terms "sumproduct double unary". It's ideal for this
application.

 

[Bob Phillips]

Assuming the data is on Sheet1

On Sheet 2, list the people in A2:An

In B2

=SUMPRODUCT(--(Sheet1!$A$2:$A$200=$A2),Sheet1!$B$2:$B$200)

copy across and down

 

[RagDyer]

Say you put your summary list in Sheet2, same as your example;
Unique list of names starting in A2,

In B2 enter:

=SUMIF(Sheet1!$A$2:$A$100,$A2,Sheet1!B$2:B$100)

And copy across to E2, then down as needed.

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[jcpotwor]

Thanks for the tips...now is there any way to do this without creating a
second sheet, using the autofilters on the orignal data but still counting
only the unique listings? Bascially I am looking for a formula that would
could the number of entries in the autofilter list.

 

[Bob Phillips]

=SUBTOTAL(9,B:B)

--
HTH

RP

Thanks for the tips...now is there any way to do this without creating a
second sheet, using the autofilters on the orignal data but still counting
only the unique listings? Bascially I am looking for a formula that would
could the number of entries in the autofilter list.

 

[jcpotwor]

Thanks again for the tip but it still does not work. Let me try a more
simpler spreadsheet. Below is a list which I use the autofilters for. I
want to count the number of names without counting the duplicates, I also
want the number to update if I autofiltered the list by a specific project.

Name Project
Joe 1
Jim 2
Bob 4
Sam 4
Joe 5
Sam 4
Rob 7
Jimmy 8
Joe 9

So the count should at this point be equal to 6 for each individual name. I
also want to use the auto filter so that they value will update. So if I
filtered the list for project 4:

Name Project
Bob 4
Sam 4
Sam 4

That same formula would net a new value of 2.

Is this possible?

Thanks again for all of your help!