Forum Discussion

Rmcclung's avatar
Rmcclung
Copper Contributor
Dec 01, 2023
Solved

Please help with (2) formulas based on exported call logs

Hi everyone,   We export call logs from our phone record system (Zoom). It displays as such in the attached example sheet. Each individual call is logged with multiple rows based on how many phone ...
  • djclements's avatar
    Dec 02, 2023

    Rmcclung The first thing I would do is create a secondary "Call No." column that fills the "Call #" down for all related rows with the following formula:

     

    =IF(ISBLANK([@[Call '#]]), CellAbove, [@[Call '#]])

     

    ...where CellAbove is a defined name (in Name Manager) that is relative to the current cell. This will help to simplify the subsequent formulas for each call number.

     

    Then, the formula for "CountRowsInCall" could be:

     

    =IF(ISBLANK([@[Call '#]]), "", COUNTIF([Call No.], [@[Call No.]]))

     

    ...and the formula for "True Missed Call?" could be:

     

    =IF(ISBLANK([@[Call '#]]), "", IF(SUMIF([Call No.], [@[Call No.]], [Call Minutes])=0, "Yes", "No"))

     

    Also, based on the data shared in the sample workbook, the formula used for "Call Minutes" could possibly be simplified by checking for "Completed" in the result column only:

     

    =IF([@Result]="Completed", [@[Duration (hh:mm:ss)]]*1440, "")

     

    However, I'll leave that for you to decide, as the sample data may not include all possible scenarios.

     

    Bonus: with the additional "Call No." column, you could also create a simple summary report using dynamic array functions available in Excel for MS365. For example:

     

    =LET(
       unq, UNIQUE(Table2[Call No.]),
       rws, COUNTIF(Table2[Call No.], unq),
       dur, SUMIFS(Table2[Duration (hh:mm:ss)], Table2[Result], "Completed", Table2[Call No.], unq)*1440,
       mss, IF(dur=0, "Yes", "No"),
       HSTACK(unq, rws, dur, mss)
    )

     

    Please see the attached workbook, which contains all of the above-mentioned methods...

Resources