COUNTIF values on another sheet based on specific value

[jmacl]

Hi I am working on a vacation tracker and need find the name of an employee in a table then count the number of times "H" is in the row of the name.
Basically i need to find the row for a work on another sheet then count the number of holidays they have taken, this will then be expanded to sick days and other absence based on a character reference in the table.
I am have tired the following but get an error:

=COUNTIF(INDIRECT(&"'Jan2021'!"&MATCH(A10,'Jan2021'!A9:A44,0)&":'Jan2021'!"&MATCH(A10,'Jan2021'!A9:A44,0)&"),"H")

I will then replicate this across multiple sheets

 

[Alex Blakenburg]

I am not sure that I am understanding what you are trying to achieve.
You seem to be looking in the same column (A) for both the name (which I assume is in A10) and for H.

If the Name on the Jan2021 sheet is in A9:A44
and the value "H" is on the Jan2021 sheet is in B9:B44\
Then your formula would look something like this.

=COUNTIFS(INDIRECT("'Jan2021'!" & "$A$9:$A$44"),A10,INDIRECT("'Jan2021'!" & "$B$9:$B$44"),"H")

However hardcoding Jan2021 would make the Indirect function an unnecessary complication, so I would assume it will be driven by a cell.
So if you put the Jan2021 as a string value in B1 then you would need:-

=COUNTIFS(INDIRECT("'"&$B$1&"'!" & "$A$9:$A$44"),A10,INDIRECT("'"&$B$1&"'!" &"$B$9:$B$44"),"H")

If you are going to put the Jan 2021 into B1 as a date, you would need this:-

=COUNTIFS(INDIRECT("'"&TEXT($B$1,"mmmyyyy")&"'!" & "$A$9:$A$44"),A10,INDIRECT("'"&TEXT($B$1,"mmmyyyy")&"'!" &"$B$9:$B$44"),"H")

 

[jmacl]

Thank you Alex for your reply but I don't think we are there yet, you have highlighted something I was not clear on, the "H" values will be in columns B through until AF.
So to reconfirm, I need to find a row in column A based on a specific value (name) then count the number of "H" in that row in columns B through to AF.

 

[Marcelo Branco]

I don't know why you are using INDIRECT. Is there a list of sheet names somewhere?

To get the count in the sheet named Jan2021 try
=COUNTIF(INDEX('Jan2021'!$B$9:$AF$44,MATCH(A10,'Jan2021'!$A$9:$A$44,0),0),"H")

M.

 

[jmacl]

I don't know why you are using INDIRECT. Is there a list of sheet names somewhere?

To get the count in the sheet named Jan2021 try
=COUNTIF(INDEX('Jan2021'!$B$9:$AF$44,MATCH(A10,'Jan2021'!$A$9:$A$44,0),0),"H")

M.

Hi Marcelo, this is great!, I have a list starting AR3:AR14 what do you have in mind

 

[Alex Blakenburg]

You could put this in AS3 and copy it down.

=IFERROR(COUNTIFS(INDEX(INDIRECT("'"&TEXT($AR3,"mmmyyyy")&"'!" & "$B$9:$AF$44"),MATCH($A$10,INDIRECT("'"&TEXT($AR3,"mmmyyyy")&"'!" &"$A$9:$A$44"),0),0),"H"),"")

20210413 Countifs with Indirect.xlsx
	AR	AS
1
2
3	Jan-21	5
4	Feb-21
5	Mar-21
6	Apr-21
7	May-21
8	Jun-21
9	Jul-21
10	Aug-21
11	Sep-21
12	Oct-21
13	Nov-21
14	Dec-21
15
Lookup

Cell Formulas
Range	Formula
AS3:AS14	AS3	=IFERROR(COUNTIFS(INDEX(INDIRECT("'"&TEXT($AR3,"mmmyyyy")&"'!" & "$B$9:$AF$44"),MATCH($A$10,INDIRECT("'"&TEXT($AR3,"mmmyyyy")&"'!" &"$A$9:$A$44"),0),0),"H"),"")

 

[Marcelo Branco]

Hi Marcelo, this is great!, I have a list starting AR3:AR14 what do you have in mind

Something like

Pasta1
	AR	AS
1
2
3	Jan2021	6
4	Feb2021
5	Mar2021
6	Apr2021
7	May2021
8	Jun2021
9	Jul2021
10	Aug2021
11	Sep2021
12	Oct2021
13	Nov2021
14	Dec2021
Plan1

Cell Formulas
Range	Formula
AS3:AS14	AS3	=IFERROR(COUNTIF(INDEX(INDIRECT(AR3&"!$B$9:$AF$44"),MATCH(A$10,INDIRECT(AR3&"!$A$9:$A$44"),0),0),"H"),"")

M.

 

[jmacl]

Excellent thanks for your help on this, I have everything i need on this.

 

[Marcelo Branco]

You are welcome. Glad to help.

M.