RAJESH1960
Banned for repeated rules violations
- Joined
- Mar 26, 2020
- Messages
- 2,313
- 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: