SumProduct and Sumif formula with two Named Ranges not working.

[excelisagony]

I'm at my wits end.

The following formula works perfectly:

=SUMPRODUCT((SUMIFS(E:E,C:C,DEVELOPMENT,B:B,"1660-Company")))

However, if I add a second named range it gives me an incorrect value:

=SUMPRODUCT((SUMIFS(E:E,C:C,DEVELOPMENT, D:D, TOTALEMPCOMP, B:B,"1660-Company")))

Any thoughts on what I am doing incorrectly?

Thank you, Excel Experts!

 

[snjpverma]

You formula seems perfect to me.

Can you post a small sample using XL2BB tool so that we can have a look at your data and find out what is wrong with it?

 

[Marcelo Branco]

If both DEVELOPMENT and TOTALEMPCOMP are vertical (or horizontal) named ranges the formula doesn't work properly. One should be horizontal and the other vertical.

See if this works
=SUMPRODUCT(SUMIFS(E:E,C:C,DEVELOPMENT,D:D,TRANSPOSE(TOTALEMPCOMP),B:B,"1660-Company"))
confirmed with Control+Shift+Enter

M.