I have the name of the start of a range in Q1, name of the end of the range in Q2 (for example: Q1 = "Data!$Y$2", Q2 = "Data!$Y$2000"). I have a criteria in F2 (for example: "Canada").
I have some existing COUNTIFS and SUMPRODUCTS set up like below (using named ranges/cells):
=COUNTIFS(EmpType,"Regular",HireDate,"<="&SOP)
=SUMPRODUCT(--(EmpType="Regular"),--(LatestHire<=SOP))
I want to add new criteria, to product something like this:
=COUNTIFS(EmpType,"Regular",HireDate,"<="&SOP,INDIRECT($Q$1):INDIRECT($Q$2),$F$2)
=SUMPRODUCT(--(EmpType="Regular"),--(LatestHire<=SOP),--(INDIRECT($Q$1):INDIRECT($Q$2)=$F$2))
However, these produce an error (#VALUE!).
This is despite the fact that formulas ONLY containing these arguments work correctly:
<colgroup><col style="width:96pt" width="128"> </colgroup><tbody>
</tbody>
Does anyone know what the issue might be?
Thanks!
I have some existing COUNTIFS and SUMPRODUCTS set up like below (using named ranges/cells):
=COUNTIFS(EmpType,"Regular",HireDate,"<="&SOP)
=SUMPRODUCT(--(EmpType="Regular"),--(LatestHire<=SOP))
I want to add new criteria, to product something like this:
=COUNTIFS(EmpType,"Regular",HireDate,"<="&SOP,INDIRECT($Q$1):INDIRECT($Q$2),$F$2)
=SUMPRODUCT(--(EmpType="Regular"),--(LatestHire<=SOP),--(INDIRECT($Q$1):INDIRECT($Q$2)=$F$2))
However, these produce an error (#VALUE!).
This is despite the fact that formulas ONLY containing these arguments work correctly:
=COUNTIFS(INDIRECT($Q$1):INDIRECT($Q$2),$F$2) |
=SUMPRODUCT(--(INDIRECT($Q$1):INDIRECT($Q$2)=$F$2)) |
<colgroup><col style="width:96pt" width="128"> </colgroup><tbody>
</tbody>
Does anyone know what the issue might be?
Thanks!