Finding the max date and countifs?

Alan32

New Member
Joined
Mar 8, 2011
Messages
3
Here's the setup of my spreadsheet:
A1 = 1/1/2011
A2:A10 = names
B1 = 1/2/2011
B2:B10 = names

Elsewhere in the worksheet, I have all the names listed. I am trying to write a formula that would list next to each name the max date whose column the name is in at the top of the sheet. I've tried several things, including:
=max(if(countif(A2:A5,"name")=1,A1,if(countif(B2:B5,"name")=1,B1)*A1,B1)
No luck and unfortunately, no idea where I'm going wrong.

Thanks for any advice you may have.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Here's the setup of my spreadsheet:
A1 = 1/1/2011
A2:A10 = names
B1 = 1/2/2011
B2:B10 = names

Elsewhere in the worksheet, I have all the names listed. I am trying to write a formula that would list next to each name the max date whose column the name is in at the top of the sheet. I've tried several things, including:
=max(if(countif(A2:A5,"name")=1,A1,if(countif(B2:B5,"name")=1,B1)*A1,B1)
No luck and unfortunately, no idea where I'm going wrong.

Thanks for any advice you may have.
Try this array formula**.

=MAX(IF(A2:B10="name",A1:B1))

D1 = name

=MAX(IF(A2:B10=D1,A1:B1))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.
 

Alan32

New Member
Joined
Mar 8, 2011
Messages
3
Can you use that formula to look for the max date across different sheets?
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Can you use that formula to look for the max date across different sheets?
How many sheets?

If there are only a "couple/few" sheets you could do something like:

=MAX(MAX(IF(Sheet1!A2:B10="name",Sheet1!A1:B1)),MAX(IF(Sheet2!A2:B10="name",Sheet2!A1:B1)))

What you could do is put that formula on each sheet in the same cell like cell X1. Then, you can get the max across the sheets like this:

=MAX(Sheet1:Sheet10!X1)

Use the appropriate sheet names.
 

Forum statistics

Threads
1,141,850
Messages
5,708,990
Members
421,602
Latest member
jkpce1880

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