Forum Discussion
Rolling Calendar COUNTIFS question
I have a table that lists dates across the top row and a column listing items on the side. Image below. This is meant to be a rolling calendar, with new dates being added all the time. I'm ...
Given that Sheet1!A1 is blank, you may try this formula:
=SUMPRODUCT(--(INDEX(ItemData,1,
MATCH(INDEX(DateLabels,COUNT(DateLabels))-13,DateLabels,1)):
INDEX(ItemData,COUNTA(ItemLabels),COUNT(DateLabels))="a"))
The defined names are:
DateLabels=Sheet1!$B$1:INDEX(Sheet1!$1:$1,COUNT(Sheet1!$1:$1)+1)
ItemData=Sheet1!$B$2:INDEX(Sheet1!$B:$XFD,COUNTA(Sheet1!$A:$A)+1,COUNT(Sheet1!$1:$1)+1)
ItemLabels=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A)+1)
=SUMPRODUCT(--(INDEX(ItemData,1,
MATCH(INDEX(DateLabels,COUNT(DateLabels))-13,DateLabels,1)):
INDEX(ItemData,COUNTA(ItemLabels),COUNT(DateLabels))="a"))
The defined names are:
DateLabels=Sheet1!$B$1:INDEX(Sheet1!$1:$1,COUNT(Sheet1!$1:$1)+1)
ItemData=Sheet1!$B$2:INDEX(Sheet1!$B:$XFD,COUNTA(Sheet1!$A:$A)+1,COUNT(Sheet1!$1:$1)+1)
ItemLabels=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A)+1)