a complex use of sumproduct ?
 

Hello,
I've a quite complex (at least for me :-)) problem I should solve
within my excel (possible not using VBA).
Suppose you have this sheet:

week 1/1/06 1/8/06 1/15/06 1/22/06

john 3 5 3 5
---- s s s s
jim 5 5 5 5
---- i i i i
micky 5 5 5 5
----- a a a a
mouse 5 5 5 5
----- w w w w
sara 5 5 5 5
---- s i i i

where, for each week, I've listed the number of days a single resource
(ie micky) and the task (s,i,a,w) he/she will work on (example: john
will be present 3 days on week 1/15 and he will work on task "s")

Now, what I need to do is to summarize how many people/days will be
spend for each task for each week

Something like this:
task *i*
week 8-Jan
people/days == 10



the diffculty is that the "week" and the task for which I need to
summarize the people/days spent, are inputs from user.

I was thinking to use the SUMPRODUCT, but it doesn't work since the
presence of chracters in the interested area ...

Hope the problem is clear enough...
*pimar


--
pimar
------------------------------------------------------------------------
pimar's Profile: http://www.excelforum.com/member.php...fo&userid=5386
View this thread: http://www.excelforum.com/showthread...hreadid=516027

a complex use of sumproduct ?
 

=SUMPRODUCT((B1:E1=--"2006-01-08")*(M2:P6="i")*(B2:E6))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"pimar" wrote in
message ...

Hello,
I've a quite complex (at least for me :-)) problem I should solve
within my excel (possible not using VBA).
Suppose you have this sheet:

week 1/1/06 1/8/06 1/15/06 1/22/06

john 3 5 3 5
---- s s s s
jim 5 5 5 5
---- i i i i
micky 5 5 5 5
----- a a a a
mouse 5 5 5 5
----- w w w w
sara 5 5 5 5
---- s i i i

where, for each week, I've listed the number of days a single resource
(ie micky) and the task (s,i,a,w) he/she will work on (example: john
will be present 3 days on week 1/15 and he will work on task "s")

Now, what I need to do is to summarize how many people/days will be
spend for each task for each week

Something like this:
task *i*
week 8-Jan
people/days == 10



the diffculty is that the "week" and the task for which I need to
summarize the people/days spent, are inputs from user.

I was thinking to use the SUMPRODUCT, but it doesn't work since the
presence of chracters in the interested area ...

Hope the problem is clear enough...
*pimar


--
pimar
------------------------------------------------------------------------
pimar's Profile:
http://www.excelforum.com/member.php...fo&userid=5386
View this thread: http://www.excelforum.com/showthread...hreadid=516027

a complex use of sumproduct ?
 

Hello Bob - thanks a lot for yuor reply
I was tring to use it, but I've the same problem I experienced befor
posting the question (I get a #VALUE as result).. could be it is due t
the fact that the area to which I apply the "SUMPRODUCT" function
contains characters. To be more clear, I'm posting a copy of th
spreadsheet with a more accurate sample.
If you can have a look it would be great .

thanks in adavance
*pima

+-------------------------------------------------------------------
|Filename: example.zip
|Download: http://www.excelforum.com/attachment.php?postid=4394
+-------------------------------------------------------------------

--
pima
-----------------------------------------------------------------------
pimar's Profile: http://www.excelforum.com/member.php...nfo&userid=538
View this thread: http://www.excelforum.com/showthread.php?threadid=51602