Countifs

[omsumo]

Dears, I have a sheet containing a mini table in different groups, and the formula COUNTIFS gives me a #Value!

 

[Kerryx]

Try this instead

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N
2		1	2	3	3	4	5
3
4
5
6
7		1	2	3	3	4	5	5	3	3	4	5	5
8
9		3
10
11
12
13		6
14	5
Sheet1

Cell Formulas
Range	Formula
C13	C13	=COUNTIF(C2:H2,C9)+COUNTIF(C7:N7,B14)

 

[arthurbr]

@omsumo Please add your XL version to your profile. Thx

 

[DanteAmor]

Hi and welcome to MrExcel

The criteria ranges must have the same range of columns, for example:

=COUNTIF(C2:H2,C9,C7:H7,B14)

Or:

=COUNTIF(C2:N2,C9,C7:N7,B14)

 

[omsumo]

Man

Try this instead

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N
2		1	2	3	3	4	5
3
4
5
6
7		1	2	3	3	4	5	5	3	3	4	5	5
8
9		3
10
11
12
13		6
14	5
Sheet1

Cell Formulas
Range	Formula
C13	C13	=COUNTIF(C2:H2,C9)+COUNTIF(C7:N7,B14)

It works, but it gives the wrong number...

 

[omsumo]

Hi and welcome to MrExcel

The criteria ranges must have the same range of columns, for example:

=COUNTIF(C2:H2,C9,C7:H7,B14)

Or:

=COUNTIF(C2:N2,C9,C7:N7,B14)

Thanks Deafr DanteAmor, I can't do it in the same range, it's a multibl table with the same title sorting based on requirements

 

[DanteAmor]

but it gives the wrong number...

I can't do it in the same range, it's a multibl table with the same title sorting based on requirements

You could give a complete example with the result you expect.
Use the XL2BB tool to put a minisheet with the example. Explain what you need to count and what are the criteria to consider in each range of data.

XL2BB Instructions & Download

 

[omsumo]

You could give a complete example with the result you expect.
Use the XL2BB tool to put a minisheet with the example. Explain what you need to count and what are the criteria to consider in each range of data.

XL2BB Instructions & Download

Hello and have a wonderful new year.

 

[DanteAmor]

Hello and happy new year too!

Try:

Dante Amor
	A	B	C	D	E	F	G
1		aa	bb	cc	dd	ee	ff
2	11	Broun	Pink	Black	Black	Gray	Broun
3	22	White	White	Black	Pink	White	Black
4	33	Pink	Pink	Gray	Gray	Broun	Gray
5	44	Black	Broun	Gray	Pink	White	Broun
6	55	Gray	Broun	Broun	Black	Broun	White
7
8			Broun	White	Pink	Black	Gray
9		aa	1	1	1	1	1
10		bb	2	1	2	0	0
11		cc	1	0	0	2	2
12		dd	0	0	2	2	1
13		ee	2	2	0	0	1
Hoja1

Cell Formulas
Range	Formula
C9:G13	C9	=COUNTIF(INDEX($B$2:$G$6,0,MATCH($B9,$B$1:$G$1)),C$8)

 

[omsumo]

Hello and happy new year too!

Try:

Dante Amor
	A	B	C	D	E	F	G
1		aa	bb	cc	dd	ee	ff
2	11	Broun	Pink	Black	Black	Gray	Broun
3	22	White	White	Black	Pink	White	Black
4	33	Pink	Pink	Gray	Gray	Broun	Gray
5	44	Black	Broun	Gray	Pink	White	Broun
6	55	Gray	Broun	Broun	Black	Broun	White
7
8			Broun	White	Pink	Black	Gray
9		aa	1	1	1	1	1
10		bb	2	1	2	0	0
11		cc	1	0	0	2	2
12		dd	0	0	2	2	1
13		ee	2	2	0	0	1
Hoja1

Cell Formulas
Range	Formula
C9:G13	C9	=COUNTIF(INDEX($B$2:$G$6,0,MATCH($B9,$B$1:$G$1)),C$8)

Many Thanks its works perfectly ...