COUNTIFS Question...

[Aitch]

How can I count the number of rows which all have the same name?
- so columns B,C,D,E are all equal so the result below would be 2

I've tried a bunch of COUNTIFS variations - but no luck!

Thanks for any help!

A​	B​	C​	D​	E​	F​
2​	Tom		Joe	Joe
	Pete	John	Bob	Tom
	Tom	Tom	Tom	Tom
	Bob	Bob	Joe	Joe
	Pete	Tom	Tom	John
	Joe	Joe	Joe	Joe
	Bob	Joe	John	Jack

 

[Dave Patton]

T202012a.xlsm
	A	B	C	D	E
1	FALSE	Tom		Joe	Joe
2	TRUE	Tom	Tom	Tom	Tom
3	TRUE	Tom	Tom	Tom	Tom
4	FALSE	Tom		Tom	Tom
5	FALSE	Tom		Joe	Joe
6	FALSE	Tom		Joe	Joe
7
8	2	2
5b

Cell Formulas
Range	Formula
A1:A6	A1	=COUNTIF(B1:E1,B1)=4
A8	A8	=COUNTIF(A1:A6,TRUE)
B8	B8	=SUMPRODUCT(--(B1:B6=C1:C6),--(B1:B6=D1:D6),--(B1:B6=E1:E6))

 

[jasonb75]

Chaining the ranges like this appears to work

Book1
	A	B	C	D	E
1	2	Tom		Joe	Joe
2		Pete	John	Bob	Tom
3		Tom	Tom	Tom	Tom
4		Bob	Bob	Joe	Joe
5		Pete	Tom	Tom	John
6		Joe	Joe	Joe	Joe
7		Bob	Joe	John	Jack
Sheet5

Cell Formulas
Range	Formula
A1	A1	=SUMPRODUCT(COUNTIFS(B1:B7,C1:C7,C1:C7,D1:D7,D1:D7,E1:E7))

 

[Aitch]

How can I count the number of rows which all have the same name?
- so columns B,C,D,E are all equal so the result below would be 2

I've tried a bunch of COUNTIFS variations - but no luck!

Thanks for any help!

A​	B​	C​	D​	E​	F​
2​	Tom		Joe	Joe
	Pete	John	Bob	Tom
	Tom	Tom	Tom	Tom
	Pete	Tom	Tom	John
	Joe	Joe	Joe	Joe
	Bob	Joe	John	Jack

Great, thanks!

Is there a way to ignore rows which all have blank cells - like row 4 above

 

[jasonb75]

I assume formulas in those cells as a row with all empty cells would not be counted. The additional wildcard criteria pair in this version will look for cells with a length of at least one character.

=SUMPRODUCT(COUNTIFS(B1:B7,"?*",B1:B7,C1:C7,C1:C7,D1:D7,D1:D7,E1:E7))

 

[Dave Patton]

I may not be thinking clearly this morning.
I am using my laptop with Excel 2010.
Why is the test for a blank row necessary?
Why does my suggestion evaluate differently with the second example?

T202012a.xlsm
	A	B	C	D	E	F	G	H	I	J
1	2	Tom		Joe	Joe			rows with same Text
2	FALSE	Pete	John	Bob	Tom	2
3	TRUE	Tom	Tom	Tom	Tom	2	Why is the test for a blank row necessary?
4	FALSE
5	FALSE		Tom	Tom	John
6	TRUE	Joe	Joe	Joe	Joe
7	FALSE	Bob	Joe	John	Jack
8	2
9
10
11							With Excel 2010, I get 5 (F13) versus 3 (G13)
12							Cells evaluate to see J13:J14
13	FALSE	Tom		Joe	Joe	5	3		G13	{0;1;1;0;0;1;0},{0;1;1;1;0;1;0},{0;1;1;1;0;1;0}
14	TRUE	Tom	Tom	Tom	Tom				F13	{0;0;2;2;0;0;1}
15	TRUE	Tom	Tom	Tom	Tom
16	FALSE	Tom		Tom	Tom
17	FALSE	Tom		Joe	Joe
18	TRUE	Joe	Joe	Joe	Joe
19	FALSE	Tom		Joe	Joe
20	3
21
5b

Cell Formulas
Range	Formula
F2,F13	F2	=SUMPRODUCT(COUNTIFS(B1:B7,C1:C7,C1:C7,D1:D7,D1:D7,E1:E7))
F3	F3	=SUMPRODUCT(COUNTIFS(B1:B7,"?*",B1:B7,C1:C7,C1:C7,D1:D7,D1:D7,E1:E7))
A1	A1	=SUMPRODUCT(COUNTIFS(B1:B7,C1:C7,C1:C7,D1:D7,D1:D7,E1:E7))
A13:A19,A2:A7	A2	=COUNTIF(B2:E2,B2)=4
A8,A20	A8	=COUNTIF(A1:A7,TRUE)
G13	G13	=SUMPRODUCT(--(B13:B19=C13:C19),--(B13:B19=D13:D19),--(B13:B19=E13:E19))