How to Calculate TAT automatically

[Gaurang Mhatre]

Hi Friends,

Hope everything is fine at your end. Really appreciate this site and members for helping out queries.
Anyways,
I have this excel file where I am calculating the TAT manually. For example for Doc received TAT, TAT is calculated by subtraction of “Actual TAT-Doc Recvd TAT” for column M.

Before entering the value of TAT in working sheet (i.e. column F), I have to filter the data in YTD sheet by filtering Product category.

For example, if I am looking TAT of Collection Electronic, then I will filter it in YTD sheet, then count the sum of “Actual-Doc Received date” column (column M) and then dividing it by count of Product (i.e. count of Collection Electronic) by this I will get the value of TAT, which I then enter in column F of working sheet. Same steps will be followed for other products as well. But, if there is any outstanding figure like 65,59, 175 etc in Column M of YTD sheet. Then I will exclude that in my calculation to avoid showing bad figures in working sheet column F

So is there any formula which will calculate it automatically, instead of doing it manually and also making sure that it does not include number exceeding 50 or more in column M of YTD sheet for that particular product.

Attached is excel file for your reference.

It would be great if you could help me out.

Thanks in advance for your help.

Regards,
Gaurang

 

[Jake Collins]

Dear Gaurang

This formula works out the TAT for the product in D7.

=SUMIFS(YTD!$M$2:$M$195,YTD!$F$2:$F$195,WORKING!D7,YTD!$M$2:$M$195,"<="&50)/COUNTIFS(YTD!$F$2:$F$195,WORKING!D7,YTD!$M$2:$M$195,"<="&50)

 

[srinidhi]

Hi Gaurang,

Hope this helps you.
=SUMIFS(YTD!M:M,YTD!M:M,"<50",YTD!F:F,D7)/COUNTIFS(YTD!M:M,"<50",YTD!F:F,D7)

 

[Haseeb A]

Hello Gaurang,

Take a look on AVERAGEIFS function,

=AVERAGEIFS(YTD!M:M,YTD!F:F,D7,YTD!M:M,"<=50")

If you have more conditions, just add it.

 

[Gaurang Mhatre]

Jake, Collins and Haseeb....Thanks alot for helping me out. Will try your formulas and will get back to you if there is any queries.

Thanks alot once again.
God bless!

Regards,
Gaurang

 

[Gaurang Mhatre]

Hi Gaurang,

Hope this helps you.
=SUMIFS(YTD!M:M,YTD!M:M,"<50",YTD!F:F,D7)/COUNTIFS(YTD!M:M,"<50",YTD!F:F,D7)

Hi Srinidhi,

Thanks for taking out your time for me.
Just one query. Why did u put YTD!M:M,YTD!M:M twice in sumifs formula and why there is no row number for M:M ?? I mean range i.e. for example range M1:M120 (if the M column data is till row 120)

Thanks in advance for your help!
Regards,
Gaurang

 

[srinidhi]

Hi Gaurang,

To answer your first question i.e why there are 2 YTD!M:M,YTD!M:M ?

the first YTD!M:M, is to sums the values (based on other conditions) in M:M Column from YTD sheet.
As you can see the formula YTD!M:M,"<50", in the second YTD!M:M I am specifying a condition. To sum only those values in M:M which are less than 50.

To answer your second ?, I have take the entire M:M Column, because in future if your data goes beyond 120 rows, then you need not change the formula.
if you specify M1:M120 & after sometime you data expands to M300 rows. you will have to modify the formula, but if you mention M:M in the formula & even if your data goes upto the last row in M:M you need not modify the formula, as M:M includes the entire M:M Column.