fguar

New Member
Joined
Jul 15, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,440
Office Version
  1. 2016
Platform
  1. Windows
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)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,825
Messages
5,766,659
Members
425,367
Latest member
Boboka

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top