How can I write the COUNTIFS function only one time when it comes to different conditions?

[Dezom]

Hi Everyone,

I put here the table for your perception.

Week 1-2-3-4 => January
Week 4-5-6 => Febraury
I am here calculating total job concept type numbers at each month.

How I am solving it currently is a bit time-consuming. Therefore I want to improve that formula that I am using.
What I want is to write Countif formulas only one time combining all weeks in one formula instead of writing it seperately like below. (But I do not want to make changings in raw data table like adding months instead of weeks, I want to solve it with formula itself) How would we achieve that?
Thanks you very much

Countifs question.xlsx
	A	B	C	D	E	F	G	H
1	Worker	Week	Concept	Duration
2	John	1.week	Correction	20				Correction
3	John	1.week	MK	20		January	1.week 2.week 3.week 4.week	1
4	Tomas	1.week	MK	20		February	5.week 6.week 7.week	4
5	Karhan	2.week	MK	20
6	Dennis	1.week	MK	12
7	Kylee	2.week	SK	12
8	Tomas	6.week	SK	20
9	John	2.week	SK	12
10	Mattheus	3.week	SK	12
11	Dennis	3.week	SK	12
12	Markus	3.week	SK	20
13	Tomas	4.week	New Work	14
14	Karhan	4.week	New Work	2
15	Mattheus	4.week	New Work	4
16	Karhan	4.week	New Work	2
17	Karhan	5.week	Correction	14
18	John	5.week	Correction	5
19	Kylee	5.week	Correction	3
20	Kylee	5.week	Correction	6
21	Kylee	5.week	New Work	2
22	Jonnathan	5.week	New Work	0
23	Daniel	5.week	Second work	22
24	Solomon	6.week	Production Sequence	3
25	John	6.week	Second work	15
26	Mattheus	6.week	New Work	18
27	Kylee	7.week	New Work	10
28	Dennis	7.week	Second work	14
29	Solomon	7.week	New Work	14
30	Jonnathan	7.week	New Work	14
Data

Cell Formulas
Range	Formula
H3	H3	=COUNTIFS(C2:C30,H2,B2:B30,"1.week")+COUNTIFS(C2:C30,H2,B2:B30,"2.week")+COUNTIFS(C2:C30,H2,B2:B30,"3.week")+COUNTIFS(C2:C30,H2,B2:B30,"4.week")
H4	H4	=COUNTIFS($C$2:$C$30,$H$2,$B$2:$B$30,"5.week")+COUNTIFS($C$2:$C$30,$H$2,$B$2:$B$30,"6.week")+COUNTIFS($C$2:$C$30,$H$2,$B$2:$B$30,"7.week")

 

[Fluff]

One option with slight change to the sheet

+Fluff New.xlsm
	A	B	C	D	E	F	G	H
1	Worker	Week	Concept	Duration
2	John	1.week	Correction	20			Week	Correction
3	John	1.week	MK	20		January	4	1
4	Tomas	1.week	MK	20		February	7	4
5	Karhan	2.week	MK	20
6	Dennis	1.week	MK	12
7	Kylee	2.week	SK	12
8	Tomas	6.week	SK	20
9	John	2.week	SK	12
10	Mattheus	3.week	SK	12
11	Dennis	3.week	SK	12
12	Markus	3.week	SK	20
13	Tomas	4.week	New Work	14
14	Karhan	4.week	New Work	2
15	Mattheus	4.week	New Work	4
16	Karhan	4.week	New Work	2
17	Karhan	5.week	Correction	14
18	John	5.week	Correction	5
19	Kylee	5.week	Correction	3
20	Kylee	5.week	Correction	6
21	Kylee	5.week	New Work	2
22	Jonnathan	5.week	New Work	0
23	Daniel	5.week	Second work	22
24	Solomon	6.week	Production Sequence	3
25	John	6.week	Second work	15
26	Mattheus	6.week	New Work	18
27	Kylee	7.week	New Work	10
28	Dennis	7.week	Second work	14
29	Solomon	7.week	New Work	14
30	Jonnathan	7.week	New Work	14
Temp

Cell Formulas
Range	Formula
H3:H4	H3	=SUMPRODUCT(($C$2:$C$30=$H$2)*(--LEFT($B$2:$B$30,FIND(".",$B$2:$B$30)-1)<=G3)*(--LEFT($B$2:$B$30,FIND(".",$B$2:$B$30)-1)>N(G2)))

 

[gaz_chops]

Not sure if this is what you are after

=COUNTIFS($C$2:$C$30,$K$2,$B$2:$B$30,G3:J3)
Enter as an array, Ctrl, Shift & Enter

 

[gaz_chops]

Ignore mine, on testing further it does not work correctly.

This should work
=SUM(COUNTIFS($C$2:$C$30,$K$2,$B$2:$B$30,G3:J3))
Enter as an array.

 

[Dezom]

Ignore mine, on testing further it does not work correctly.

This should work
=SUM(COUNTIFS($C$2:$C$30,$K$2,$B$2:$B$30,G3:J3))
Enter as an array.

thanks that works

 

[Dezom]

One option with slight change to the sheet

+Fluff New.xlsm
	A	B	C	D	E	F	G	H
1	Worker	Week	Concept	Duration
2	John	1.week	Correction	20			Week	Correction
3	John	1.week	MK	20		January	4	1
4	Tomas	1.week	MK	20		February	7	4
5	Karhan	2.week	MK	20
6	Dennis	1.week	MK	12
7	Kylee	2.week	SK	12
8	Tomas	6.week	SK	20
9	John	2.week	SK	12
10	Mattheus	3.week	SK	12
11	Dennis	3.week	SK	12
12	Markus	3.week	SK	20
13	Tomas	4.week	New Work	14
14	Karhan	4.week	New Work	2
15	Mattheus	4.week	New Work	4
16	Karhan	4.week	New Work	2
17	Karhan	5.week	Correction	14
18	John	5.week	Correction	5
19	Kylee	5.week	Correction	3
20	Kylee	5.week	Correction	6
21	Kylee	5.week	New Work	2
22	Jonnathan	5.week	New Work	0
23	Daniel	5.week	Second work	22
24	Solomon	6.week	Production Sequence	3
25	John	6.week	Second work	15
26	Mattheus	6.week	New Work	18
27	Kylee	7.week	New Work	10
28	Dennis	7.week	Second work	14
29	Solomon	7.week	New Work	14
30	Jonnathan	7.week	New Work	14
Temp

Cell Formulas
Range	Formula
H3:H4	H3	=SUMPRODUCT(($C$2:$C$30=$H$2)*(--LEFT($B$2:$B$30,FIND(".",$B$2:$B$30)-1)<=G3)*(--LEFT($B$2:$B$30,FIND(".",$B$2:$B$30)-1)>N(G2)))

thanks Fluff, that works

 

[Fluff]

Glad we could help & thanks for the feedback.