URGENT SUMPRODUCT INDEX Formula

To the wonderfully helpful smart bunch you all are, I need some further assistance building another model!

I have a sumproduct Index issue.

I would like to sum column based on a number of criteria.

The conditions are:
1. Division (in this case marketing)
2. Subdivision (in this case either CE02 CE03 onwards)
3. Location (Central or otherwise)
4. Year (2012 or 2013)
5. Company (13A up to 31S)
Other conditions could be for example Column F & E in the DATA tab

Challenges for me:

The index is quite big
The Year and Company are row based conditions
There could be up to 12 conditions as I would like the model to remain flexible for future use
.....(someone else on this forum taught me about using column index e.g. ("&A1&":"&A1) where A1 would have a column reference L or something which I think will allow me to make the model really flexible but I havent tried it yet!


I have attached a sample spreadsheet and please do ask any questions if it is not quite clear enough.

Thanks, I would like to use the SUMPRODUCT but I cant get it to work

In the sample spreadsheet I show the issue I am having with the SUMPRODUCT,

=SUMPRODUCT((DATA!B:B=Profiling!C4)*(DATA!D:D=C12)*(DATA!K1:AX1="2012")*(DATA!K2:AX2="05B")*(DATA!A1:AX2000))

I have been using a version of this but it keeps bringing back a 0

I would really appreciate some help on this

Thanks so much everyone

K

In what way do I need to change the layout in the DATA sheet?

Thanks so much,

Keelin

Hi Ford,

You wouldn't happen to still have that spreadsheet to attach by any chance?

It would be awesome to compare to

Thanks so much

Keelin

I thought I had it, but it turns out I can't quite get it to work properly,

Would you mind talking me through the helper column a bit more? I haven't come across something like that before.

Thanks so much,

Keelin