naridox
New member
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!
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!