SUMIFS Formula (Syntax question)

[Keelin]

Sorry All,

I have it working now but cant delete the post?


..........

Hi all,

I have a relatively tricky sum formula that works perfectly (there was a bit of trial and error on my part as I am no big expert)

=SUMIFS(INDEX(MATRIX!$BD$1:$CN$2000,0,MATCH(D$53&D$54,MATRIX!$BD$1:$CN$1&MATRIX!$BD$2:$CN$2,0)),MATRIX!$Q$1:$Q$2000,'Marketing Profile'!$B73,MATRIX!$K$1:$K$2000,$B$54)

However I realised that I now need to exclude some records from the sum. These records are flagged as Contractors in Column O of the spreadsheet but I cant get the syntax right

=SUMIFS(INDEX(MATRIX!$BD$1:$CN$2000,0,MATCH(E$53&E$54,MATRIX!$BD$1:$CN$1&MATRIX!$BD$2:$CN$2,0)),MATRIX!$Q$1:$Q$2000,'Marketing Profile'!$B72,MATRIX!$K$1:$K$2000,$B$54,MATRIX!$O$1:$O$2000,"<> Contractors")

Does anyone know what the right syntax would be for this?

Thanks,

Keelin

[daddylonglegs]

Try removing the space and using just

"<>Contractors"

[TMS]

Difficult to know for sure without any data. However, those (as far as I can tell) should be Array Formulae and committed with Ctrl-Shift-Enter.

Just putting them into and empty workbook and naming the sheets appropriately, I get 0 for both but, as I say, no data to check it.

Short story, as Array Formulae, they produce a result, just don't know if it's the right result.

Regards, TMS

[Keelin]

Thank-you all,

Daddylonglegs (love the name I think you were right about the spacing.

And as you said TMS I 'think' I have it working now but must go through all the checks...although at least now it is bringing back data - and hopefully fingers crossed its the right data!

Thanks so much

K

[ben_hensel]

I have it working now but cant delete the post?

Don't delete it, marked it [SOLVED] instead.

To mark your thread solved do the following:
New Method
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved

Old Method
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save