Max value using COUNTIF formula

Dishboy09

New Member
Joined
Jun 11, 2017
Messages
32
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,682
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 11, 2017
Messages
32
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,682
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 11, 2017
Messages
32
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.


NameScore
Bob50
Bob50
John60
Steve70
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,682
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 11, 2017
Messages
32
Office Version
  1. 365
Platform
  1. Windows
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.

NameScoreScore 2Score 3
Bob50644
Bob502555
John60677
Steve70999
 

Dishboy09

New Member
Joined
Jun 11, 2017
Messages
32
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
56,915
Office Version
  1. 365
Platform
  1. Windows
All the ranges need to be the same size.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top