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, change and/or hardware activity did john do but not just in the numerical sense.

Looking for John--App--8 then on the next row John--change--2 but I do not want to display a John--Hardware--0 as I do not care about 0 hours for a certain activity. I have used SumProduct to get the hourly total but I cannot find an easy way to output this with name and activity 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)

"boobot" <[email protected]> wrote in
message news:[email protected]...
>
> 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, change
> and/or hardware activity did john do but not just in the numerical
> sense.
>
> Looking for John--App--8 then on the next row John--change--2 but I do
> not want to display a John--Hardware--0 as I do not care about 0 hours
> for a certain activity. I have used SumProduct to get the hourly total
> but I cannot find an easy way to output this with name and activity
> especially when the data will be wiped and renewed weekly!
>
>
> --
> boobot
> ------------------------------------------------------------------------
> boobot's Profile:
http://www.excelforum.com/member.php...o&userid=34667
> View this thread: http://www.excelforum.com/showthread...hreadid=544389
>

[Miguel Zapico]

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.

"boobot" wrote:

>
> 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, change
> and/or hardware activity did john do but not just in the numerical
> sense.
>
> Looking for John--App--8 then on the next row John--change--2 but I do
> not want to display a John--Hardware--0 as I do not care about 0 hours
> for a certain activity. I have used SumProduct to get the hourly total
> but I cannot find an easy way to output this with name and activity
> especially when the data will be wiped and renewed weekly!
>
>
> --
> boobot
> ------------------------------------------------------------------------
> boobot's Profile: http://www.excelforum.com/member.php...o&userid=34667
> View this thread: http://www.excelforum.com/showthread...hreadid=544389
>
>