Counta each 2 columns

[NDMDRB]

Hi, please I need you help with a formula that counts the number of working days for each driver

As you can see in the attached file, the main sheet contains all the driver's emp # and names, on the top of each column I added the date, and the second row contains the area, where each trip came from
I have only two areas so I can add for each driver in the specific date how many trips from each area (if there is)
Then, in a separate column I sum all the trips from each area alone, and one more column to check how many days each driver work during this month
Ex: first driver works for 3 days, (first day has trips from both areas which counts only 1, so in this case I can't use counta for all the range D3:M3 since I will get 4, so I added another formula as in column T..., check each day if there is a trips or not and then use the sum function in the working days column

Please help me getting better solution to get the working days directly in the required column without adding the last functions as in column T, or if you have a better way to record these data this will be highly appreciated

Trip file

 

[jasonb75]

Please post your example directly to the forum, ideally using XL2BB.

Many members has security restrictions that will prevent them from opening files that you have linked to in other locations.

 

[NDMDRB]

Please post your example directly to the forum, ideally using XL2BB.

Many members has security restrictions that will prevent them from opening files that you have linked to in other locations.

The problem is that I'm using my mobile at the moment and I tried XL2BB but didn't work here, I will update this later today, thanks

 

[NDMDRB]

Here is the XL2BB format:

Test Trip File.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1			1-Jun		2-Jun		3-Jun		4-Jun		5-Jun			Total Trips			Working days		Checking if there is any trip per each day
2	Emp #	Name	E	W	E	W	E	W	E	W	E	W		E	W
3	1234	Malik	5	6						4		6		5	16		3		1			1	1
4	1235	Mark			5	6	5	6				4		10	16		3			1	1		1
5	1236	Barkat							4	5				4	5		1					1
6	1237	Imad		3		5		2			5	6		5	16		4		1	1	1		1
7	1238	Kuldep	2		2	2		3			1	2		5	7		4		1	1	1		1
Sheet1

Cell Formulas
Range	Formula
O3:P7	O3	=SUMIF($D$2:$M$2,O$2,$D3:$M3)
T3:T7	T3	=IF(COUNTA(D3:E3)>0,1,"")
U3:U7	U3	=IF(COUNTA(F3:G3)>0,1,"")
V3:V7	V3	=IF(COUNTA(H3:I3)>0,1,"")
W3:W7	W3	=IF(COUNTA(J3:K3)>0,1,"")
X3:X7	X3	=IF(COUNTA(L3:M3)>0,1,"")
R3:R7	R3	=SUM(T3:X3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B7	Cell Value	duplicates	text	NO

 

[jasonb75]

This formula works with the example. Enter into R3 then fill down.

=SUMPRODUCT(--(FREQUENCY(IF(ISNUMBER(D3:M3),COLUMN(D3:M3)),IF(D$2:M$2="W",COLUMN(D3:M3)))>0))

Depending on your excel version, you may need to array confirm the formula with Ctrl Shift Enter.

 

[NDMDRB]

This formula works with the example. Enter into R3 then fill down.

=SUMPRODUCT(--(FREQUENCY(IF(ISNUMBER(D3:M3),COLUMN(D3:M3)),IF(D$2:M$2="W",COLUMN(D3:M3)))>0))

Depending on your excel version, you may need to array confirm the formula with Ctrl Shift Enter.

Thank you so much Jason,
It works exactly as I need
Just have additional inquiry if you may help
what if I need to get the working days during a period
lets say in this example, I need the working days from 2nd of June until 4th

 

[jasonb75]

I think this is right.
Period start date in T1 and end date in T2
Array confirmed with Ctrl Shift Enter if not using office 365.

=SUM(--((COUNTIFS($D$1:$L$1,$D$1:$L$1,$D$1:$L$1,">="&$T$1,$D$1:$L$1,"<="&$T$2,$D$2:$L$2,"E",D3:L3,">0")+COUNTIFS($D$1:$L$1,$D$1:$L$1,$D$1:$L$1,">="&$T$1,$D$1:$L$1,"<="&$T$2,$E$2:$M$2,"W",E3:M3,">0"))>0))

 

[NDMDRB]

I think this is right.
Period start date in T1 and end date in T2
Array confirmed with Ctrl Shift Enter if not using office 365.

=SUM(--((COUNTIFS($D$1:$L$1,$D$1:$L$1,$D$1:$L$1,">="&$T$1,$D$1:$L$1,"<="&$T$2,$D$2:$L$2,"E",D3:L3,">0")+COUNTIFS($D$1:$L$1,$D$1:$L$1,$D$1:$L$1,">="&$T$1,$D$1:$L$1,"<="&$T$2,$E$2:$M$2,"W",E3:M3,">0"))>0))

That's great, thank you,