Count Formulae Help

[HaudYerWeesht]

Hi. I have a list of staff and I’m keeping track of any days they take off.

Cell A1 - Joiner, A2 - Plumber, A3 - Roofer, A4 Joiner etc

In column B I have blank cells and each cell denotes 4 hours unless it is filled and then it becomes 0. I have a formula in place in another column that adds up all the blank cells and multiplies it by 4 and gives the total hours for that day.

However it is for all trades. Is there a formula that will add up the blank cells in column B only for one of the trades? So if the 2 joiners were working could a formula look for the word joiner in column A and then check column B for blank cells on the same row as the joiners and then multiply them by 4?

Thanks

 

[etaf]

countifs()
should work for you

=countifs( A1:A10000, "joiner", B1:B10000, "")
then * 4
you could use a cell with joiner in rather than hardcoding

Book4
	A	B	C	D
1
2	joiner			5
3	bricklayer
4	joiner	1
5	bricklayer
6	joiner
7	bricklayer
8	joiner	1
9	bricklayer
10	joiner
11	bricklayer
12	joiner	1
13	bricklayer
14	joiner
15	bricklayer
16	joiner
17	bricklayer
Sheet1

Cell Formulas
Range	Formula
D2	D2	=COUNTIFS(A1:A23,"joiner",B1:B23,"")

using a cell to put the criteria in - also the count multiplied by 4

Book4
	A	B	C	D	E	F
1
2	joiner				Joiner	20
3	bricklayer
4	joiner	1
5	bricklayer
6	joiner
7	bricklayer
8	joiner	1
9	bricklayer
10	joiner
11	bricklayer
12	joiner	1
13	bricklayer
14	joiner
15	bricklayer
16	joiner
17	bricklayer
Sheet1

Cell Formulas
Range	Formula
F2	F2	=COUNTIFS(A1:A23,E2,B1:B23,"")*4

 

[HaudYerWeesht]

countifs()
should work for you

=countifs( A1:A10000, "joiner", B1:B10000, "")
then * 4
you could use a cell with joiner in rather than hardcoding

Book4
	A	B	C	D
1
2	joiner			5
3	bricklayer
4	joiner	1
5	bricklayer
6	joiner
7	bricklayer
8	joiner	1
9	bricklayer
10	joiner
11	bricklayer
12	joiner	1
13	bricklayer
14	joiner
15	bricklayer
16	joiner
17	bricklayer
Sheet1

Cell Formulas
Range	Formula
D2	D2	=COUNTIFS(A1:A23,"joiner",B1:B23,"")

using a cell to put the criteria in - also the count multiplied by 4

Book4
	A	B	C	D	E	F
1
2	joiner				Joiner	20
3	bricklayer
4	joiner	1
5	bricklayer
6	joiner
7	bricklayer
8	joiner	1
9	bricklayer
10	joiner
11	bricklayer
12	joiner	1
13	bricklayer
14	joiner
15	bricklayer
16	joiner
17	bricklayer
Sheet1

Cell Formulas
Range	Formula
F2	F2	=COUNTIFS(A1:A23,E2,B1:B23,"")*4

perfect, thanks etaf

 

[etaf]

you are welcome
no real need to quote my reply in yours - FYI

 

[HaudYerWeesht]

I hit reply to ur answer instead of just replying here!