Max value using COUNTIF formula

[Dishboy09]

Needing some help to figure out this formula.

I am using COUNTIF currently of this =COUNTIFS(AF:AF,T6,AT:AT,">0") to find # of 0-1 values in columns. This certain part of my sheet uses a different setup. This particular one gives a certain # but on say half of the entries. I need a max value formula to search the same criteria in the countif but find the max value associated with T6 from AF:AF in AT:AT??

THANKS!

 

[jasonb75]

Please update your account to show which version of excel you are using by clicking your username at the top right of this page then going to account details, checking the correct box for your version of excel then scrolling down and saving the changes.

Your question would be easiest solved with MAXIFS but we don't know if you have it available or if you need an alternative method.

 

[Dishboy09]

Please update your account to show which version of excel you are using by clicking your username at the top right of this page then going to account details, checking the correct box for your version of excel then scrolling down and saving the changes.

Your question would be easiest solved with MAXIFS but we don't know if you have it available or if you need an alternative method.

I have made the additions to my profile. I am using Office365 version on version 1910.

 

[jasonb75]

Thanks for that
Your question is a little confusing, but I believe that the formula

=MAXIFS(AT:AT,AT:AT,">0",AF:AF,T6)

should do what you need.

 

[Dishboy09]

Thanks for that
Your question is a little confusing, but I believe that the formula

=MAXIFS(AT:AT,AT:AT,">0",AF:AF,T6)

should do what you need.

So here is my question in better detail. My sheet has tons of entries like this. Each person will have multiple entries and the score is the same in this column for each of their entries.

I need the formula to Find the max value in the column for the particular person and display it in a cell.

So bobs would be 50 and so forth.

Name	Score
Bob	50
Bob	50
John	60
Steve	70

 

[jasonb75]

The principle of the formula is

=MAXIFS(score column,name column, name)

so assuming names in AF and scores in AT it would be

=MAXIFS(AT:AT,AF,AF,AF6)

looking at your example above, I don't think that the ">0" will be required in this formula, but it it is that can be added easily.
The confusing part was why you were using AF:AF,AT6 as a criteria pair in your formula when they appear to have different data types and should not be comparable.

 

[Fluff]

Considering you said

Each person will have multiple entries and the score is the same in this column for each of their entries.

As all the values will be the same, why do you need to find the "max" value?

 

[Dishboy09]

Considering you said

As all the values will be the same, why do you need to find the "max" value?

Some of the columns in my data do not have same values, but the one does. I am pulling this data together and just need this column to report the max number instead of adding them all together.

Name	Score	Score 2	Score 3
Bob	50	6	44
Bob	50	25	55
John	60	6	77
Steve	70	9	99

 

[Dishboy09]

Considering you said

As all the values will be the same, why do you need to find the "max" value?

Here is what I have come up with but getting VALUE error.

=MAXIFS(AQ:AQ,T6:T20,T6,AF:AF,T6)

AQ Range im looing at, T6:T20 names range, T6 is name, AF:AF is range with data names, T6 is name

 

[Fluff]

All the ranges need to be the same size.