Counting Question

[Gina]

I have a workbook where on the tab "Data"

tab "Data" Column A, Cells A2:A2500 Gives a Year Value (for example, 2007,
2008)

tab "Data" Column D, Cells D22500 Gives a specific Date (for example Feb
1, 2007, Feb 18, 2007)

tab "Data" Column C, Cells C2:C2500 Subtracts the Value of the date from the
current line from the date of the line previous (=D4-D3) and returns a number
value.

tab "Data" Column G, Cells G2:G2500 gives a the department of each record.
(For example "Admin".


What I am trying to do is in cell C20 of the "Frequencies" tab, I would like
to go back and find the largest number in "Data" worksheet, cells C2:C2500,
where the year is 2007, and returns the highest value in that range.

Could anyone please give me a hand with this?

Thank you,
Gina

 

[Pete_UK]

Try this array* formula:

=MAX(IF(Data!A2:A2500=2007,Data!C2:C2500))

*An array formula needs to be committed using Ctrl-Shift-Enter (CSE)
rather than the usual <Enter>. If you do this correctly then Excel
will wrap the formula in curly braces { } when viewed in the formula
bar - do not type these yourself. If you need to amend/edit the
formula then use CSE again.

Hope this helps.

Pete

 

[John C]

Modifying a formula by yshridhar on another post.

C20: =LARGE(IF((Data!A2:A2500=2007),Data!C2:C2500),1)

this is an array formula, and therefore instead of pressing enter, you must
press and hold CTRL+Shift+enter when entering the formula.

 

[Gina]

these worked great. i didn't understand what you meant originally, re:
ctrl/shift/enter, but I figured it out and it is exactly what I needed.
Thank you so much.
Gina

 

[Gina]

This worked great! Thank you again.

If I wanted to add a further filter to show only when the department was
"Admin", which is Data!G2:G2500=Admin, would I just add it to the formula
like:

=MAX(IF(Data!A2:A2500=2007,Data!C2:C2500,Data!G2:G2500=Admin))

?

Thanks!

 

[Pete_UK]

You're welcome, Gina - thanks for feeding back.

Pete

 

[Pete_UK]

No, you would need to do it like this:

=MAX(IF((Data!A2:A2500=2007)*(Data!G2:G2500="Admin"),Data!C2:C2500))

Again, commit with CSE.

Hope this helps.

Pete