# SUMPRODUCT(COUNTIFS(INDIRECT

#### fguar

##### New Member
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

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

##### Well-known Member
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
ABCDN
1YearRefResult
22002864441YearRefs
32012864442202012358
4000202157121
5000200286444
6000201280838
7000201294604
8200365054
9201286444
10201286444
11200321880
1st
Cell Formulas
RangeFormula
A2:B7A2=Query!A2
C2:C7C2=COUNTIFS(\$D\$3:\$D\$500000,A2,\$N\$3:\$N\$500000,B2)

Fguar.xlsx
ABC
1YearRefResult
22002864443
32012864446
40
50
Query
Cell Formulas
RangeFormula
C2:C5C2=SUM('1st:3rd'!C2)

Replies
1
Views
211
Replies
5
Views
1K
Replies
1
Views
187
Replies
0
Views
109
Replies
15
Views
819

1,185,987
Messages
5,955,175
Members
438,186
Latest member
subvtech

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back