Need Help on Sumif using rank based on criteria (which changes)

[abhishekadhikari84]

Hi Team,

This is my first post and apologies if I am overstepping any rules here. I have gone through similar posts, but didnt find a way forward. There are similar threads, but the issue here is a bit different.
The problem Statement is as follows - I need to calculate the time taken before a certain status and time taken after a certain status.

I have a Salesforce report (image attached). From Salesforce I get the Case number, Date and Time Opened, Case History Status (different status through the case has gone) & Case History Modified Date and Time (corresponding to the Status change).

Using this, I have calculated the Status Rank (based on Case History Modified Date and Time) for each case - Column J and formula mentioned above.
I have also calculated a column where Customer Interactions are counted - based on Case History Status of "Pending Response (Customer)".

Now I want to calculate the total time spent on the case - Before it was sent to customer and after it was received from Customer.
This is represented in column L - Blue for Time before customer, Yellow for Time after customer up to case closure.

I have tried Sumifs with Rank - but here I the rank of "Pending Response (Customer)" changes depending on how many different statuses were there before it for a given case.
I tried Sumproduct, but again the same issue.

I would like to do a Sumifs, based on a dynamic Rank, which is based on position of "Pending Response (Customer)" for that case number. Possibly tweak the same formula for calculating time before and after this status.
Needless to say, for cases where there is no "Pending Response (Customer)" the formula returns no value or zero value.

Thanks in advance. Sorry for the long post.

Abhishek

 

[sijpie]

I'll see if I can look at this later this week or early next. Please reply if you are still interested.

 

[abhishekadhikari84]

I'll see if I can look at this later this week or early next. Please reply if you are still interested.

Yes please... I still need the way...

 

[sijpie]

E	F	G	H	I	J	K	L		Q	R
Case Number	Date/Time Opened	Case History Status		Case History Last Modified	Status Rank		Time in Status		Case Number	Total Time in Comp
4075631​	01/03/2022 05:56​	New		01/03/2022 05:56​	1​		0:00​		4075631​	3:29​
4075631​	01/03/2022 05:56​	New		01/03/2022 05:56​	1​		0:19​		4075639​	5:45​
4075631​	01/03/2022 05:56​	Ready to be Assigned		01/03/2022 06:15​	3​		0:10​
4075631​	01/03/2022 05:56​	In Process		01/03/2022 06:25​	4​		0:00​
4075631​	01/03/2022 05:56​	In Process		01/03/2022 06:25​	4​		1:30​
4075631​	01/03/2022 05:56​	Closed		01/03/2022 07:55​	6​		0:00​
4075631​	01/03/2022 05:56​	Closed - Response received		01/03/2022 09:25​	7​		1:30​
4075631​	01/03/2022 05:56​	Closed		01/03/2022 10:55​	8​		0:00​
4075639​	02/03/2022 05:56​	New		02/03/2022 05:56​	1​		0:00​
4075639​	02/03/2022 05:56​	New		02/03/2022 05:56​	1​		0:15​
4075639​	02/03/2022 05:56​	Ready to be Assigned		02/03/2022 06:11​	3​		0:15​
4075639​	02/03/2022 05:56​	Assigned		02/03/2022 06:26​	4​		0:15​
4075639​	02/03/2022 05:56​	Ready to be Assigned		02/03/2022 06:41​	5​		0:15​
4075639​	02/03/2022 05:56​	In Process		02/03/2022 06:56​	6​		4:15​
4075639​	02/03/2022 05:56​	Pending Response		02/03/2022 11:11​	7​		0:00​
4075639​	02/03/2022 05:56​	Response received		03/03/2022 12:26​	8​		0:15​
4075639​	02/03/2022 05:56​	In Process		03/03/2022 12:41​	9​		0:15​
4075639​	02/03/2022 05:56​	Closed		03/03/2022 12:56​	10​		0:00​

Formula in column L (starting L3):
=IF(E3<>E4,0,IF(AND(ISERROR(SEARCH("pending response",G3)),G3<>"Closed"),I4-I3,0))

Formula in R3:
=SUMIF(E:E,N3,L:L)