Using Sumproduct to do Maxif with non-numeric values in series

[JohnLynam]

Hi All,

This is my first post so aplogies if I have messed up anything. I have searched for answers but can't find something sprecifically addressing this issue so if someone can help me out with this it would be much appreciated.

First, here is a sample dataset:

1/1/2014	31/1/2014	1
1/2/2014	28/2/2014	2
1/3/2014	31/3/2014	3
1/4/2014	30/4/2014	Black
1/5/2014	31/5/2014	5
1/6/2014	30/6/2014	6
1/7/2014	31/7/2014	7
1/8/2014	31/8/2014	8
1/9/2014	30/9/2014	9
1/10/2014	31/10/2014	10

<TBODY>
</TBODY>

There is one non-numeric value in the series. I would like to find the maximum numeric value of the third column for any two dates I choose. If all of the values were numeric then I could simply use:


=SUMPRODUCT( MAX( (A1:A10>=StartDate) * (B1:B10<=EndDate) * C1:C10 )

where StartDate and EndDate are dates.

This works. But it does not work if there is a non-numeric value.

Generally, I can work around non-numeric values in a sumproduct with something like:


=SUMPRODUCT( --(A1:A10>=StartDate) , --(B1:B10<=EndDate) , C1:C10 )


This one obviously coerces the true/false into 1/0 but importantly it will not work with an asterisk - it needs commas separating the arrrays.

So - I can use sumproduct with non-numeric values and I can use the Sumproduct/Max for numeric values but I can't seem to do both - sumproduct/max with non-numeric values. If anyone can show me what I am missing it would be great. For exmaple, what formula would let me find the max of column C for dates between (and including) 1/1/2014 and 31/7/2014 but looking up the full range of data.

Note - please don't tell me it can be done with an array formula. I specifically want an answer that does not rely on an array formulae - if there is one.

Thanks in advance.

 

[XOR LX]

Note - please don't tell me it can be done with an array formula. I specifically want an answer that does not rely on an array formulae - if there is one.

Hi.

Can I first ask as to your reasons for making such a statement?

I'm always surprised to see people declaring such requirements, which, in my experience, generally seem to stem from some sort of "array formula paranoia" which does the rounds on the internet from time to time.

Not that I'm suggesting that you yourself have caught that particular bug, of course, which is why I ask.

By the way, I take it that you're aware that, required keystroke combination aside, SUMPRODUCT itself is, to all intents and purposes, also an array formula?

Regards

 

[Peter_SSs]

Agreeing with XOR LX's comments.
This formula also is really an array formula but, like SUMPRODUCT, does not require the CSE entry. It does require Excel 2010 or later though.

=AGGREGATE(14,6,C1:C10/(A1:A10>=StartDate)/(B1:B10<=EndDate),1)

 

[JohnLynam]

I must confess I don't like arrays. I don't like the fact that I have to use CSE and my memory is that array formulae applied to large ranges slow down more than using the equivalent sumproduct formula - although I have not tested this rigourously.


But the reason for this post is intellectual curiosity. I have a spreadsheet that was using sumproducts happily to perform maxif calculations with multiple criteria and then some non-numeric values crept into the sheet (not errors). I tried, out of curiosity to solve it, but have not beenable to. So I thought I would throw the question out to some much more clever people on this forum.

It has also highlighted to me that I do not understand exactly how sumproduct works - since the use of an asterisk can produce a different answer to the use of a comma when separating arrays. So if anyone can provide colour on that it would be great too.

Cheers

 

[JohnLynam]

Peter,

Thanks also for your reply. I have never used the aggregate function. I will read up on it.