Steve 1962
Active Member
- Joined
- Jan 3, 2006
- Messages
- 351
- Office Version
- 365
- Platform
- Windows
Hi
I did repost this post but I'm not sure that it came through, so just posting again just in case it didn't work the first time. Apologies if this is a double-up.
I have added an additional column C (Exception) which has either a 1 or 2 or can be some other number.
I would like the current counting table that we now have working fine to now only count the entries if they are anything but 1 in the "Exception" column.
Eg: Count as normal but do not count those cells which have a 1 in them.
Thanks
I did repost this post but I'm not sure that it came through, so just posting again just in case it didn't work the first time. Apologies if this is a double-up.
I have added an additional column C (Exception) which has either a 1 or 2 or can be some other number.
I would like the current counting table that we now have working fine to now only count the entries if they are anything but 1 in the "Exception" column.
Eg: Count as normal but do not count those cells which have a 1 in them.
Thanks
Book1 | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | Date | Reference | Exception | Start | End | Reference | 5C42 | 111 | 234 | 678 | |||
2 | 1/04/2020 | 5C42 | 1 | Cairo | New York | Allowed Locations | Boston | Cairo | Boston | Auckland | |||
3 | 1/04/2020 | 5C42 | 1 | Boston | New York | New York | Auckland | New York | Las Vegas | ||||
4 | 1/04/2020 | 5C42 | 1 | Boston | New York | Calgary | New York | Cairo | Los Angeles | ||||
5 | 1/04/2020 | 111 | 1 | Dallas | Boston | ||||||||
6 | 1/04/2020 | 567 | 1 | Boston | Calgary | ||||||||
7 | 2/04/2020 | 234 | 1 | Boston | Cairo | Should Read - | 5C42 | 111 | 234 | 678 | |||
8 | 2/04/2020 | 234 | 1 | Los Angeles | Boston | 1/04/2020 | 1 | 1 | 0 | 0 | |||
9 | 2/04/2020 | 567 | 1 | Boston | Boston | 2/04/2020 | 0 | 1 | 1 | 0 | |||
10 | 2/04/2020 | 567 | 1 | Boston | New York | 3/04/2020 | 0 | 2 | 0 | 3 | |||
11 | 2/04/2020 | 111 | 1 | Boston | New York | 4/04/2020 | 0 | 0 | 1 | 0 | |||
12 | 2/04/2020 | 567 | 2 | Boston | New York | ||||||||
13 | 3/04/2020 | 111 | 2 | New York | New York | ||||||||
14 | 3/04/2020 | 111 | 2 | New York | Los Angeles | ||||||||
15 | 3/04/2020 | 111 | 2 | Las Vegas | New York | ||||||||
16 | 3/04/2020 | 678 | 2 | New York | Cairo | ||||||||
17 | 3/04/2020 | 678 | 2 | New York | Cairo | ||||||||
18 | 3/04/2020 | 678 | 1 | Seattle | Cairo | ||||||||
19 | 4/04/2020 | 111 | 1 | New York | Cairo | ||||||||
20 | 4/04/2020 | 111 | 2 | New York | Cairo | ||||||||
21 | 4/04/2020 | 567 | 2 | New York | Boston | ||||||||
22 | 4/04/2020 | 234 | 2 | Auckland | Boston | ||||||||
23 | 4/04/2020 | 237 | 2 | Cairo | Boston | ||||||||
24 | 4/04/2020 | 237 | 2 | Auckland | Boston | ||||||||
Mr Excel |
Cell Formulas | ||
---|---|---|
Range | Formula | |
H8:K11 | H8 | {=SUM(--(FREQUENCY(IF(ISNA(MATCH($D$2:$E$24,H$2:H$4,0))*ISNUMBER(MATCH($A$2:$A$24,$G8,0))*ISNUMBER(MATCH($B$2:$B$24,H$1,0)),ROW($A$2:$A$24)-ROW($A$2)+1,""),ROW($A$2:$A$24)-ROW($A$2)+1)>0))} |
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces. |