Hi can anyone help with a formula?
On Sheet1 I have headers in cells "A1:D1"
A1 = Ref
B1 = A
C1 = B
D1 = C
On Sheet2 I have Data with headers in 3 separate blocks with 7 columns each:
"B1:H" "J1:P" "R1:X"
"B1:H1" - headers = A
<tbody>
</tbody>
<tbody>
</tbody>
Any help would be appreciated
Regards
pwill
On Sheet1 I have headers in cells "A1:D1"
A1 = Ref
B1 = A
C1 = B
D1 = C
On Sheet2 I have Data with headers in 3 separate blocks with 7 columns each:
"B1:H" "J1:P" "R1:X"
"B1:H1" - headers = A
"J1:P1" - headers = B
"R1:X1" - headers = C
On Sheet1 in the Ref Column "A2" different values will be entered. I have entered 0 the example below.
In cell "C2" Sheet1 using the Ref Value in "A1" I want to Count how many times 0 appears on Sheet2 in block B
<strike></strike><strike></strike>"R1:X1" - headers = C
On Sheet1 in the Ref Column "A2" different values will be entered. I have entered 0 the example below.
In cell "C2" Sheet1 using the Ref Value in "A1" I want to Count how many times 0 appears on Sheet2 in block B
("J1:P")
And also count at the same time in cells ("B2" & "D2") Sheet1 - how many times 0 appears in
block A ("B1:H") and block C ("R1:X") but only when 0 appears in the same rows as block B ("J1:P")
eg Sheet1
And also count at the same time in cells ("B2" & "D2") Sheet1 - how many times 0 appears in
block A ("B1:H") and block C ("R1:X") but only when 0 appears in the same rows as block B ("J1:P")
eg Sheet1
| A | B | C | D | E | F | |
| 1 | Ref | A | B | C | ||
| 2 | 0 | 5 | 7 | 4 | ||
| 3 | ||||||
| 4 | ||||||
| 5 |
<tbody>
</tbody>
Sheet1 "B2" there are five 0's that appear in Block A on the same rows as Block B and there are four 0's that appear in Block C on the same rows as Block B
eg Sheet2 (I have removed data in the empty rows for easier viewing)
eg Sheet2 (I have removed data in the empty rows for easier viewing)
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | |
| 1 | A | A | A | A | A | A | A | B | B | B | B | B | B | B | C | C | C | C | C | C | C | ||||
| 2 | -2 | 7 | 0 | 18 | 27 | 28 | 35 | 0 | -6 | 9 | 14 | 23 | 24 | 31 | 1 | 0 | -9 | 11 | 20 | 21 | 28 | ||||
| 3 | |||||||||||||||||||||||||
| 4 | 0 | 22 | 28 | 38 | 39 | 41 | 47 | 0 | -6 | 10 | 11 | 13 | 19 | -24 | |||||||||||
| 5 | -2 | 0 | -3 | 8 | 9 | 37 | 43 | ||||||||||||||||||
| 6 | 0 | 5 | 16 | 19 | 30 | 33 | 37 | ||||||||||||||||||
| 7 | -3 | 5 | -6 | 8 | 16 | 19 | 30 | 0 | 3 | -8 | 11 | -11 | 14 | 25 | 0 | 3 | -8 | -11 | 14 | -19 | -22 | ||||
| 8 | |||||||||||||||||||||||||
| 9 | 1 | 0 | 12 | 14 | 22 | 26 | 38 | 0 | 6 | -6 | 8 | 16 | 20 | 32 | 1 | 2 | 0 | 10 | -12 | 14 | 26 | ||||
| 10 | 0 | -7 | -10 | -11 | -18 | 20 | 22 | ||||||||||||||||||
| 11 | 1 | 3 | -3 | 8 | 36 | 40 | 0 | 1 | 0 | -4 | 7 | 35 | 39 | 40 | |||||||||||
| 12 | 0 | 4 | -12 | -14 | 16 | -23 | -26 | ||||||||||||||||||
| 13 | 0 | 5 | 27 | 29 | 34 | 35 | 39 | ||||||||||||||||||
| 14 | 1 | 0 | 7 | 12 | -13 | -26 | -29 | ||||||||||||||||||
| 15 | |||||||||||||||||||||||||
| 16 | 1 | 5 | 7 | -7 | 29 | 30 | 32 | 2 | 3 | 0 | -8 | 28 | 29 | 31 | 2 | 3 | -3 | -11 | 25 | 0 | 28 | ||||
| 17 | |||||||||||||||||||||||||
| 18 | 3 | 7 | 10 | 0 | 22 | 27 | 45 | 1 | -4 | 0 | 17 | 18 | 23 | 41 | |||||||||||
| 19 | 0 | 21 | 32 | 34 | 39 | 40 | 46 | 12 | 14 | 0 | 27 | 32 | 33 | 39 | -10 | 11 | 22 | 23 | 29 | 30 | 36 | ||||
| 20 | -1 | 5 | 6 | 13 | 15 | 22 | 23 | ||||||||||||||||||
| 21 |
<tbody>
</tbody>
Any help would be appreciated
Regards
pwill
Last edited:
