Hi,
I am attempting to use two countif conditions across multiple sheets (which I have named "Tabs" in name manager) to count how many times a job is raised for an item with a certain reference number in a particular year of its ownership.
I have used a no. of forum posts to get me to where I am and named my sheet range (Tabs) and created the following formula but I keep getting VALUE errors.
The issue has arisen because there are so many jobs within the export (2 mil+) that I haven't been able to create one sheet with them all on to do a COUNTIFS easily. Instead, I have had to split the jobs into years and now am trying to do the same formula across the separate sheets.
N3:N500000 = Item Reference Numbers
D3 = Item Reference Number to Match
D3:D500000 = Years of Jobs Raised
K1 =Year to Match
SUMPRODUCT(COUNTIFS(INDIRECT("'"&Tabs&"'!N3:N500000"),$D3),INDIRECT("'"&Tabs&"'!D3:D500000"),K$1)
Thank you
I am attempting to use two countif conditions across multiple sheets (which I have named "Tabs" in name manager) to count how many times a job is raised for an item with a certain reference number in a particular year of its ownership.
I have used a no. of forum posts to get me to where I am and named my sheet range (Tabs) and created the following formula but I keep getting VALUE errors.
The issue has arisen because there are so many jobs within the export (2 mil+) that I haven't been able to create one sheet with them all on to do a COUNTIFS easily. Instead, I have had to split the jobs into years and now am trying to do the same formula across the separate sheets.
N3:N500000 = Item Reference Numbers
D3 = Item Reference Number to Match
D3:D500000 = Years of Jobs Raised
K1 =Year to Match
SUMPRODUCT(COUNTIFS(INDIRECT("'"&Tabs&"'!N3:N500000"),$D3),INDIRECT("'"&Tabs&"'!D3:D500000"),K$1)
Thank you