Search code examples
excel-formuladynamiccountif

COUNTIF for specific rows across dynamic number of Excel sheets

I am trying to calculate the total number of times "Training" occurs for each team member, across a dynamic number of Excel worksheets (i.e. Months, currently ranging from "Nov 2023" to "Feb 2024"). A new month will be added to the list "Months" every time.

The number of "Training" for a specific team member (A7) should add up to 5:
Nov 23 - 2
Dec 23 - 1
Jan 24 - 1
Feb 24 - 1

I have used INDEX/MATCH to obtain the row referencing the team member A7, COUNTIF to count only the "Training" and INDIRECT/SUMPRODUCT to refence the list "Months". However, the formula I have used below appears to only output the first result, i.e. "2" referring to Nov 23.

=SUMPRODUCT(COUNTIF(INDEX(INDIRECT("'"&Months&"'!$A$1:$Z$20"),MATCH(A7,INDIRECT("'"&Months&"'!$A:$A"),0),0),"Training"))

Please advise how I may return the correct output which should be "5".

Solution

  • Maybe there is a more elegant solution (especially via a Powerquery).

    But if you have Excel 365 you can try this:

    =LET(months,A3:A5,
user,A7,
mergedData,DROP(REDUCE("",months,LAMBDA(r,x,VSTACK(r,INDIRECT("'" & x & "'!A2:Z20")))),1),
dUser,FILTER(mergedData,CHOOSECOLS(mergedData,1)=A7),
SUM(MAP(dUser,LAMBDA(c,IF(c="Training",1,0)))))

    IMPORTANT: you have to update the range to check (I used A2:Z20)!!!!

    mergedData pulls data (via INDIRECT) for each month provided. Then the result is filtered by the user in A7.

    It is not possible to apply COUNTIFS on dUser- therefore the mapping and summing to retrieve the result.

    enter image description here