Aleternative to SUMPRODUCT formula

[matt45]

Hi,

I'm starting this thread again with a spreadsheet extract so hopefully this will help. I need a less cumbersome alternative to a sumproduct formula as I need to build in a department layer as well. The formula is just not easy to decipher etc. Can anyone help?

Thanks

Matt

[Olly]

Is that data copied from a pivot table?

If it is, then why not simply reference the original pivot table, using =GETPIVOTDATA ?

[matt45]

Wouldn't it just be an even less user friendly formula then? Plus there are hardcoded versions around so some are pasted values of pivot tables if you know what I mean. Isn't there a CHOOSE function or similiar that would be easier to decipher perhaps? SUMPRODUCT just looks cumbersome and too long, probably too memory intensive as well.

[Olly]

Wouldn't it just be an even less user friendly formula then?

Not at all. GETPIVOTDATA uses field names and parameter values, it's a simple formula to read and write.

It's really poor practice to take hard copies of pivot tables!! Keep your data organised in one place, and write your reports to reference that data... so if your data changes, you don't need to change the reports, and vice versa...

Maybe if you post a more complete workbook, which contains your source data structure, and your required outcome, we could look at the best solution...

[matt45]

Hard copies are taken due to disciplines not really in place yet re closes etc (another story). But aside from the pivot table option, are there any other options? I think I just hate SUMPRODUCT. Sorry, I created a cut down version of the problem, reluctant to upload a complete spreadsheet/workbook for obvious reasons

[Olly]

You COULD use something like:

Please Login or Register  to view this content.

See attachment for worked example.

But that's not really much simpler than what you already have.

I really don't understand why you wouldn't look at the source data, rather than trying to find complex formulas to interrogate hard-coded crosstab data, though...

[6StringJazzer]

I'm starting this thread again

Please do not do that. It violates our rules against duplicate threads. You should have updated your original thread instead. I am going to close that thread to prevent any further confusion.



Please take the time to review our rules. There aren't many, and they are all important.