Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using SUMIF inside SUMPRODUCT = complex problem (suite) | Excel Worksheet Functions | |||
Using VLOOKUP inside SUMPRODUCT = complex problem | Excel Worksheet Functions | |||
Complex SUMPRODUCT/VLOOKUP/SUMIF | Excel Worksheet Functions | |||
Sumproduct syntax - more complex | Excel Discussion (Misc queries) | |||
Help with Complex SUMPRODUCT formula | Excel Worksheet Functions |