Help With Formula

michaelwy

New Member
I have a worksheet that counts the number of days a person works each month. Everything was fine until I was asked to separate out the days a person only made emails or phone calls and no F2F (Face to Face) calls. The formula for Days worked was easy I just concatenated the person name with date and sorted and got the days worked. I have no idea how to get days that were not face to face.

The formula result I would like is in the column called Days no F2F

 Assigned Subject Date Combined Days Worked Days no F2F Person 1 F2F 3/1/2018 Person 143160 1 0 Person 1 F2F 3/1/2018 Person 143160 0 0 Person 1 F2F 3/1/2018 Person 143160 0 0 Person 1 Phone 3/2/2018 Person 143161 1 1 Person 1 Phone 3/5/2018 Person 143164 1 0 Person 1 F2F 3/5/2018 Person 143164 0 0 Person 1 Phone 3/12/2018 Person 143171 1 0 Person 1 F2F 3/12/2018 Person 143171 0 0 Person 1 F2F 3/14/2018 Person 143173 1 0 Person 1 F2F 3/14/2018 Person 143173 0 0 Person 1 F2F 3/14/2018 Person 143173 0 0 Person 1 F2F 3/14/2018 Person 143173 0 0 Person 1 F2F 3/14/2018 Person 143173 0 0 Person 1 Phone 3/15/2018 Person 143174 1 0 Person 1 F2F 3/15/2018 Person 143174 0 0 Person 1 F2F 3/15/2018 Person 143174 0 0 Person 1 F2F 3/15/2018 Person 143174 0 0 Person 1 F2F 3/15/2018 Person 143174 0 0 Person 1 email 3/15/2018 Person 143174 0 0 Person 1 email 3/15/2018 Person 143174 0 0 Person 1 F2F 3/16/2018 Person 143175 1 0 Person 1 F2F 3/16/2018 Person 143175 0 0 Person 1 F2F 3/16/2018 Person 143175 0 0 Person 1 Phone 3/19/2018 Person 143178 1 1 Person 1 email 3/19/2018 Person 143178 0 0

<tbody>
</tbody>

Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

SteveO59L

Well-known Member
have you considered a pivot table?

jorismoerings

Well-known Member
Hi,

if a pivot isn't feasible try this array formula:

<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

Replies
10
Views
148
Replies
3
Views
212
Replies
1
Views
123
Replies
1
Views
20
Replies
3
Views
189

1,109,333
Messages
5,528,089
Members
409,801
Latest member
Jamaira

This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...