Using COUNTIFS alongside HLOOKUP

[deacon10]

Hi all,

I am hoping one of you great people can help with a simple problem (I think) that I can’t get my head around.

I am trying to use a COUNTIFS statement alongside a HLOOKUP.

In Sheet 1 in columns C to F, I want to count the ‘Modules’ (Test1 to Test8) in Sheet 2 that = 1. The First condition is if Sheet1 column A is 1 in Sheet2 column J, and the third is if Sheet1 C3 is 1 in Sheet2 column N.
The items in red below probably best show the conditions:-

I have this formula that works, (an example in Sheet1 C4)

=COUNTIFS(Sheet2!J:J,1,Sheet2!I:I,"Slice1",Sheet2!N:N,1)

Unfortunately, the above formula is quite manual for setting up columns J & N – particularly if a new Module is added. Ideally, I would like some type of HLOOKUP for conditions 1 & 3 to do the count

Any ideas greatly appreciated

 

[Alex Blakenburg]

You will have a better chance of getting a reponse if you provide some sample data using XL2BB.
It saves anyone wanting to respond from having to manually recreate the data.

See if this works for you:

20230526 Multi Dimension Match deacon10.xlsx
	A	B	C	D	E	F
1
2
3	Module	Slice	Test5	Test6	Test7	Test8
4	test1	Slice1	2	1	2	2
5	test2	Slice1	0	0	0	0
6	test3	Slice2	0	0	0	0
7	test4	Slice3	0	0	0	1
Sheet1

Cell Formulas
Range	Formula
C4:F7	C4	=COUNTIFS( INDEX(Sheet2!$H$3:$Q$20000,0,MATCH($A4,Sheet2!$H$3:$Q$3,0)),1, Sheet2!$I$3:$I$20000,$B4, INDEX(Sheet2!$H$3:$Q$20000,0,MATCH(C$3,Sheet2!$H$3:$Q$3,0)),1)

Sheet2 - for anyone else who might want to answer

20230526 Multi Dimension Match deacon10.xlsx
	H	I	J	K	L	M	N	O	P	Q
2
3	Acc	Slice	Test1	Test2	Test3	Test4	Test5	Test6	Test7	Test8
4	1234	Slice1	1				1	1	1	1
5	1535	Slice1	1				1		1	1
6	4563	Slice3						1
7	1538	Slice3				1				1
8
Sheet2

 

[DanteAmor]

Try:

Libro1
	A	B	C	D	E	F
1
2
3	Module	Slice	Test5	Test6	Test7	Test8
4	test1	Slice1	2	1	2	2
5	test2	Slice1	0	0	0	0
6	test3	Slice2	0	0	0	0
7	test4	Slice3	0	0	0	1
Sheet1

Cell Formulas
Range	Formula
C4:F7	C4	=COUNTIFS(Sheet2!$I:$I,$B4,INDEX(Sheet2!$J:$Q,0,MATCH($A4,Sheet2!$J$1:$Q$1,0)),1,INDEX(Sheet2!$J:$Q,0,MATCH(C$3,Sheet2!$J$1:$Q$1,0)),1)

It's a pity that the row numbers are not visible in your images.

I'm considering row 1 for the headers on sheet2:

Libro1
	I	J	K	L	M	N	O	P	Q
1	Slice	Test1	Test2	Test3	Test4	Test5	Test6	Test7	Test8
2	Slice1	1				1	1	1	1
3	Slice1	1				1		1	1
4	Slice3						1
5	Slice3				1				1
Sheet2

If the headers are on another row, then change the number 1 in this part of the formula:
=COUNTIFS(Sheet2!$I:$I,$B4,INDEX(Sheet2!$J:$Q,0,MATCH($A4,Sheet2!$J$1:$Q$1,0)),1,INDEX(Sheet2!$J:$Q,0,MATCH(C$3,Sheet2!$J$1:$Q$1,0)),1)

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------​

 

[deacon10]

Try:

Libro1
	A	B	C	D	E	F
1
2
3	Module	Slice	Test5	Test6	Test7	Test8
4	test1	Slice1	2	1	2	2
5	test2	Slice1	0	0	0	0
6	test3	Slice2	0	0	0	0
7	test4	Slice3	0	0	0	1
Sheet1

Cell Formulas
Range	Formula
C4:F7	C4	=COUNTIFS(Sheet2!$I:$I,$B4,INDEX(Sheet2!$J:$Q,0,MATCH($A4,Sheet2!$J$1:$Q$1,0)),1,INDEX(Sheet2!$J:$Q,0,MATCH(C$3,Sheet2!$J$1:$Q$1,0)),1)

It's a pity that the row numbers are not visible in your images.

I'm considering row 1 for the headers on sheet2:

Libro1
	I	J	K	L	M	N	O	P	Q
1	Slice	Test1	Test2	Test3	Test4	Test5	Test6	Test7	Test8
2	Slice1	1				1	1	1	1
3	Slice1	1				1		1	1
4	Slice3						1
5	Slice3				1				1
Sheet2

If the headers are on another row, then change the number 1 in this part of the formula:
=COUNTIFS(Sheet2!$I:$I,$B4,INDEX(Sheet2!$J:$Q,0,MATCH($A4,Sheet2!$J$1:$Q$1,0)),1,INDEX(Sheet2!$J:$Q,0,MATCH(C$3,Sheet2!$J$1:$Q$1,0)),1)

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------​

Amazing stuff Alex & Dante, I really appreciate your advice and help. This works great.

Thanks you both!

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.