Question about SumIF

[zhuanyi]

Hello,
I know this is really confusing, but...anyway....
Say for example I have 4 columns of data, the first contains, say, the name of the sales man, second column contains the name of a client and and the third column contains different projects and are mixed together, the forth contains the number of hours of the sales man spend on this particular client and in this particular project, a sales man could spend time on multiple projects and of course, a client with multiple project may be given to different salesman. Now I want to know how many hours altogether does a parituclar salesman spend on all the projects for a particular client by constructing a separate sheet in this work book, is there a chance I could do that using sumif? Or is there any better suggestion? Thanks a lot!

Regards,
Anyi

[Max]

Hazarding a thought .. believe using a pivot table would be a good start to
help unravell some of the many underlying intents expressed
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"zhuanyi" wrote:
>
> Hello,
> I know this is really confusing, but...anyway....
> Say for example I have 4 columns of data, the first contains, say, the
> name of the sales man, second column contains the name of a client and
> and the third column contains different projects and are mixed
> together, the forth contains the number of hours of the sales man spend
> on this particular client and in this particular project, a sales man
> could spend time on multiple projects and of course, a client with
> multiple project may be given to different salesman. Now I want to know
> how many hours altogether does a parituclar salesman spend on all the
> projects for a particular client by constructing a separate sheet in
> this work book, is there a chance I could do that using sumif? Or is
> there any better suggestion? Thanks a lot!
>
> Regards,
> Anyi
>
>
> --
> zhuanyi
> ------------------------------------------------------------------------
> zhuanyi's Profile: http://www.excelforum.com/member.php...o&userid=37361
> View this thread: http://www.excelforum.com/showthread...hreadid=570563
>
>

[JMB]

Another possibility. Say A1:A10 is the salesman name, B1:B10 is the client,
and D1:D10 is the hours. Change ranges as needed. Note that Sumproduct
cannot accomodate entire columns, such as A:A.

=SUMPRODUCT(--(A1:A10="Joe"), --(B1:B10="ABC Corp"), D1:D10)


"zhuanyi" wrote:

>
> Hello,
> I know this is really confusing, but...anyway....
> Say for example I have 4 columns of data, the first contains, say, the
> name of the sales man, second column contains the name of a client and
> and the third column contains different projects and are mixed
> together, the forth contains the number of hours of the sales man spend
> on this particular client and in this particular project, a sales man
> could spend time on multiple projects and of course, a client with
> multiple project may be given to different salesman. Now I want to know
> how many hours altogether does a parituclar salesman spend on all the
> projects for a particular client by constructing a separate sheet in
> this work book, is there a chance I could do that using sumif? Or is
> there any better suggestion? Thanks a lot!
>
> Regards,
> Anyi
>
>
> --
> zhuanyi
> ------------------------------------------------------------------------
> zhuanyi's Profile: http://www.excelforum.com/member.php...o&userid=37361
> View this thread: http://www.excelforum.com/showthread...hreadid=570563
>
>

[JMB]

Of course, I would set up cell references instead of hardcoding the salesman
name and client into the formula.

"JMB" wrote:

> Another possibility. Say A1:A10 is the salesman name, B1:B10 is the client,
> and D1:D10 is the hours. Change ranges as needed. Note that Sumproduct
> cannot accomodate entire columns, such as A:A.
>
> =SUMPRODUCT(--(A1:A10="Joe"), --(B1:B10="ABC Corp"), D1:D10)
>
>
> "zhuanyi" wrote:
>
> >
> > Hello,
> > I know this is really confusing, but...anyway....
> > Say for example I have 4 columns of data, the first contains, say, the
> > name of the sales man, second column contains the name of a client and
> > and the third column contains different projects and are mixed
> > together, the forth contains the number of hours of the sales man spend
> > on this particular client and in this particular project, a sales man
> > could spend time on multiple projects and of course, a client with
> > multiple project may be given to different salesman. Now I want to know
> > how many hours altogether does a parituclar salesman spend on all the
> > projects for a particular client by constructing a separate sheet in
> > this work book, is there a chance I could do that using sumif? Or is
> > there any better suggestion? Thanks a lot!
> >
> > Regards,
> > Anyi
> >
> >
> > --
> > zhuanyi
> > ------------------------------------------------------------------------
> > zhuanyi's Profile: http://www.excelforum.com/member.php...o&userid=37361
> > View this thread: http://www.excelforum.com/showthread...hreadid=570563
> >
> >