Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif of Sumif perhaps?
I have a rather large spreadsheet of Project Manager data that I want
to use to generate a pivot report from, however in the Pivot I want to create %'ages of the subtotals. All that I understand as there are several threads that give me sufficienf pointers. What i'm stuggling with is the SUMIF side of it. My data layout (or a cut-down version of it) is as follows A B C D ----------- O P---- AA Project Project Team Forecast Forecast Sumif Sumif Manager Name Name Days - Jan Days- Dec Jan Dec Each project can have any of up to 20 different teams working on it A Project can be managed by any 1 of 15 Project Managers Each Project Manager can be managing up to 15 projects at any one time What I want to produce in my pivot is a report showing the %'age of a working month each Project manager is spending on each of his projects. But I only want to include the forecast time for the Project Management team, not any of the other 20 or so teams that may be present. My attempt at the sumif went as follows =IF($B2="Proj - Project Management - EU",SUMIF($A$2:$A$587,$A2,D$2:D$587),0) in columns P thru AA for columns D thru O. That worked, after a fashion, but included all the forecast data for every team rather than just the Project Managers, which I (perhaps optimistically) thought the IF would filter out for me. Any help would be much appreciated. Regards Fred |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif of Sumif perhaps?
i find that for complex multiple extraction, the sumproduct function is better and easier -- robert111 ------------------------------------------------------------------------ robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996 View this thread: http://www.excelforum.com/showthread...hreadid=527189 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif of Sumif perhaps?
OK I have a sort of solution, using 2 additional columns for each month
in the original data, In columns P - AA I used a formula to get only the values for the Project Managers forecasts =IF(C2="Proj - Project Management - EU",D2,0) and then in columns AB - AM I used a SUMIF to calculate the %'age allocated to each project for each month. =IF(ISERROR(P2/SUMIF($A$2:$A$1000,$A2,P$2:P$1000)),0,(P2/SUMIF($A$2:$A$1000,$A2,P$2:P$1000))) A B C D -------- O P---- AA AB --- AM Project Project Team Forecast Forecast Sumif Sumif % % Manager Name Name Days-Jan Days-Dec Jan Dec Jan Dec And then simply pivoted the data to get the desired results. Not the prettiest of solutions I admit, but it worked. Can someone explain how Robert's suggestion to use SumProduct would have been used in this instance as I don't follow the logic of multiplying two text values to come up with something meaningful. Regards Fred |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
SUMIF | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
SUMIF with Mutiple Ranges & Criteria | Excel Discussion (Misc queries) | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |