Name and Date Count in Excel

Question

I have been searching multiple sites trying to find an answer to this question, and if it is possible for excel to perform something like this, but here is my situation.

I have a logbook. After every call, our firefighters open the excel file and input call information about the call to log it. The information includes the following:

Call Log

This continues throughout the whole year. Separated cells for each Position, Unit, Name, Nature, Etc. all within one "call info box". What I am looking to do is count how many times a name appears on the calls throughout each month. So basically how many calls say "T. Name" responded to between 1/1/21 and 1/31/21. We receive multiple calls a day so there are multiple call info boxes that have the same date. The dates do not line up with the names.

I figured out how to see how many times a date between or equal to 1/1/21 and 1/31/21 appears so I am able to reference individual month call totals all off of our "Call Log" sheet, but the names is where the complication for me is coming in. As a workaround I currently copy all of January's calls into a separate sheet labeled "January" and have it count the person's name, but I am hoping to cut out that extra step and find a way for it to count everything using only the "Main Log" sheet.

Is it possible for Excel to perform such a function to take the total number of input dates between or equal to 1/1/21 and 1/31/21 and see how many times a name appears on those calls, without having to copy and paste the calls for the month into a separate sheet for each individual month?

Also, I am looking to have it set up where it does NOT count the name if the nature of the call is "EMS". EMS calls do not count toward our firefighters' call percentage.

Purpose of this is to generate a call percentage for each individual. Percentage of calls they responded to in the month, as well as percentage of calls they've responded to for the entire year. Is such a thing possible with the way this logbook is laid out? Thanks.

---

Data after formula input for pivot table:

List Pivot Table

question from:https://stackoverflow.com/questions/65864384/name-and-date-count-in-excel

Answer 1

Your main problem is the human-friendly arrangement of the module. I'd say the "easiest" way is to rearrange the data in order to make it computer-friendly so a pivot table can read it. In a sheet of your choice, put in the specified cell the given formula/value:

Cell A1:

Sheet

as header of the sheet list.

Cell A2:

January

as the name of the sheet with the call logs of January

Cell A3:

February

as the name of the sheet with the call logs of February

Cell A4-A13:

you got it, type the rest of the call logs sheet name.

Cell D1:

 Month

as header of the month list.

Cell D2:

 =IF(D1="Month",INDEX(A:A,2),IF(COUNTIF(INDIRECT(D1&"!B:B"),"Call Number")*12>TRUNC((CELL("row",D2)-CELL("row",INDIRECT("E"&MAX(AGGREGATE(15,6,ROW(D$1:D1)*1/(D$1:D1=D1),1)-1,1))))/5-0.1,0)*12+1,D1,INDEX(A:A,MATCH(D1,A:A,0)+1)))

Cell E1:

 Row

as header of the row list.

Cell E2:

 =TRUNC(COUNTIF($D$1:D2,D2)/5-0.1)*12+1

Cell F1:

 Call Number

as header of the call number list.

Cell F2:

 =INDEX(INDIRECT(D2&"!"&"C2",FALSE),$E2+1)

Cell G1:

 Date

as header of the date list.

Cell G2:

 =INDEX(INDIRECT(D2&"!"&"C2",FALSE),$E2+5)

Cell H1:

 Nature

as header of the nature list.

Cell H2:

 =INDEX(INDIRECT(D2&"!"&"C5",FALSE),$E2)

Cell I1:

 Off

as header of the officers list.

Cell I2:

 =IF(INDEX(INDIRECT(D2&"!"&"C"&((CELL("row",I1)-CELL("row",I$1))/5-TRUNC((CELL("row",I1)-CELL("row",I$1))/5,0))*10+5,FALSE),$E2+2)="","",INDEX(INDIRECT(D2&"!"&"C"&((CELL("row",I1)-CELL("row",I$1))/5-TRUNC((CELL("row",I1)-CELL("row",I$1))/5,0))*10+5,FALSE),$E2+2))

Then select the range D2:I2 and drag it down to report its formulas until they returns errors. You should now have a computer-friendly list. You can use it as a source for a pivot table. I'd suggest to put: Nature in the filters; Off in columns; Month and Date in rows; counts of Call Number in values. Of course once you've pasted the formulas, you can move them from their positions. So if you want to put them in the Main sheet, just create the columns you need to place them in the correct addresses and then move them as you please.

---

---

---

Extra tip

I assume that you tend to manually report your data in the log; to accomplish that, you copy-paste-clear-compile an old log. Using VBA you could make this process faster, safer and you could also store the data in an already computer-friendly way while keeping the log format for the human interation. This way you wouldn't need all these formulas to rearrange the data.