Hi, hope someone can help.
I'm looking to get the J column to work out the total for days and nights for each department.
So far I have
=COUNTIFS(C4:C11,"*DAYS*",D4:D11,"*Dept 1*")>0
But I can only get to TRUE or False but it to obviously add each instance together.
Hope that makes sense...with my attached mini sheet.
Thanks
I'm looking to get the J column to work out the total for days and nights for each department.
So far I have
=COUNTIFS(C4:C11,"*DAYS*",D4:D11,"*Dept 1*")>0
But I can only get to TRUE or False but it to obviously add each instance together.
Hope that makes sense...with my attached mini sheet.
Thanks
Student Returners.xlsm | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | Table I will be pulling data from | Department Total (days and night added together) | This is as far as I got… | Formula needed here? | Need the result to equal the below | ||||||||
2 | |||||||||||||
3 | Name | Total Hours | Days or Nights | Department | Dept 1 | 15 | Days | TRUE | ? | 7.5 | |||
4 | 1 | 7.5 | Days | Dept 1 | Nights | TRUE | ? | 7.5 | |||||
5 | 2 | 7.5 | Nights | Dept 1 | |||||||||
6 | 3 | 7.5 | Days | Dept 2 | Dept 2 | 7.5 | Days | TRUE | ? | 7.5 | |||
7 | 4 | 7.5 | Nights | Dept 3 | Nights | FALSE | ? | 0 | |||||
8 | 5 | 7.5 | Nights | Dept 3 | |||||||||
9 | 6 | 7.5 | Days | Dept 4 | Dept 3 | 15 | Days | FALSE | ? | 0 | |||
10 | 6 | 7.5 | Nights | Dept 4 | Nights | TRUE | ? | 15 | |||||
11 | 6 | 7.5 | Days | Dept 4 | |||||||||
12 | Dept 4 | 22.5 | Days | TRUE | ? | 15 | |||||||
13 | Nights | TRUE | ? | 7.5 | |||||||||
Test |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G3 | G3 | =SUMIF(D4:D11,"*"&F3&"*",B4:B11) |
I3 | I3 | =COUNTIFS(C4:C11,"*DAYS*",D4:D11,"*Dept 1*")>0 |
I4 | I4 | =COUNTIFS(C4:C11,"*NIGHTS*",D4:D11,"*Dept 1*")>0 |
G6 | G6 | =SUMIF(D4:D11,"*"&F6&"*",B4:B11) |
I6 | I6 | =COUNTIFS(C4:C11,"*DAYS*",D4:D11,"*Dept 2*")>0 |
I7 | I7 | =COUNTIFS(C4:C11,"*NIGHTS*",D4:D11,"*Dept 2*")>0 |
G9 | G9 | =SUMIF(D4:D11,"*"&F9&"*",B4:B11) |
I9 | I9 | =COUNTIFS(C4:C11,"*DAYS*",D4:D11,"*Dept 3*")>0 |
I10 | I10 | =COUNTIFS(C4:C11,"*NIGHTS*",D4:D11,"*Dept 3*")>0 |
G12 | G12 | =SUMIF(D4:D11,"*"&F12&"*",B4:B11) |
I12 | I12 | =COUNTIFS(C4:C11,"*DAYS*",D4:D11,"*Dept 4*")>0 |
I13 | I13 | =COUNTIFS(C4:C11,"*NIGHTS*",D4:D11,"*Dept 4*")>0 |