SUMPRODUCT(COUNTIFS(INDIRECT

[fguar]

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

 

[Toadstool]

Hi Fguar,

Even with that approach I believe it is still treated as a 3D formula and there are only limited functions which can be used, and they don't include SUMPRODUCT or COUNTIFS: Microsoft 3D References

You could design around this by doing the search logic in each sheet and just summing the counts in your query sheet, e.g. I have three worksheets (1st, 2nd and 3rd) which are all identical. In columns A and B I pull the year and reference parameters for a COUNTIFS from the Query sheet and in column C the COUNTIFS result is calculated (NOTE: My test data has three references for 86444, one for year 2002 and two for year 2012).

You can then on the Query sheet enter the year/reference parameters and then use a 3D SUM to pull the search results from the other sheets.

Fguar.xlsx
	A	B	C	D										N
1	Year	Ref	Result
2	2002	86444	1	Year										Refs
3	2012	86444	2	2020										12358
4	0	0	0	2021										57121
5	0	0	0	2002										86444
6	0	0	0	2012										80838
7	0	0	0	2012										94604
8				2003										65054
9				2012										86444
10				2012										86444
11				2003										21880
1st

Cell Formulas
Range	Formula
A2:B7	A2	=Query!A2
C2:C7	C2	=COUNTIFS($D$3:$D$500000,A2,$N$3:$N$500000,B2)

Fguar.xlsx
	A	B	C
1	Year	Ref	Result
2	2002	86444	3
3	2012	86444	6
4			0
5			0
Query

Cell Formulas
Range	Formula
C2:C5	C2	=SUM('1st:3rd'!C2)