Counting MAX value in array formula

[rjacko10]

I use array formula all the time to do conditional counting, but I'm having trouble specifying the counting of max values.

Per row, I need to count the max number of dates present per ID number

colA = ID number
colB = Date
colC = my formula

As an example I'm using this on row 6, but it is not working as I had hoped:

{=SUM(IF(A:A=A6,IF(MAX(B:B)=B6,1,0)))}

It correctly returns 0 when column B isn't a max value. However when it is the max date it counts all the rows where column A values are equal.

I've been stuck on this for a while. Please can someone help me?

Kind regards,
Rob

[AliGW]

Will you please attach a sample Excel workbook?

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[rjacko10]

Thanks AliGW - I've just uploaded an example spreadsheet

[AliGW]

Sorry - I don't understand the expected results: why 1 in D8 when there is only one instance of the date? Is this really a counting exercise rather than a sum???

[rjacko10]

Sorry! - I'm trying to count the maximum dates only per ID.

So in row 8, 08/01/2017 is the maximum date for that ID number, and it only occurs once, so I just want a 1 returned.

Conversely, rows 4 and 5 share the maximum date for ID 1, so I would like 2 to be returned for both of those rows, as there are 2 maximum dates for this ID number.

I hope this makes sense!

[xlnitwit]

Hi,

Perhaps this formula array entered (using Ctrl+Shift+Enter) into D2
=IF(B2=MAX(IF($A$2:$A$10=A2,$B$2:$B$10)),COUNTIFS(A:A,A2,B:B,B2),0)
note that the blue section should be limited to as few rows as possible for performance reasons.

[AliGW]

So it IS a counting exercise, not a sum!!! Doe post #6 give you your answer?

[rjacko10]

Thank you guys!! It seems to work.

Yes, sorry AliGW, it is counting but I always use SUM(IF('logic statement',1,0)) to do my array counting. I thought this was generically the correct method to use.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.