Counta each 2 columns

NDMDRB

Board Regular
Joined
Jun 20, 2016
Messages
163
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,470
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jun 20, 2016
Messages
163
Office Version
  1. 2016
Platform
  1. Windows
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

Board Regular
Joined
Jun 20, 2016
Messages
163
Office Version
  1. 2016
Platform
  1. Windows
Here is the XL2BB format:

Test Trip File.xlsx
BCDEFGHIJKLMNOPQRSTUVWX
11-Jun2-Jun3-Jun4-Jun5-JunTotal TripsWorking daysChecking if there is any trip per each day
2Emp #NameEWEWEWEWEWEW
31234Malik564651631  11
41235Mark5656410163 11 1
51236Barkat45451   1 
61237Imad352565164111 1
71238Kuldep222312574111 1
Sheet1
Cell Formulas
RangeFormula
O3:P7O3=SUMIF($D$2:$M$2,O$2,$D3:$M3)
T3:T7T3=IF(COUNTA(D3:E3)>0,1,"")
U3:U7U3=IF(COUNTA(F3:G3)>0,1,"")
V3:V7V3=IF(COUNTA(H3:I3)>0,1,"")
W3:W7W3=IF(COUNTA(J3:K3)>0,1,"")
X3:X7X3=IF(COUNTA(L3:M3)>0,1,"")
R3:R7R3=SUM(T3:X3)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:B7Cell ValueduplicatestextNO
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,470
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

This formula works with the example. Enter into R3 then fill down.
Excel Formula:
=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.
 
Solution

NDMDRB

Board Regular
Joined
Jun 20, 2016
Messages
163
Office Version
  1. 2016
Platform
  1. Windows
This formula works with the example. Enter into R3 then fill down.
Excel Formula:
=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

Well-known Member
Joined
Dec 30, 2008
Messages
14,470
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula:
=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

Board Regular
Joined
Jun 20, 2016
Messages
163
Office Version
  1. 2016
Platform
  1. Windows
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.
Excel Formula:
=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,
 

Forum statistics

Threads
1,147,518
Messages
5,741,638
Members
423,675
Latest member
Dea21

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top