Pivot table or SUMPRODUCT?

[mfarah]

I’m trying to find out how many consultant and peer days (column P through to W) are used allocated and delivered to support poor or weak organisations (column B) against 11 themes (workforce, local democracy, Leadership/local democracy, etc (column BW))<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
from a big excel spreadsheet that is updated by a lot of people and does not belong to me so I cannot change it - though i have to obtain this intelligence from it each month.

I’m told a pivot table is best to display this but try as I might I cannot get this information into a pivot table in any meaningful way - so any help here would be appreicated. In the absence of a pivot table I have tried to do a calculation using SUMPRODUCT e.g: =SUMPRODUCT(-(NORTH!BW3:BW101="Leadership/local democracy"),-(NORTH!B3:B101="Weak"),NORTH!P3:P101) or SUM( IF) but neither work so I’m obviously doing something wrong – see Poor and weak delivery tab in this file - http://www.box.net/shared/r6bcom48dv for my paltry effort in this.

Once i work out the delivery to poor and weaks by theme i also have to do a COUNT of poor and weaks by theme also...
<o> </o>
hope you can help – am available for additional clarifying questions…the file is located here - http://www.box.net/shared/r6bcom48dv would appreciate your help

 

[barry houdini]

I can't access your file but the SUMPRODUCT formula looks like a viable formula, what result does it give you, is it zero or error or a non-zero result that doesn't appear correct

 

[mfarah]

I've just tested the file and if you click on the download button and then on open it should open for you.....In anycase It gives me a #N/A error I maybe should state that there are spaces in some of the colums - would this make a difference even if it's not on the relevant line of data that should be returned by that function?

 

[barry houdini]

I can't access the site because of restrictions where I work......

If you get #N/A that probably means you have #N/A errors somewhere in the data. SUMPRODUCT won't work if you have errors in any of the ranges. You can change the formula or eliminate the #N/As. If you have some where are they (which columns)?