MAXIF and MINIF problem

[Fishboy]

Hi All,

Long time reader, first time poster here. Basically I am having an issue with reporting some of the data on one of my massive spreadsheets.

Ultimately what I am looking to do is have one cell report the maximum time taken to achieve a goal (read from column AJ3), but based on the specific location (read from column A). So far I have trawled through the web looking at various different solutions but none of them seem to be working, i.e they either give an error in a popup because it is broken, or it just returns a value of zero even though I know the answer is an actual number.

The kind of code I have been toying with is as follows (NB: The formula is on sheet3, and the data is on sheet1 labelled "Vetting Breakdown" btw):

=MAX(IF('Vetting Breakdown'!$A$3:$A$1048576="Luton",$AJ$3:$AJ$1048576))

My understanding of the above means I expected the formula to:
- Check everything between A3 to the end of Column A on the sheet named "Vetting Breakdown"
- Only count if the cell in column A contains "Luton"
- Then check everything between AJ3 to the end of column AJ and report the highest value

As I say, I already know the maximum value for this should come back as 43, but it is coming back with 0.

Does anyone have any ideas what I am doing wrong, or whether or not I am trying to achieve the impossible? I am having the exact same issue with the MIN version of the above formula as well, so anyhelp would be greatly appreciated.

Many thanks in advance

 

[XOR LX]

Thanks Xor, at least I know this should be working

All I need to do now is figure out why it isn't!

At the risk of being patronising, are you absolutely sure it's been committed as an array formula?

And did you check re my query about additional spacing?

Cheers

 

[Fishboy]

At the risk of being patronising...

It's ok mate, I'd rather check and double-check these things as nobody is perfect!

I am 100% certain it is an array formula contained in {} by pressing CTRL+SHIFT+Enter.

I am also 100% certain that the "Luton" values in column A do not have additional spacing (I manually re-entered them all to be sure).

I even added * wildcards either side of the "Luton" search criteria in the array formula to count any cells containing "Luton" as any part of the cell.

 

[XOR LX]

I don't suppose you can share the workbook somehow?

Regards

 

[Fishboy]

Hi again Xor,

I have added the dummy version of the macro-enabled workbook to my Onedrive so you should be able to grab a copy of it from here.

It is worth noting the array formula that is not working is currently located in cell S41 on the 3rd worksheet labelled MI by Location.

Let me know if you have any problems accessing the file.

 

[XOR LX]

Your formula is referencing only $AJ$3:$AJ$14!! But that's not that range on the Vetting Breakdown tab! It's by default the one in which the formula itself resides, i.e. MI By Location!

Should of course be 'Vetting Breakdown'!$AJ$3:$AJ$14​.

Can't believe I didn't think of this on first seeing your formula!

Regards

 

[Fishboy]

So I need to change the end of the formula to 'Vetting Breakdown'!$AJ$3:$AJ$14....

Doh! I'll give it a go!

 

[Fishboy]

Yay! Cheers Xor, you are a hero, and I am a muppet! I knew it had to be something simple I had missed!

PS - I hope you you weren't appalled by how many maximum cell range queries appear through out the sheet!

 

[XOR LX]

Yay! Cheers Xor, you are a hero, and I am a muppet! I knew it had to be something simple I had missed!

Ha! We all do it!

PS - I hope you you weren't appalled by how many maximum cell range queries appear through out the sheet!

Actually, I'm a bit puzzled. First of all, instead of e.g.:

A$3:$A$1048576

why not simply:

A$:$A

?

Also, referencing entire columns has no detrimental effect in most formulas. It's fine to do so in e.g. COUNTIF(S)/SUMIF(S), but not at all a good idea in array formulas (and I also include the likes of SUMPRODUCT and LOOKUP in that definition, even though they don't require CSE).

Regards

 

[Fishboy]

Actually, I'm a bit puzzled. First of all, instead of e.g.:

A$3:$A$1048576

why not simply:

A$:$A

?

Foolishness on my part I think!

Also, referencing entire columns has no detrimental effect in most formulas. It's fine to do so in e.g. COUNTIF(S)/SUMIF(S), but not at all a good idea in array formulas (and I also include the likes of SUMPRODUCT and LOOKUP in that definition, even though they don't require CSE).

Regards

Is this because of the extra processing required? I have noticed some of my heftier spreadsheets sometimes suffer from processing lag

 

[XOR LX]

Is this because of the extra processing required? I have noticed some of my heftier spreadsheets sometimes suffer from processing lag

Not quite sure of the technical reason. I've a hunch that it's due to functions such as COUNTIF(S)/SUMIF(S) possessing the in-built ability to detect the last used cell(s) in the range(s) passed to them, and hence only calculate over those ranges, an ability array formulas don't possess. Though I'm not at all sure.

Regards