CountIf across columns, as an array functions, not concatenated.

[carpetony]

I have one large table, I want to summarize via CountIf, the number of entries in other columns associated to the specific companies.

I need to create the summary table on the fly with VBA, and was hoping to have to input the fewest functions necessary, and was hoping it could do a Vertical and Horizontal Spill of my CountIf functions.

I can create the dynamic list of UNIQUE(Companies) in Column L, and now want to dynamically get the counts of the subsequent columns D thru H. CountIf works with # (hash) to dynamically fill in my values for a single column, but it falters for the subsequent columns. I was wondering if there was something I was missing or could do. I want to leave out to of the columns, and was planning on doing a ChooseCols to omit those, rather than dragging the function or entering it multiple times.

This spill functions works. and I can drag to the right. . .
M2 is =COUNTIFS($B$3:$B$200,$L2#,D$3:D$200,"<>")

However. . .
N2 is =COUNTIFS($B$3:$B$200,$L2#,D$3:H$200,"<>")
Returns #VALUE! errors for the Spill.
Looking at it, I can kind of understand why it doesn't. . .but can't think of anyway to get it to work.

Not the end of the world, this would just be a nice thing, and ended up being a rabbit hole.

Thanks!

 

[shift-del]

However. . .
N2 is =COUNTIFS($B$3:$B$200,$L2#,D$3:H$200,"<>")
Returns #VALUE! errors for the Spill.

Your ranges are not the same size.

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1												Unique	Header1	Header2	Header3	Header4	Header5
2		List		Header1	Header2	Header3	Header4	Header5				A	2	2	2	2	1
3		A		1	2	2	3	2				B	0	1	1	1	0
4		B			4	3	1					C	2	2	1	1	1
5		C		3	6
6		A		4	8	7	1
7		B
8		C		6	12	13	9	8
9
Sheet4

Cell Formulas
Range	Formula
L2:L4	L2	=UNIQUE(B3:B8)
M2:Q4	M2	=COUNTIFS(B3:B8,L2#,OFFSET(D3:D8,0,{0,1,2,3,4}),"<>")
Dynamic array formulas.

In the array constant {0,1,2,3,4}, you can pick the columns you want (0 is the first column). You can also do this with the BYCOL function, but someone else will need to show you how to do that.