Formula that automatically calculate Incoming, abandoned and answered calls.

[juan.doe]

Hello,

I want to produce a daily report that automatically shows the abandoned, answered and calculated incoming calls of a given DATE [a day before the reporting date] using an excel formula when I pasted in the raw data.

Given:

- Report Sheet
- Raw Data Sheet

To get the product of Incoming call needs to sum the Answered (Col C) and Abandoned calls (Col H) from the Raw Data.

While the figures for abandoned and answered need to pullout from Raw Data Col J and Col G respectively.

Any info or formula is highly appreciated.

Thanks,

John

[Olly]

Need to see how your data is stored - can you attach a workbook?

[juan.doe]

@OllyXLS

I've just uploaded the workbook for your reference. Thank you!

[Olly]

Incoming calls:

Please Login or Register  to view this content.

Abandoned after threshold:

Please Login or Register  to view this content.

Answered after threshold:

Please Login or Register  to view this content.

[juan.doe]

@OllyXLS

The formulas worked well in the sample workbook but when I pasted it in the actual report I'm getting an #N/A error.

[Olly]

That's because you have no data for yesterday. The formula matches values for TODAY()-1 = yesterday's date, as your initial requirement stated

Now, do you actually want to SUM calls per company for the stated date range?!

[juan.doe]

Yes please.

I really appreciate your help.

[Olly]

No problem.

You still have no data for the range you have stated... All your data is for October.

See the attached file, which sums calls between the stated start and end dates.



Your raw data structure is lousy, though. It means you'll have to edit the row references in the sumif formulae whenever you get more rows of data...

[juan.doe]

Nice... Great Work!

Just an observation...

The sum of "Ans after Threshold" shows zero values instead of 9 for ABC, 2 - DEF, 121 - GHI, 6 - JKL, and 24 for MNO.

And one last...

Can you please create formulas similar to what you've done in the first workbook?

In the attached, I highligthed in yellow the cells that need the formulas and their expected values.

Thank you so much....

[Olly]

How about you adjust the formulas I have showed you, to suit your need? Happy to explain HOW they work, but I am sure you can figure out which ranges / references the formulas refer to, and tweak as needed.