excel - Conditional MAX function -


i'm using array formula return max value column (count) based on column (client name). each client has multiple account numbers repeated multiple times. intend return count of account number present in column (acct #) maximum times client.

i'm able satisfactory results except when 2 or more account numbers same client have same count want return null or blank not max count.

below example: acct # 4565 & 7898 repeated 6 time xyz ltd. since, xyz ltd. maximum time acct # repeated 6 more 1 account repeated 6 times, want return null or blank in column (count max)

table array formulas:

client name acct #   count                       count max xyz ltd.    1234    =countifs(b:b,b2,a:a,a2)    =max(if(a:a=a2,c:c)) xyz ltd.    4565    =countifs(b:b,b3,a:a,a3)    =max(if(a:a=a3,c:c)) xyz ltd.    4565    =countifs(b:b,b4,a:a,a4)    =max(if(a:a=a4,c:c)) xyz ltd.    4565    =countifs(b:b,b5,a:a,a5)    =max(if(a:a=a5,c:c)) xyz ltd.    4565    =countifs(b:b,b6,a:a,a6)    =max(if(a:a=a6,c:c)) xyz ltd.    1234    =countifs(b:b,b7,a:a,a7)    =max(if(a:a=a7,c:c)) xyz ltd.    1234    =countifs(b:b,b8,a:a,a8)    =max(if(a:a=a8,c:c)) xyz ltd.    4565    =countifs(b:b,b9,a:a,a9)    =max(if(a:a=a9,c:c)) xyz ltd.    1234    =countifs(b:b,b10,a:a,a10)  =max(if(a:a=a10,c:c)) xyz ltd.    4565    =countifs(b:b,b11,a:a,a11)  =max(if(a:a=a11,c:c)) xyz ltd.    7898    =countifs(b:b,b12,a:a,a12)  =max(if(a:a=a12,c:c)) xyz ltd.    7898    =countifs(b:b,b13,a:a,a13)  =max(if(a:a=a13,c:c)) xyz ltd.    7898    =countifs(b:b,b14,a:a,a14)  =max(if(a:a=a14,c:c)) xyz ltd.    7898    =countifs(b:b,b15,a:a,a15)  =max(if(a:a=a15,c:c)) xyz ltd.    7898    =countifs(b:b,b16,a:a,a16)  =max(if(a:a=a16,c:c)) xyz ltd.    7898    =countifs(b:b,b17,a:a,a17)  =max(if(a:a=a17,c:c)) 

table results:

clien name  acct #  count   count max xyz ltd.    1234    4       6 xyz ltd.    4565    6       6 xyz ltd.    4565    6       6 xyz ltd.    4565    6       6 xyz ltd.    4565    6       6 xyz ltd.    1234    4       6 xyz ltd.    1234    4       6 xyz ltd.    4565    6       6 xyz ltd.    1234    4       6 xyz ltd.    4565    6       6 xyz ltd.    7898    6       6 xyz ltd.    7898    6       6 xyz ltd.    7898    6       6 xyz ltd.    7898    6       6 xyz ltd.    7898    6       6 xyz ltd.    7898    6       6 

in example, expected result in column(count max) blank value.

or can using vba?

take formula working , place in formula replacing formula

=if(countifs($a$2:$a2,$a2,$b$2:$b2,$b2)>1,"",your formula) 

copy down far need. display "" when account name , # repeated , value of formula when first instance.

proof of concept

the formula using array formula have use ctrl+shift+enter, not enter when finish. when done add {} around formula cannot added manually. single cell array formula. once have done first cell, copy cell down required.


Comments

Popular posts from this blog

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

python Tkinter Capturing keyboard events save as one single string -

sql server - Why does Linq-to-SQL add unnecessary COUNT()? -