Counting in filtered tables/or countif conditions????

[glorg]

Hi all, I'm stumped on how to count how many instances of a name appear in the whole range if it is either:
Filtered so that only type D rows are visible, or
Doing a Countif on the whole range if the corresponding row is a D type row.

Any help greatly appreciated

 

[SunnyAlv]

Hi all, I'm stumped on how to count how many instances of a name appear in the whole range if it is either:
Filtered so that only type D rows are visible, or
Doing a Countif on the whole range if the corresponding row is a D type row.

Any help greatly appreciated

what is your excel version?

 

[glorg]

Apologies.
Microsoft® Excel® for Microsoft 365 MSO (Version 2311 Build 16.0.17029.20068) 32-bit

 

[SunnyAlv]

Like this ?

Cell K4

=FILTER($A$1:$I$5,I1:I5=L2)

Cell O2

=COUNTA(FILTER($B$1:$I$5,I1:I5=L2))

 

[Peter_SSs]

Microsoft 365

Please add that to your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I also suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

count how many instances of a name appear

Do you mean this?

23 12 15.xlsm
	B	C	D	E	F	G	H	I	J	K	L
1	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Type		Name	Name 3
2	Name 4	Name 2	Name 2	Name 3	Name 6	Name 1	Name 7	D		Type	D
3	Name 6	Name 7	Name 6	Name 4	Name 6	Name 3	Name 3	N		Count	3
4	Name 1	Name 3	Name 3	Name 7	Name 1	Name 4	Name 1	D
5	Name 5	Name 5	Name 2	Name 6	Name 1	Name 7	Name 5	N
Count Names

Cell Formulas
Range	Formula
L3	L3	=SUMPRODUCT((B2:H5=L1)*(I2:I5=L2))

 

[glorg]

Thanks Peter, I'll speak to the Admins and see if I can get that add-on added-on.

In the meantime, I feel I haven't explained myself very well. On my current worksheet, it does a countif per name for all shifts on a Fri,sat/sun.
It does one countif per name for the total range. What I need (and explained poorly) is one countif for only N type rows in the range Monday through Thursday.
This way I can show how many times a person is allocated to certain shift types and thus demonstrate many stupid layers of equity across the roster. Kill me now.

 

[Peter_SSs]

Not being familiar with your data or requirement, I'm afraid that does not make any sense to me.
Really need to see some sample data, the expected results and explanation in relation to that specific sample data.

 

[glorg]

I was in Macksville yesterday, could've sat at the Star and shown you in person. I'll work on getting the add-on and get back to you

 

[Peter_SSs]

I was in Macksville yesterday,

Feel free to send a message if you are coming through again.