The problem is that SUMIFS, COUNTIFS, AVERAGEIFS, etc.
are in agroup of functionsthat do not support 3D references.
The formula in F5 is:
Notice we areconcatenatingthe sheet names to the ranges we need to work with.
To coerce these values into valid cell references we use theINDIRECT function.
INDIRECT converts the text values to valid references and returns the result to the SUMIF function for therangeandsum_rangearguments.
Because thenamed range"sheets" containsthreevalues, SUMIF actually runsthreetimes, one for each reference.
This number is the total number of hours logged to the Alpha project in all three worksheets.
InLegacy Excel,SUMPRODUCT is used frequentlybecause it can handlearraysnatively without requiring Ctrl-Shift-Enter.
Criteria can be applied to dates, numbers, and text.
INDIRECT is useful when you want to assemble a text value that can be used as a valid reference.
Related videos
How to create 3D references
