How to count data based on lookup from 2 tables

[BradleyS]

I have two lookup tables ID (Col A-C) and TYPE (Col E-I). I have colour coded them to help work out the combinations.
The DATA table (Col K-L) is what I need to count up based on each ID and TYPE combination, and the results to go under the total column in O. I have manually added the correct totals in column P
I'm not sure what formula to even start using to get the required totals. Any help is most appreciated.

I tried using COUNTIFS which works on it's own i.e. using either ID or TYPE, but fails when I try add both the ID and TYPE.

Staff Roles.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	ID1	ID2	ID3		TYPE1	TYPE2	TYPE3	TYPE4	TYPE5		DATA			COUNT DATA	TOTALS
2	ROL001	ROL006	ROL011		COT005	COT007	COT001	COT103	COT010		ID	TYPE		ID1 & TYPE1		1
3	ROL002	ROL016	ROL012		COT012	COT008	COT002	COT116	COT021		ROL015	COT117		ID1 & TYPE2		1
4	ROL003	ROL019	ROL013		COT013	COT014	COT003	COT117	COT035		ROL023	COT015		ID1 & TYPE3		1
5	ROL004	ROL020	ROL014		COT015	COT022	COT004		COT115		ROL032	COT039		ID1 & TYPE4
6	ROL005	ROL021	ROL015		COT016	COT023	COT006				ROL002	COT009		ID1 & TYPE5
7	ROL007	ROL022	ROL017		COT017	COT033	COT009				ROL001	COT023
8	ROL008	ROL023	ROL018		COT019	COT038	COT011				ROL023	COT009		ID2 & TYPE1		1
9	ROL009	ROL024	ROL026		COT025	COT039	COT018				ROL006	COT009		ID2 & TYPE2		1
10	ROL010	ROL025	ROL027		COT026	COT040	COT020				ROL023	COT116		ID2 & TYPE3		3
11	ROL047	ROL032	ROL028		COT029	COT041	COT024				ROL023	COT011		ID2 & TYPE4		1
12	ROL050	ROL035	ROL029		COT102	COT042	COT027				ROL002	COT019		ID2 & TYPE5		1
13											ROL023	COT021
14														ID3 & TYPE1
15														ID3 & TYPE2
16														ID3 & TYPE3
17														ID3 & TYPE4		1
18														ID3 & TYPE5
Sheet2

 

[Domenic]

With the following solution, you'll notice that I have split Column N into two columns so that it's easier to specify the criteria in the formula. Then, simply enter the formula in P2, confirm with CONTROL+SHIFT+ENTER, and copy down.

bradley.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	ID1	ID2	ID3		TYPE1	TYPE2	TYPE3	TYPE4	TYPE5		DATA			COUNT DATA		TOTALS
2	ROL001	ROL006	ROL011		COT005	COT007	COT001	COT103	COT010		ID	TYPE		ID1	TYPE1	1
3	ROL002	ROL016	ROL012		COT012	COT008	COT002	COT116	COT021		ROL015	COT117		ID1	TYPE2	1
4	ROL003	ROL019	ROL013		COT013	COT014	COT003	COT117	COT035		ROL023	COT015		ID1	TYPE3	1
5	ROL004	ROL020	ROL014		COT015	COT022	COT004		COT115		ROL032	COT039		ID1	TYPE4	0
6	ROL005	ROL021	ROL015		COT016	COT023	COT006				ROL002	COT009		ID1	TYPE5	0
7	ROL007	ROL022	ROL017		COT017	COT033	COT009				ROL001	COT023
8	ROL008	ROL023	ROL018		COT019	COT038	COT011				ROL023	COT009		ID2	TYPE1	1
9	ROL009	ROL024	ROL026		COT025	COT039	COT018				ROL006	COT009		ID2	TYPE2	1
10	ROL010	ROL025	ROL027		COT026	COT040	COT020				ROL023	COT116		ID2	TYPE3	3
11	ROL047	ROL032	ROL028		COT029	COT041	COT024				ROL023	COT011		ID2	TYPE4	1
12	ROL050	ROL035	ROL029		COT102	COT042	COT027				ROL002	COT019		ID2	TYPE5	1
13											ROL023	COT021
14														ID3	TYPE1	0
15														ID3	TYPE2	0
16														ID3	TYPE3	0
17														ID3	TYPE4	1
18														ID3	TYPE5	0
Sheet1

Cell Formulas
Range	Formula
P2:P18	P2	=IF(AND(LEN(N2)>0,LEN(O2)>0),SUM(IF(ISNUMBER(MATCH($K$3:$K$13,INDEX($A$2:$C$12,0,MATCH(N2,$A$1:$C$1,0)),0)),IF(ISNUMBER(MATCH($L$3:$L$13,INDEX($E$2:$I$12,0,MATCH(O2,$E$1:$I$1,0)),0)),1))),"")

Hope this helps!

 

[BradleyS]

Thank you for this I would never have worked that one out. No wonder I was struggling.
I wasn't expecting you to use the text I put in column N. I only entered it like this to make it easier to understand. In the main project it's a completely different description. However, I will see if I can use it and just hide these reference columns. Thank you for you help with this.

 

[Domenic]

Thank you for this I would never have worked that one out. No wonder I was struggling.

You're very welcome, glad I could help.

I wasn't expecting you to use the text I put in column N. I only entered it like this to make it easier to understand. In the main project it's a completely different description. However, I will see if I can use it and just hide these reference columns. Thank you for you help with this.

Hiding the columns is one option, but it would seem to me that leaving them unhidden would make things a lot clearer.