BradleyS
Active Member
- Joined
- Oct 28, 2006
- Messages
- 333
- Office Version
- 2010
- Platform
- Windows
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.
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 |