Alternative to countif needed for multiple lookup

[whiteshadoz]

Hello,

I have a spreadsheet (Excel Online) that I need help with a formula on. The attached workbook has detail on but to summarise on here too. (the workbook has had all private data removed)
starttime
I have a list of data from incoming and outgoing calls. This is a large data file that is from another source I can't edit or control the format of. It is mainly made up of:

• user numbers making the call
• time the call was made (in time format)
• start time of the call (in date and time format)
• connect time of the call (in date and time format)
• disconnect time of the call (in date and time format)

On my spreadsheet, I am copying the incoming data to one tab and the outgoing data to another.
I then have a summary sheet which displays the user numbers in one column and then needs to count how many inbound calls the user has had between a set time and same again with outbound. Currently, I have a way to do this using countifs.

My issue is, I now need to count the same for outbound calls but only if the connect time to disconnect time is above 30 seconds.

I have a cell with 0.000347222 in (as a general format of 30 seconds) but can't work out a way to check if column K (disconnect) minus column L (connect) is above this value.

If you have any alternative ways on the other areas of the workbooks that would be great.

[AliGW]

Administrative Note:

Is your forum profile showing the version of Excel that you need this to work for?

Members will tailor the solutions they offer to the version of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.

Thanks.

[AliGW]

I am not at all clear how you would determing the length of a call from this data. Please add a few expected results (calculated manually) where you wish to see them and post the workbook again.

[whiteshadoz]

Hello,

Thank you for your reply.

To get the call time from the outgoing tab it would be column K minus column J.

[AliGW]

I had no idea there were two other sheets - looked like a sheet with only one tab!

I'm out of time, I'm afraid - someone else will jump in.

Are you wtill using Excel 2007??? If not, you need to update your profile, please.

[whiteshadoz]

Thank you.

I've updated my profile to MS365, I'm just trying to find the version.

Call Summary spreadsheet attached with the requested manual info.



The formaul I have now to count how many calls are outgoing is:

=COUNTIFS('Outgoing Data Dump'!$AL:$AL,'Total from Report'!$D8,'Outgoing Data Dump'!$AF:$AF,">="&'Total from Report'!M$6,'Outgoing Data Dump'!$AF:$AF,"<"&'Total from Report'!Q$6)

What I can't do is add another condition with something to the effect of:
('Outgoing Data Dump'!K:K)-('Outgoing Data Dump'!J:J)">="&'Total from Report'!B6

As I understand it, I can't use a countif to do (column-column)>=(Cell data)

Hope that makes sense.

[Glenn Kennedy]

edit. reply removed by GK

[whiteshadoz]

Hi, thank you for replying.

The raw data on Outgoing tab in cell I2 shows: 22/03/2023 12:48:03

This does include seconds. The incoming sheet doesn't show seconds but I am not needing to check it from that sheet, just the outgoing as mentioned above.

If it was needed to be above a minute though, I still can't make a formula that does this. Can you help?

[Glenn Kennedy]

Your INCOMING call data is logged to the nearest minute. Your OUTGOING call data is logged to the nearest second. How do you want to proceed?

[Glenn Kennedy]

Our replies have passed each other in cyberspace. Working on it.

[Glenn Kennedy]

I8:
=SUMPRODUCT(('Incoming Data Dump'!$AJ$2:$AJ$10000=$D8)*('Incoming Data Dump'!$AE$2:$AE$10000>=I$6)*('Incoming Data Dump'!$AE$2:$AE$10000<M$6)*('Incoming Data Dump'!$K$2:$K$10000-'Incoming Data Dump'!$J$2:$J$10000>=2*$B$6))

K8:
=SUMPRODUCT(('Outgoing Data Dump'!$AL$2:$AL$10000=$D8)*('Outgoing Data Dump'!$AF$2:$AF$10000>=I$6)*('Outgoing Data Dump'!$AF$2:$AF$10000<M$6)*('Outgoing Data Dump'!$K$2:$K$10000-'Outgoing Data Dump'!$J$2:$J$10000>=$B$6))

Select I8:K20. Copy-paste to other columns.

DO NOT use whole column references with SUMPRODUCT. It will be VERY slow. Since your sample data was massively in excess ofour recommendations (10-20 rows) I have not checked this extensively.

[whiteshadoz]

I think that seems to be working! You absolute star, thank you.

Apologies, I had trimmed out the data that was sensitive and forgot to strip the row data down to a lower number so I am sorry for that, I'll be more mindful in the future.

On to the next stages that I want to work out but if I get stuck, I know where to come.

Thanks again! Have a good evening.

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.