SUMIFS statement with 2 array formular

[EXCLstuck]

I'm trying to make this formula work...

=SUM(SUMIFS('SHEET1'!$T:$T,'SHEET1'!$N:$N,{"January","February","March"},'SHEET1'!$A:$A,AB57,'SHEET1'!$L:$L,{"MON","TUE","WED"}))

If I test the two parts independently like this

=SUM(SUMIFS('SHEET1'!$T:$T,'SHEET1'!$N:$N,{"January","February","March"},'SHEET1'!$A:$A,AB57))
or
=SUM(SUMIFS('SHEET1'!$T:$T,'SHEET1'!$A:$A,AB57,'SHEET1'!$L:$L,{"MON","TUE","WED"}))

I get the answers I expect, but when I combine them together I get 0 as the sum which is not right.

Can anyone advice how I can solve this?

 

[Marcelo Branco]

Welcome to Mr Excel

See if this works (observe the ; in the second constant array)

=SUM(SUMIFS('PRINT'!$T:$T,'PRINT DDS'!$N:$N,{"January","February","March"},'PRINT'!$A:$A,AB57,'PRINT'!$L:$L,{"MON";"TUE";"WED"}))

Hope this helps

M.

 

[EXCLstuck]

Thanks for the quick reply Mr Excel, but when I try that I'm still having the same problem. Any other ideas?

(PS using Excel 2013)

 

[pgc01]

Hi

If you replace the comma with the semicolon in the second array, like Marcelo posted, it should work, unless the result of the 2 combined is indeed 0.

Can you post some data in columns A, L, N, T, just 4-5 rows is enough, where the formula does not work?

 

[EXCLstuck]

Hi - great news! Yes your fix worked! (just had one of the references on the wrong cell when I rewrote it!)

Thanks for your super speedy (and excellent) advice!

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.