Max with criteria

[toony]

Hi. I have the below issue.

I have the below formula that works;
=MAX(IF(AD:AD="office supplies",AP:AP))

Then I have this othee formula which gives me 0 for the Furniture criteria......but the highest is not 0....
It's just the same formula but different criteria. I checked the spellling and all but can't just see why it woul give me 0 value. Any ideas?
=MAX(IF(AD:AD="Furniture",AP:AP))

 

[etaf]

what version of excel ?
any spaces either side of the text ?

if an older version of excel - not 365 , then you will probably need to enter as an array
“Ctrl+Shift+Enter”
so you will see {} around the formula - but

365 version automatically makes array formulas arrays

Also its not good practice to refer to full columns - a range of rows maybe more efficient

if you actually type in furniture into a cell and then a number, does it pickup that number ?

 

[FormR]

If you're not using an ancient version of Excel then you can use MAXIFS() with full column references, without risk of performance issues.

For example:
=MAXIFS(AP:AP,AD:AD,"Furniture")

To help diagnose your specific issue, what does the below formula return when placed in a spare cell and the AD1 reference changed to a cell that looks to contain the text "furniture"?

=AD1="Furniture"

 

[DanteAmor]

Another option with "regular" formula.

=SUMPRODUCT(MAX((AD1:AD1000="Furniture")*(AP1:AP1000)))

It is recommended in any formula to use a certain range.

 

[FormR]

Another option with "regular" formula.

Hi Dante, your suggested formula is not "regular" and suffers exactly the same performance issues as the OP's

It is recommended in any formula to use a certain range.

To some extent, but with maxifs() and their kind, you'd be hard pressed to notice any difference in performance in a real world scenario using full column references or not.

 

[DanteAmor]

Hi Dante, your suggested formula is not "regular" and suffers exactly the same performance issues as the OP's

I know and that's why I put it in quotes, and I also know about the performance problem, that's why my recommendation, which doesn't hurt.

 

[FormR]

I know

and I also know about the performance problem

Then why even offer it as an alternative

 

[DanteAmor]

Then why even offer it as an alternative

Sometimes users have trouble entering array formulas with shift + control + enter, it is an alternative to enter the formula with just enter.
If they don't have 365, it's still a viable alternative.

 

[toony]

Hi. Thank you all. None is actually working. The strange part is that the same formula (=MAX(IF(AD:AD="office supplies",AP:AP))) works just fine for the "Support Office" criteria but not for "Furniture". Strange. I have used a range as advised.
My MS is 2016 (Microsoft® Excel® 2016 MSO (Version 2112 Build 16.0.14729.20254) 64-bit)

The feedback for each of you is as follows, but no success to summarise;
- Etaf: I've entered it as arrary formula with “Ctrl+Shift+Enter” and it seems it to work but actually not. It was giving me 0 with mine. With the array formula is gives me 7 which is not correct. The result for the Furniture criteria should be 3.
- FormR; the below funtion I don't have access to it. I have formulated =AD2="Furniture" and it gives the relevant "TRUE" statement.
This was a simply and interesting diagnosis excersice actually thanks. Would this also be called validation?
=MAXIFS(AP:AP,AD:AD,"Furniture")-
- DanteAmor; The below gives me 0 result which should be 3 actually for Furniture.
=SUMPRODUCT(MAX((AD1:AD1000="Furniture")*(AP1:AP1000)))

 

[RoryA]

With the array formula is gives me 7 which is not correct

How are you verifying that?