SUMIFS with Array Constant Fails

Hello folks, many hours trying to figure what I am missing. Notice in the formula below that one of the SUMIFS criteria is an array constant {"MCH","IGT","STT"}

Please Login or Register  to view this content.

That works fine. However, assume the array constant is elsewhere in a cell named 'fPlants'. I cannot figure out why the formula below will not work - it sums to zero which is incorrect

Please Login or Register  to view this content.

If I step through the formula using Evaluate Formula, notice below that there are extra quotes added to the array constant

Formula.jpg

What am I doing wrong? Any advise would be appreciated

Can you post a copy of a sample of the sheet, not a jpg?

Try this..
I think I will do..

Formula:

Please Login or Register  to view this content.

Don't forget to click *

Welcome to the Forum.

This is because your first formula is treated as a regular formula since you are passing the criteria as a direct array of values like {"MCH","IGT","STT"}, but when you pass the criteria as fPlants, the formula needs to break this names range into array of values like {"MCH","IGT","STT"}, therefore in this case the formula becomes an array formula.
So with your second formula you need to confirm it with Ctrl+Shift+Enter instead of just Enter. i.e. select the formula cell, press F2 (Function Key), hold down the Ctrl+Shift together and then press Enter.
When you enter an array formula correctly, you will notice in the formula bar that the formula gets surrounded by the Curly Brackets and if you don't see the curly brackets in the formula bar, try again to confirm it as an array formula.

Sorry if I explained it poorly.

Instead of using an array constant use cells to hold the criteria.

A1 = MCH
A2 = IGT
A3 = STT

B1 = Budget

Then your formula would be:

=SUMPRODUCT(SUMIFS(tData[Amount],tData[Plant],$A$1:$A$3,tData[Category],$B$1,tData[Date],X$7))

Originally Posted by zCJH4254

I tried each 'solution' and the only one that worked was Tony Valko's .

That happens a lot!

I retained the SUM(SUMIF) structure and used a named range.

If using SUM(SUMIF you'll have to array enter the formula.

Array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.