Total all cells containing specific text in two cells

[suxrule]

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

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

 

[Fluff]

Just remove the >0 from the end of the formula

 

[suxrule]

Thanks.

Sorry I've probably not explained myself very well.

I want any time "Days" or "Nights" is mentioned with the correct department I want the hours associated with each department added to either the days or nights adjacent cells in the red highlighted cells in my example above.

Thanks again though.

 

[Fluff]

Ok, you need to use sumifs in that case like

=SUMIFS(B4:B11,C4:C11,"*DAYS*",D4:D11,"*Dept 1*")

 

[suxrule]

Excellent, thanks so much, really appreciate it.

 

[Fluff]

Glad to help & thanks for the feedback.