Layered If/Then Sumproduct/Sumif Qty from date range Indirectly through Vlookup

I'm attempting to a multi-layered if/then sumproduct/sumif a quantity within a range of dates indirectly through through a vlookup.

I think I'm failing in the interactions between the indirect references and the range comparisons via vlookup to my criteria page. Right now the function cell is F16 on the DASH and eventually I'll use the variable versions of this formula to reflect many more pages than are currently on the doc.

Attachment: View attachment 1704 Tracker DPTTS x.xlsx


=IF(AND($X$3<>"",$X$4<>""), "Fix",
IF(AND($X$3<>"",$X$4=""), SUMIFS(
INDIRECT($E16&"!$D$16:$D$2005"),
INDIRECT($E16&"!$B$16:$B$2005"),">="&VLOOKUP($X$3,Criteria!$A$6:$C$10,2,FALSE),
INDIRECT($E16&"!$B$16:$B$2005"),"<="&VLOOKUP($X$3,Criteria!$A$6:$C$10,3,FALSE)),
IF(AND($X$3="",$X$4<>""), SUMIFS(INDIRECT($E16&"!$D$16:$D$2005"),INDIRECT($E16&"!$B$16:$B$2005"),$X$4),
IF(AND($X$3="",$X$4=""), COUNTA(INDIRECT($E16&"!$D$16:$D$2005")),""))))


I'd appreciate any help anyone can provide! Thank you!

One problem is this part of the formula is not referencing a column of numbers so no summing can happen..

INDIRECT($E16&"!$D$16:$D$2005")

Within the structure of my formula, can you tell me where that would go?

It is already in your formula. I am saying that in the Johnson tab, column D is not numeric, so when you are using INDIRECT($E16&"!$D$16:$D$2005") in SUMIFS as the summing range, it will not add up anything.


=IF(AND($X$3<>"",$X$4<>""), "Fix",
IF(AND($X$3<>"",$X$4=""), SUMIFS(
INDIRECT($E16&"!$D$16:$D$2005"),
INDIRECT($E16&"!$B$16:$B$2005"),">="&VLOOKUP($X$3,Criteria!$A$6:$C$10,2,FALSE),
INDIRECT($E16&"!$B$16:$B$2005"),"<="&VLOOKUP($X$3,Criteria!$A$6:$C$10,3,FALSE)),
IF(AND($X$3="",$X$4<>""), SUMIFS(INDIRECT($E16&"!$D$16:$D$2005"),INDIRECT($E16&"!$B$16:$B$2005"),$X$4),
IF(AND($X$3="",$X$4=""), COUNTA(INDIRECT($E16&"!$D$16:$D$2005")),""))))

What if I'm attempting to count instances?

In that case COUNTIFS or SUMPRODUCT should do

It's working! Thank you, both!!