Forum Discussion
Please help with (2) formulas based on exported call logs
- 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...
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...
- RmcclungDec 04, 2023Copper ContributorThis works perfectly. Thank you!