Issue with CountIFS / SUMPRODUCT with multiple criteria

[JayB0730]

Hello,

I have an excel able that I am calculating count based on multiple criteria (Last Shipped Column & Status Column). So far the below formulas (1-4) work. However, I now I have to add a 3rd criteria and can't seem to get the formula's to work with either COUNTIFS or SUMPRODUCT.

The 3rd scenario is where Expiration Date (NonOptTBL[Expiration]) is either <= Today() or >Today() AND <=90.

1. Last shipped date is 1-30 days & status is NOT "I".

=COUNTIFS(NonOptTBL[Days Last Shipped],">"&0,NonOptTBL[Days Last Shipped],"<="&30,NonOptTBL[Status],"<>"&"I")

2. Last shipped date is 31-60 days & status is NOT "I".

=COUNTIFS(NonOptTBL[Days Last Shipped],">"&30,NonOptTBL[Days Last Shipped],"<="&60,NonOptTBL[Status],"<>"&"I")

3. Last shipped date is 61-90 days & status is NOT "I".

=COUNTIFS(NonOptTBL[Days Last Shipped],">"&60,NonOptTBL[Days Last Shipped],"<="&90,NonOptTBL[Status],"<>"&"I")

4. Last shipped date is GREATER than 90 days OR No Delivery Date is present & status is NOT "I".

=(SUMPRODUCT((NonOptTBL[Days Last Shipped]>90)*(NonOptTBL[Status]<>"I")))+SUMPRODUCT((NonOptTBL[Days Last Shipped]=0)*(NonOptTBL[Status]<>"I"))

TIA.
Jay

 

[Marcelo Branco]

I think the 3rd scenario can be simplified to
NonOptTBL[Expiration]<=TODAY()+90

Am i right?

M.

 

[JayB0730]

Hi Marcelo,

Hmm you may be right. But how would I be able to include that 3rd scenario and let's say introduce 4,5,6, etc. scenarios? I feel stuck especially with formula #4 .

 

[Marcelo Branco]

According to post 1, the criteria for formula 4 are
Last shipped date is GREATER than 90 days OR No Delivery Date is present & status is NOT "I".

I'm supposing you mean
(Days Last Shipped>90 Or Days Last Shipped=0) AND Status<>"I"

If so maybe something like this
=SUMPRODUCT(--((NonOptTBL[Days Last Shipped]=0)+(NonOptTBL[Days Last Shipped]>90)),--(NonOptTBL[Status]<>"I"))

To use an OR condition in an array formula you should add the two conditions.

M.