Solution with SumProduct?

[boobot]

Novice here,

I need to have an output of more than just a find/sum of two columns
If the below were on columns A,B and C

name--activity--hours
bob--change--5
bob--change--4
bob--app --4
john--app--3
john--app--5
john--change--2
bill--hardware--3
bill--hardware--1
bill--app--2
bill--app--4

What I would like to do is output how many hours of the app, chang
and/or hardware activity did john do but not just in the numerica
sense.

Looking for John--App--8 then on the next row John--change--2 but I d
not want to display a John--Hardware--0 as I do not care about 0 hour
for a certain activity. I have used SumProduct to get the hourly tota
but I cannot find an easy way to output this with name and activit
especially when the data will be wiped and renewed weekly

 

[Bob Phillips]

Why not build a table on another sheet with say Change in B1, App in C1,
etc., Bob in A2, Bill in A3, etc. then in B2 add

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

copy down and across, and just suppress zeroes (Tools>Options>General,
uncheck zero values checkbox)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Guest]

If you have weekly updates, I would recommend to use a pivot table in this
case. If you use a dynamic named range as the origin, it will react to the
changes in the data with just refreshing the table each week.

Hope this helps,
Miguel.