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

[Rmcclung]

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 extensions rang during the call. However, the call # itself is only documented in (Column A). As you can see, call #1 is from A2:A19, and call #2 is from A20:A38. Here are the formulas I'm looking for:

1 - Column J (CountRowsInCall) - I'd like this to formula to count the total # of rows that are within that call, but only display the total # of rows within that call on the same row as the Call #. See example spreadsheet for the values that should be displayed

2 - Column K (True Missed Call?) - I would like this formula to display "YES" on the same row as the call # if it's a missed call. A missed call is identified by any call where the "Call Minutes" in column L is blank. However, the formula needs to look at all rows in the "Call Minutes" column within that same call #. See example. Call #1 is a missed call since there are no values in "Call Minutes," but call #2 is not a missed call since L33 has a value in the "Call Minutes" column.

Any ideas on formulas for these? 

[#2]

-

[#3 · Accepted solution]

@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...

[#4]

This works perfectly. Thank you!

[#5]

Hello! Thank you for the help. The only issue I found with the formula is if the same Phone # calls in more than once, the formula will count those rows into the original call # as well. I believe another user was able to fix the issue.