RAJESH1960
Active Member
 Joined
 Mar 26, 2020
 Messages
 464
 Office Version

 2019
 Platform

 Windows
Hello Experts,
I am trying to sort the entered data horizantally. The problem is in the amounts in columns N, P, R, T are the same. It has to show the amount as per the entered data. The columns are taking the amount to all the cells as the name of the particulars is the same. How do I rectify this problem.?
I am trying to sort the entered data horizantally. The problem is in the amounts in columns N, P, R, T are the same. It has to show the amount as per the entered data. The columns are taking the amount to all the cells as the name of the particulars is the same. How do I rectify this problem.?
Cell Formulas  

Range  Formula  
H3:H13  H3  =SUM(N3+P3+R3+T3+V3+X3+Z3+AB3) 
I3:I13  I3  =IF(A3="","",IF(D2=D3,I2+1,1)) 
J3:J13  J3  = IFERROR(INDEX(A:A,MATCH(K3,D:D,0)),"") 
K3:K13  K3  =IFERROR(INDEX($D$3:$D$501,MATCH(0,COUNTIF($K$2:$K2,($D$3:$D$501)),0)),"") 
L3:L13  L3  = IFERROR(INDEX(B:B,MATCH(K3,D:D,0)),"") 
M6:M13,M3:M4,AA3:AA13,Y3:Y13,W3:W13,U3:U13,S3:S13,Q3:Q13,O3:O13  M3  =IFERROR(VLOOKUP($K3&"/"&M$2,CHOOSE({1,2},$D$3:$D$501&"/"&$I$3:$I$501,$C$3:$C$501),2,0),"") 
N3:N13  N3  =SUMPRODUCT(($C$3:$C$501=M3)*($D$3:$D$501=K3)*($E$3:$F$501)) 
P3:P13  P3  =SUMPRODUCT(($C$3:$C$501=O3)*($D$3:$D$501=K3)*($E$3:$F$501)) 
R3:R13  R3  =SUMPRODUCT(($C$3:$C$501=Q3)*($D$3:$D$501=K3)*($E$3:$F$501)) 
T3:T13  T3  =SUMPRODUCT(($C$3:$C$501=S3)*($D$3:$D$501=K3)*($E$3:$F$501)) 
V3:V13  V3  =SUMPRODUCT(($C$3:$C$501=U3)*($D$3:$D$501=K3)*($E$3:$F$501)) 
X3:X13  X3  =SUMPRODUCT(($C$3:$C$501=W3)*($D$3:$D$501=K3)*($E$3:$F$501)) 
Z3:Z13  Z3  =SUMPRODUCT(($C$3:$C$501=Y3)*($D$3:$D$501=K3)*($E$3:$F$501)) 
AB3:AB13  AB3  =SUMPRODUCT(($C$3:$C$501=AA3)*($D$3:$D$501=K3)*($E$3:$F$501)) 
M5  M5  =IFERROR(VLOOKUP($K5&"/"&M$2,CHOOSE({1,2},$D$3:$D$501&"/"&$I$3:$I$501,$C$3:$C$501),2,0),"") 
Press CTRL+SHIFT+ENTER to enter array formulas. 
Cells with Conditional Formatting  

Cell  Condition  Cell Format  Stop If True  
H2062:H1048576,H2:H36  Cell Value  <0  text  NO 
H2062:H1048576,H2:H36  Cell Value  >0  text  NO 
Last edited: