Countifs & Max

[spydey]

Hey there, so I have been running a Countifs formula for a while, and it works great. Love being able to do multiple criteria.

Basically it is:

=Countifs(Items,Item,Locations,Location,Dates,">"&Date)

But now I need to include a MAX for the Dates section. Basically I need to take the MAX date out of two dates and incorporate it into my already existing formula. I am not sure how I would do that, something tells me that the below is not correct. I just have a feeling .....

In my mind, I see it as:

=Countifs(Items, Item, Locations, Location, Max(Dates1,Dates2), ">"&Date)

Dates1 & Dates2 are separate ranges.

I am running excel 2013, so I don't have access to Maxif or Maxifs, as those were introduced in 2016.

I have tried the above formula using CSE, and it doesn't work, it gives me an error. Mot likely due to the MAX formula being nested in the countifs.



I was thinking of something like

=Max(Countifs(Items, Item, Locations, Location, Dates, ">"&Date1),Countifs(Items, Item, Locations, Location, Dates, ">"&Date2))

But that will just give me the largest number of the counts between those two criteria, which is not what I want.



I thought of something like maybe:

=Count(If((Items=Item)*(Locations=Location),If(Max(Dates1,Dates2)>Date)))

Or something similar, but wasn't exactly sure how the syntax should go, due to multiple criteria.



Kind of clueless here and looking for some direction .....

Any thoughts?

-Spydey

 

[spydey]

One other tidbit that might be helpful.

If Dates1 is blank, Dates2 will always be blank.

If Dates1 is not blank, Dates2 could be blank.

If both Dates1 and Dates2 are not blank, Dates2 will always be the MAX between Dates1 & Dates2.

Don't know if that helps or not, but I thought it useful.

-Spydey

 

[Eric W]

Try:

=SUM((Items=Item)*(Locations=Location)*(IF(Dates2<>"",Dates2,Dates1)>Date))

and confirm it by pressing Control+Shift+Enter in the formula bar.

 

[spydey]

Try:

=SUM((Items=Item)*(Locations=Location)*(IF(Dates2<>"",Dates2,Dates1)>Date))

and confirm it by pressing Control+Shift+Enter in the formula bar.

Thanks Eric! I will give it a try here in a moment.

Question for you: Why would =Countifs(Items,Item,Locations,Location,Max(Dates1,Dates2),">"&Date) via CSE, no work?

I think it has something to do with trying to do a function inside of the Countifs ... but am not sure.

Thanks again for your help!

-Spydey

 

[Eric W]

COUNTIFS is very particular about its format. It's always "(range,condition,range,condition...)". You are trying to apply a function to the range giving "(range,condition,function(range),condition...)". That is not allowed, since the result of function(range) is an array, not a range.

Once we're past that, why can't we just use this?

=SUM((Items=Item)*(Locations=Location)*(MAX(Dates1,Dates2)>Date))

Because MAX works on the ENTIRE range, not just 2 items, one from Dates1 and one from Dates2. So the first two conditions result in n rows, but the last only 1 row, containing useless information. So we need to come up with a way to look at one row at a time, which is why I used IF instead of MAX.

 

[spydey]

COUNTIFS is very particular about its format. It's always "(range,condition,range,condition...)". You are trying to apply a function to the range giving "(range,condition,function(range),condition...)". That is not allowed, since the result of function(range) is an array, not a range.

Once we're past that, why can't we just use this?

=SUM((Items=Item)*(Locations=Location)*(MAX(Dates1,Dates2)>Date))

Because MAX works on the ENTIRE range, not just 2 items, one from Dates1 and one from Dates2. So the first two conditions result in n rows, but the last only 1 row, containing useless information. So we need to come up with a way to look at one row at a time, which is why I used IF instead of MAX.

Fantastic!! Thanks for the very thorough explanation. I appreciate your help Eric.

-Spydey

 

[spydey]

Out of curiosity, could we use sumproduct to achieve the same thing?

I am always looking for multiple ways to do the same thing.

Would it have any benefit over the formula you wrote?

Thanks again!

-Spydey

 

[Eric W]

True, SUMPRODUCT was my first stab at it too. You can actually use SUMPRODUCT instead of SUM in the formula I provided, but you'll still need to use Cntl+Shift+Enter. SUMPRODUCT has some array processing abilities, so

=SUMPRODUCT((Items=Item)*(Locations=Location))

would actually work. But again, the interior IF function gets us. This part must be processed as an array function, but it is evaluated before the SUMPRODUCT array processing kicks in. Which is why CSE is required. And since CSE is required, I prefer to use SUM over SUMPRODUCT.

There may actually still be ways to create a function that doesn't require CSE, even though it still has array processing. Some people prefer those, because if someone unfamiliar with CSE uses it, they won't mess it up by not pressing CSE. But those formulas tend to be more complicated, and I think the version I gave you is fairly elegant.

 

[spydey]

True, SUMPRODUCT was my first stab at it too. You can actually use SUMPRODUCT instead of SUM in the formula I provided, but you'll still need to use Cntl+Shift+Enter. SUMPRODUCT has some array processing abilities, so

=SUMPRODUCT((Items=Item)*(Locations=Location))

would actually work. But again, the interior IF function gets us. This part must be processed as an array function, but it is evaluated before the SUMPRODUCT array processing kicks in. Which is why CSE is required. And since CSE is required, I prefer to use SUM over SUMPRODUCT.

There may actually still be ways to create a function that doesn't require CSE, even though it still has array processing. Some people prefer those, because if someone unfamiliar with CSE uses it, they won't mess it up by not pressing CSE. But those formulas tend to be more complicated, and I think the version I gave you is fairly elegant.

Yes, I agree that the version you gave me is quite simple, straight forward, and elegant. I am not too worried about others getting into it and messing it up. I am converting all of my formulas for this workbook to VBA so they won't even be in-cell. But I had to update a few formulas first, and this was one of them.

Thanks for your help again, I really appreciate it.

-Spydey