# Max value using COUNTIF formula

#### Dishboy09

##### New Member
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!

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### jasonb75

##### Well-known Member
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

##### New Member
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

##### Well-known Member
Thanks for that
Your question is a little confusing, but I believe that the formula
Excel Formula:
``=MAXIFS(AT:AT,AT:AT,">0",AF:AF,T6)``
should do what you need.

#### Dishboy09

##### New Member

Thanks for that
Your question is a little confusing, but I believe that the formula
Excel 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

##### Well-known Member
The principle of the formula is
Excel Formula:
``=MAXIFS(score column,name column, name)``
so assuming names in AF and scores in AT it would be
Excel Formula:
``=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

##### MrExcel MVP, Moderator

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

##### New Member
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

##### New Member
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

##### MrExcel MVP, Moderator
All the ranges need to be the same size.

Replies
12
Views
415
Replies
9
Views
175
Replies
0
Views
110
Replies
11
Views
164
Replies
6
Views
43

1,130,219
Messages
5,640,958
Members
417,183
Latest member
CuteLeo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back