3 CRITERIA USING COUNTIFS and INDIRECT

Interduco

New Member
Joined
Dec 1, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi everyone,

Hope everyone is well and healthy.

I'm having difficulty in writing a dynamic formula to count the number of competencies based on three criteria.

The formula will be written in column H6 onwards in the 'competency tracker' sheet while the list of competencies per subject are written in different sheets on the same workbook.

The table for the competencies already have table names and are written on the same columns (i.e., column A for the grade level, column B for the quarter and column C for the competencies)

The formula that I used, following the criteria Subject is English, Grade level is I and Quarter is 3rd, was:

=COUNTIFS(INDIRECT($E6&"!C:C"),1,INDIRECT($E6 & "!A:A"),'Competency Tracker'!$F6,INDIRECT($E6 & "!B:B"),'Competency Tracker'!G6)

The answer should be 6.

Any help will be very much appreciated. Thank you very much.View attachment 52460


View attachment 52462
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
should work - i setup, a rough spreadsheet and used your formula (as the link to attachment did not work)
see dropbox file attached - will only be available for about a week

you may want to check the spelling , any spaces - if numbers are real numbers or TEXT entered numbers
as i say it should work as written , and return the correct result - if all criterial work OK
try manually entering everything into a countif , so dont use the indirect () and manually enter criteria

Main Sheet with formula
Tracker-ETAF.xlsx
ABCDEFG
1COUNTIFS(INDIRECT($E6&"!C:C"),1,INDIRECT($E6 & "!A:A"),'Competency Tracker'!$F6,INDIRECT($E6 & "!B:B"),'Competency Tracker'!G6)
2
3
4
5Sheet Nane
6data1
7
8Result
93
10
11
Sheet1
Cell Formulas
RangeFormula
E9E9=COUNTIFS(INDIRECT($E6&"!C:C"),1,INDIRECT($E6 & "!A:A"),'Competency Tracker'!$F6,INDIRECT($E6 & "!B:B"),'Competency Tracker'!G6)


Tracker Sheet
Tracker-ETAF.xlsx
ABCDEFGHI
1
2
3
4
5
63rdenglish
7
8
9
Competency Tracker


Data Sheet for the count data
Tracker-ETAF.xlsx
ABCD
1F6G6grade
23rdenglish1
33rdenglish1
43rdenglish1
53rdfrench1
63
74
Data1


 
Upvote 0
Solution
What kind of error announcement?

a quick tip:
Try to count with range 1:100 first, then 1:200 next,... till getting error
 
Upvote 0
Safer to enclose the sheet names in quotes:

=COUNTIFS(INDIRECT("'"&$E6&"'!C:C"),1,INDIRECT("'"&$E6 & "'!A:A"),'Competency Tracker'!$F6,INDIRECT("'"&$E6 & "'!B:B"),'Competency Tracker'!G6)
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

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
Back
Top