a complex use of sumproduct ?

[pimar]

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

 

[Bob Phillips]

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[pimar]

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
+-------------------------------------------------------------------