There is no need to apologize, it's your spreadsheet and your data so you can do as you want. It's just that from my side I wouldn't have responded to your original question if I had to interpret a 5 row hard-coded formula so I wondered why you'd gone that direction. I understand you did it as you weren't following my use of OFFSET, so let me try and explain.
Your Sheet2 is looking at 12 months data but the cells you want to check are not contiguous (e.g. not all names for all months are in a single column or single row) so you want to check sets of 15 rows of data for each month.
OFFSET lets you define a matrix of cells against which you can perform a function, such as COUNTIFS.
The format is: OFFSET(reference, rows, cols, [height], [width])
- The "reference" is the starting point for the matrix. In our case I always use $B$1.
- The "rows" is how many rows below the "reference" we begin to define the matrix. The months October, November, December are all in row 4, January, February and March in row 22, April, May, June in row 40 and July, August, September in row 58. That is why my Sheet2 column K has those numbers as the row offset.
- The "cols" is how many columns to the right of the "reference" we begin to define the matrix. Similar as for "rows" I use Sheet2 column L to say that October, January, April and July all start in column B so column 2, November, February, May, August are in column T so column 20, etc.
- The "[height]" is how many rows are included in the matrix so it is always 15 as all the ranges we want to check (Names, Ship date, Highshcool) are 15 rows of data.
- The "[width]" is how many columns are included in the matrix but every range we use is a single column so that parameter if omitted and defaults to 1.
Let me correct the Highschool check as an example. For each month you want to count how many times the text "H.S." appears in the Highschool column when there is a number greater than zero in the Ship date.
Sheet2 cell J2 had the formula
Excel Formula:
=COUNTIFS(OFFSET(Sheet1!$B$1,K2+1,L2+2,15),"*H.S.*")
but we need to change it to
Excel Formula:
=COUNTIFS(OFFSET(Sheet1!$B$1,K2+1,L2+2,15),"*H.S.*",OFFSET(Sheet1!$B$1,K2+1,L2+5,15),">0")
so that we also check the Ship date.
First let's interpret the first OFFSET(Sheet1!$B$1,K2+1,L2+2,15)
K2 contains 4 and L2 contains 2 so it becomes
OFFSET(Sheet1!$B$1,5,4,15)
$B$1 plus 5 rows and plus 4 columns becomes $F$6 and with a height of 15 the range is $F$6:$F$20, which is the cells for the October Highschool column.
Now we want to check the Ship date so OFFSET(Sheet1!$B$1,K2+1,L2+5,15) becomes OFFSET(Sheet1!$B$1,5,7,15) so $B$1 plus 5 rows and 7 columns is $I$6 for a height of 15 makes the range $I$6:$I$20
The COUNTIFS becomes
COUNTIFS($F$6:$F$20,"*H.S.*",$I$6:$I$20,">0")
so it returns a count where the wildcard "*H.S.*" is found in $F$6:$F$20 AND $I$6:$I$20 are greater than zero.
This modified formula correctly identifies 22 and not 23 (but should it also check for Name not null?)
| Newbienew-V3 DEP STATUS BOARD.xlsx |
|---|
|
|---|
| A | B | C | D | E | F | G | H | I | J | K | L |
|---|
| 1 | | H.S. THOM | | | Month Accession | | dep | Name but no Date | Date but no Name | High School | Row | Column |
|---|
| 2 | | H.S. WEN | | oct | 10 | | oct | 0 | 0 | 6 | 4 | 2 |
|---|
| 3 | | H.S. EAG | | nov | 8 | | nov | 0 | 0 | 5 | 4 | 20 |
|---|
| 4 | | H.S. TRI | | dec | 10 | | dec | 0 | 0 | 5 | 4 | 38 |
|---|
| 5 | | H.S. COL | | jan | 8 | | jan | 0 | 0 | 5 | 22 | 2 |
|---|
| 6 | | H.S. BOOK | | feb | 0 | | feb | 8 | 0 | 0 | 22 | 20 |
|---|
| 7 | | COLLEGE | | mar | 0 | | mar | 0 | 1 | 1 | 22 | 38 |
|---|
| 8 | | | | apr | 0 | | apr | 1 | 0 | 0 | 40 | 2 |
|---|
| 9 | | | | may | 0 | | may | 0 | 0 | 0 | 40 | 20 |
|---|
| 10 | | | | jun | 0 | | jun | 0 | 0 | 0 | 40 | 38 |
|---|
| 11 | | | | jul | 0 | | jul | 0 | 0 | 0 | 58 | 2 |
|---|
| 12 | | | | aug | 0 | | aug | 0 | 0 | 0 | 58 | 20 |
|---|
| 13 | | | | sep | 0 | | sep | 0 | 0 | 0 | 58 | 38 |
|---|
| 14 | | | | | 36 | | | 9 | 1 | 22 | | |
|---|
|
|---|
