Countifs & Averageifs

[waqasbutt]

Hi All,

Need help, I do not know how to calculate below scenarios;

1) I need monthly combine results in column K based on:

1.1 - Date falls in specific month e.g January 2019
1.2 - Result (K) = Yes
1.3 - Closing_Date (C) is = or greater then open_Date (B)

1) I need monthly average in LK based on:

1.1 - Date falls in specific month e.g January 2019
1.2 - Result (K) = Yes
1.3 - Closing_Date (C) is = or greater then open_Date (B)

Upload Image


Regards,
Waqas

 

[jasonb75]

First, use this formula in F2 and fill down to check it the close date is greater than or equal to the open date.

=IF(C2>=B2,"Yes","No")

Then in K2, use this formula for your count result

=COUNTIFS(B:B,">="&(EOMONTH(J2,-1)+1),B:B,"<"&(EOMONTH(J2,0)+1),D:D,"Yes",F:F,"Yes")

and in L2 for the average

=AVERAGEIFS(E:E,B:B,">="&(EOMONTH(J2,-1)+1),B:B,"<"&(EOMONTH(J2,0)+1),D:D,"Yes",F:F,"Yes")

 

[waqasbutt]

Dear @jasonb75,

Sorry for late reply. I just tried it works perfect. Appreciated your help.

Regards,
Waqas

 

[waqasbutt]

One question, can you use multiple criteria in average formula?
If in column D, I have Yes, No & Perhaps as input and average formula give results based on Yes & Perhaps.

I tried but it do not work.

=AVERAGEIFS(E:E;B:B;">="&(EOMONTH(J2;-1)+1);B:B;"<"&(EOMONTH(J2;0)+1);D:D;"Yes";F:F;"Yes";D:D;"Perhaps";F:F;"Yes")


Regards,
Waqas

 

[jasonb75]

Not with averageifs, you need an alternative method using sumifs and countifs. The formula would work with averageifs, but in most cases the results will be incorrect.

=SUM(SUMIFS(E:E;B:B;">="&(EOMONTH(J2;-1)+1);B:B;"<"&(EOMONTH(J2;0)+1);D:D;{"Yes";"Perhaps"};F:F;"Yes"))/SUM(COUNTIFS(B:B;">="&(EOMONTH(J2;-1)+1);B:B;"<"&(EOMONTH(J2;0)+1);D:D;{"Yes";"Perhaps"};F:F;"Yes"))

The way that I have entered the Yes and Perhaps criteria in the formula looks for cells where D:D="Yes" or D:D="Perhaps", the way that you tried it the formula is looking for cells where D:D="Yes" and D:D="Perhaps", as you can't have "Yes" and "Perhaps" in the same cell (unless they are in a sentence and used with wildcards), it always results in #DIV/0!

 

[waqasbutt]

Hmmm, What if I want to get one average value for all where results in column D "Yes & Perhaps".
Can use Averageifs(E:E;B:B; D:D;{"Yes";"Perhaps")?

It ideally give average -328 (which is E2,3,4,6,7,8,10,11,12,13)

 

[jasonb75]

You still need to use the same method as in my previous post. You can only use average functions with 1 criteria per column.

 

[waqasbutt]

Hi,

I have an issue Averageifs does not give results based on multiple criteria;

Problem ID​	Submit Date​	Status​	Month​	Average Days​
Issue 1​	29/11/2018 15:51​	Cancelled​	Nov-18​	-458​
Issue 2​	30/11/2018 14:57​	Cancelled​	Nov-18​	-457​
Issue 3​	04/12/2018 15:34​	Closed​	Dec-18​	-453​
Issue 4​	04/12/2018 15:43​	Cancelled​	Dec-18​	-453​
Issue 5​	05/12/2018 16:14​	Closed​	Dec-18​	-452​
Issue 6​	07/12/2018 12:05​	Cancelled​	Dec-18​	-450​
Issue 7​	07/12/2018 13:04​	Closed​	Dec-18​	-450​
Issue 8​	07/12/2018 13:53​	Cancelled​	Dec-18​	-450​
Issue 9​	12/12/2018 10:26​	Closed​	Dec-18​	-445​
Issue 10​	12/12/2018 10:57​	Cancelled​	Dec-18​	-445​
Issue 11​	12/12/2018 14:59​	Closed​	Dec-18​	-445​
Issue 12​	19/12/2018 10:53​	Closed​	Dec-18​	-438​
Issue 13​	19/12/2018 11:08​	Closed​	Dec-18​	-438​
Issue 14​	20/12/2018 10:13​	Closed​	Dec-18​	-437​
Issue 15​	27/12/2018 10:50​	Pending​	Dec-18​	-430​
Issue 16​	27/12/2018 12:40​	Closed​	Dec-18​	-430​
Issue 17​	27/12/2018 12:46​	Closed​	Dec-18​	-430​
Issue 18​	27/12/2018 12:56​	Closed​	Dec-18​	-430​
Issue 19​	27/12/2018 13:02​	Closed​	Dec-18​	-430​
Issue 20​	27/12/2018 13:15​	Closed​	Dec-18​	-430​
Issue 21​	27/12/2018 16:20​	Cancelled​	Dec-18​	-430​
Issue 22​	27/12/2018 17:21​	Closed​	Dec-18​	-430​
Issue 23​	02/01/2019 12:08​	Closed​	Jan-19​	-424​
Issue 24​	04/01/2019 14:51​	Closed​	Jan-19​	-422​
Issue 25​	04/01/2019 19:43​	Cancelled​	Jan-19​	-422​
			Average​	-439.16​

Status​

Cancelled​

Closed​

Pending​

=AVERAGEIFS(E1:E26;C1:C26;B30;C1:C26;C30;C1:C26;D30)​

Regards,
Waqas

 

[jasonb75]

Again, see post 5.

You can not do what you want with any of the average functions.

 

[waqasbutt]

Hi @jasonb75 , ok I will try that. Thank you.