Finding the max date and countifs?

[Alan32]

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.

 

[T. Valko]

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]

Can you use that formula to look for the max date across different sheets?

 

[T. Valko]

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.