COUNTIFS

[carlclements]

Hi,
Is there a COUNTIF formula that counts the number of cells with data (numbers) in group of columns based on a reference data point in column B?

My spreadsheet has 2 tabs. First tab has a list of names in column B then in columns C onwards are descending years. Each row has a name in column B and a number under one of the years (columns) with the rest of the columns empty

On another tab, I reference the Names in a cell in column B and want to count the frequency of cells with a number in it with that Name in the list

The below formula works for the first set of years in columns D-H but how do I adjust the formula to look at a different set of columns, which is to only count cells in columns I-L and exclude those in B-H?

=COUNTIFS('Data (2)'!B:H,'Values'!B3)

 

[RoryA]

Welcome to the forum.

I don't understand the relation between your description and the formula you posted. That formula just counts cells in columns B:H that contains the value in B3 in the Values sheet. From your description, you might as well have used:

=COUNTIFS('Data (2)'!B:B,'Values'!B3)

since only column B contains names. The formula takes no account of whether there are numbers in columns C:H or not.

 

[RobP]

Indeed as Rory suggested...

So I'm not sure if this is what you need ?

Assuming you do a lookup in Col A for the name in B3, then count how many entries are across his row ?

Book1
	A	B	C	D	E	F	G	H
1
2		2005	2004	2003	2002	2001	2000
3	Fred	2
4	Bill			4
5	John		3
6	Ron						6
7	Dave		5
8
9
Data

Book1
	A	B	C	D
1
2
3		Fred	1
4
5
Values

Cell Formulas
Range	Formula
C3	C3	=COUNT(XLOOKUP(B3,Data!A3:A7,Data!B3:H7,""))

 

[carlclements]

Sorry, i didn't explain well. Here is a sample of the data

And I would like a formula that counts how many cells within a column range are populated with a number for each name. I have manually entered below

 

[RoryA]

You could use FILTER:

=COUNT(FILTER('Data (2)'!$I$2:$L$1000,'Data (2)'!$B$2:$B$1000='Values'!B3))

 

[RobP]

Maybe something like this (formulas different in each year range, and type the name manually into cell A10)

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1		2024	2023	2022	2021	2020	2019	2018	2017	2016	2015	2014	2013	2012	2011	2010	2009	2008	2007	2006	2005	2004	2003	2002	2001	2000
2	Fred		165		155.5		165	213.5	155		194
3	Fred		200						149.6
4	Bill								145
5	Bill
6
7
8
9		2020-now	2015-2019	2010-2014	2005-2009	2000-2004
10	Fred	3	5	0	0	0
11
12
13
Sheet2

Cell Formulas
Range	Formula
B10	B10	=COUNT(FILTER(B2:F5,A2:A5=A10,""))
C10	C10	=COUNT(FILTER(G2:K5,A2:A5=A10,""))
D10	D10	=COUNT(FILTER(L2:P5,A2:A5=A10,""))
E10	E10	=COUNT(FILTER(Q2:U5,A2:A5=A10,""))
F10	F10	=COUNT(FILTER(V2:Z5,A2:A5=A10,""))

Sorry, forgot you had 2 sheets... so heres an example:

Book1
	A	B	C	D
1
2			2020-now	etc..
3		Fred	3
4
Values

Cell Formulas
Range	Formula
C3	C3	=COUNT(FILTER(Data!B2:F5,Data!A2:A5=B3,""))

But like Rory says really ...

 

[carlclements]

thanks all for your help. working as needed now