Hello Team,
I am new to this forum and just logged in.
I require a formula to calculate the total volume, declined cases on a daily basis.
Can anyone please help me with the formula. I am managing 2 teams now and I want to calculate the volume and processing and declined count based on the cut off time.
Can anyone please help me. I have attached a sample file.
Welcome to the forum!
You need to provide some sample RESULT data. Please add that to your workbook (manually calculated) and post it again.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Hi Aligw,
Thank you. I have already added the data in the spreadsheet. Please refer Data tab in the attached spreadsheet.
I don't believe you have. There is no Data tab and I see no result data here:
Excel 2016 (Windows) 32 bit
Total Received Declined Declined /Total received Processed Not processed Grand Total Total Received Declined Processed Not processed Grand Total
Sheet: Summary
I did a manual calculation and updated the sheet.
I would need a formula so whenever I paste the data it should provide me the results.
Hi Aligw,
I am sorry, if you refer sheet 2 in my spreadsheet it contains the data. I would need the summary to be filled here using the formula.
For example, sheet 2 has 3 cases received for ATR so I want the summary against total volume received as "3'.
If there is a time in the declined column in the sheet 2 the summary sheet against the declined should return the count
All this should get updated based on the cut off time.
You aren't getting it, sorry. What I want YOU to do is fill in the SUMMARY sheet (manually) with the results you are expecting.
Please do as I have asked and then post a fresh copy of the workbook. Thanks.
Now explain the results.
1. Why 3 APR? The third is after the cut-off at 19:00.
2. What constitutes 'not processed'? They have all either been given a processing number or been declined.
Come on - you can do better than this. Help us to help you!!!
Hi - This is interesting.
. Why 3 APR? The third is after the cut-off at 19:00. - If u refer sheet 2 the team ATR received 3 cases in total however 1 case is processed by 19:00 which is after cut off.
We will have to count how many cases received under the team. Count of case number will give us the total.volume received.
2. What constitutes 'not processed'? They have all either been given a processing number or been declined. - Not processed cases will not have any reference number in column 'E'.so if there is a blank there last, then it is considered as not processed. Also if there is a declined time for these cases, then it should be counted in declined count as well.
Hope I clarified ur questions.
This makes no sense to me at all - sorry.. Why 3 APR? The third is after the cut-off at 19:00. - If u refer sheet 2 the team ATR received 3 cases in total however 1 case is processed by 19:00 which is after cut off.
We will have to count how many cases received under the team. Count of case number will give us the total.volume received.
I am going to step away from this and leave it for someone with better mind-reading skills than I have. Good luck with it!
Oops. See basically what i need is the count of cases received , count of processed and count of declined to be summarized in a sheet
I am not sure how could I explain so you understand the requirement.
I am sorry as well!! Please help me !!
I'm sorry - I don't have the time now. It's early evening in the UK and I am about to log off. Someone will help you, but you are going to need to be much clearer about what you want.
I've been looking at this as well, and have a few questions, some of which AliGW has already asked.
According to your manual results, the Total Received line should ignore the cutoff time. Is that correct?
What is the purpose of the Grand Total line? Would that adjust based on cutoff time, making it different than Total Received?
How is Not Processed different than Declined?
Edit:
I see you already answered the Not Processed question, but doesn't that mean you'll be double-counting? How do you want that to show up?
Last edited by Melvosh; 04-24-2019 at 02:10 PM.
If someone helped achieve your solution, consider clicking "* Add Reputation" on their post.
If your question has been answered, mark the thread as [SOLVED] using the Thread Tools menu at the top of the thread.
Hi Melvosh,
I have reframed in a simpler way and now u should be clear.
Please refer attached sheet
Hi Melvosh,
You are correct, the grand total is the grand total received. This is based on cut off time, total cases received before cut off and after cut off.
The only difference between not processed and declined is that if the cases are processed, we will have a reference number tied to it (COLUMN E)
If cases are not processed, them the reference number will be blank. There are chances that some cases get declined and we should count that declined on the declined count.
Please see the attached file and let us know if it provides what you need. All formulas are COUNTIFS using the criteria provided.
Can anyone please help me out !!!
Please check post #16 for a potential solution.
Hi Melvosh,
That's exactly I m looking out. If I need to again filter with country codes do I need to add the criteria in the count ifs.
Say for example after the team, if I need to publish the same data using the countries do I need to add the same criteria?
Yes, add another criterion. For example:
=COUNTIFS(Sheet2!$A$2:$A$6,"ATR",Sheet2!$C$2:$C$6,"<=" & $I$2)
would become:
=COUNTIFS(Sheet2!$A$2:$A$6,"ATR",Sheet2!$C$2:$C$6,"<=" & $I$2,country_range,country)
Thanks Aligw for the super quick response. !!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks