Need Help to Correct the formula

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
478
Office Version
  1. 2019
Platform
  1. Windows
Hello ,
In my sample data it was working fine. But when I tried in my original data it is not working.
I have changed the range in the formula in the first row only. I am getting the right answer. If I drag down the formula to 1000 rows, I am getting an error from the 2nd row onwards. I tried unlocking the freeze cells but it ain’t working. What could be the problem.?. I would really appreciate if someone can tell me or correct the formula for the “J” column. The rest of the columns I could change accordingly.
Query to sort multiple data horizontally.xlsx
ABCDEFGHIJKLMNOPQRST
1DATEPARTICULARSNUMBERHelperDEBITCREDITVoucher No.1Amt2Amt3Amt4Amt5Amt6Amt
201-02-2021January81151-37928115January-3792 0 0 0 0 0
301-02-2021February81161-83808116January0 0 0 0 0 0
401-02-2021March81171-96008117March-9600 0 0 0 0 0
501-02-2021April81181-19708118April-1970 0 0 0 0 0
601-02-2021May81191-81658119May-8165 0 0 0 0 0
701-02-2021June81201-130638120June-13063 0 0 0 0 0
801-02-2021July81211-150008121July-15000August645September10015October1056November2600December684
901-02-2021August8121206454348January10000 0 0 0 0 0
1001-02-2021September812130100154349February4000 0 0 0 0 0
1101-02-2021October81214010561490March-15000 0 0 0 0 0
1201-02-2021November81215026008144 0 0 0 0 0 0
1301-02-2021December8121606848145 0 0 0 0 0 0
1402-02-2021January434810100008146 0 0 0 0 0 0
1502-02-2021February43491040008147 0 0 0 0 0 0
1602-02-2021March14901-150008148 0 0 0 0 0 0
1702-02-2021April81441-148508149 0 0 0 0 0 0
1802-02-2021May81451-140008150 0 0 0 0 0 0
1902-02-2021June81461-17568151 0 0 0 0 0 0
2002-02-2021July81471-64068152 0 0 0 0 0 0
2102-02-2021August81472064058153 0 0 0 0 0 0
2202-02-2021September81473018154 0 0 0 0 0 0
2302-02-2021October81481-236508155 0 0 0 0 0 0
2402-02-2021November81491-139058156 0 0 0 0 0 0
2502-02-2021December81501-73898157 0 0 0 0 0 0
2602-02-2021January81511-24554355 0 0 0 0 0 0
2702-02-2021February81521-70531492 0 0 0 0 0 0
2802-02-2021March81531-12904358 0 0 0 0 0 0
2902-02-2021April81541-32701493 0 0 0 0 0 0
3002-02-2021May81551-75158165 0 0 0 0 0 0
3102-02-2021June81561-70008166 0 0 0 0 0 0
3202-02-2021July81562066454359 0 0 0 0 0 0
3302-02-2021August8156303558167 0 0 0 0 0 0
3402-02-2021September81571-30008168 0 0 0 0 0 0
3503-02-2021October435510108688169 0 0 0 0 0 0
3603-02-2021November14921-25008170 0 0 0 0 0 0
3703-02-2021December43581011808171 0 0 0 0 0 0
3803-02-2021January43582-2368172 0 0 0 0 0 0
3903-02-2021February43583-2364360 0 0 0 0 0 0
4003-02-2021March43584-2364361 0 0 0 0 0 0
4103-02-2021April43585-2364362 0 0 0 0 0 0
4203-02-2021May43586-2364379 0 0 0 0 0 0
Solved
Cell Formulas
RangeFormula
I2:I3,S2,Q2,O2,M2,K2I2=IFERROR(VLOOKUP($H2&"/"&I$1,CHOOSE({1,2},$C$2:$C$2000&"/"&$D$2:$D$2000,$B$2:$B$2000),2,0),"")
J2J2=SUMPRODUCT(--($B$2:$B$2000=I2)*($C$2:$C$2000=H2)*($E$2:$F$2000))
L2:L42L2=SUMPRODUCT(--($B$2:$B$2000=K2)*($C$2:$C$2000=H2)*($E$2:$F$2000))
N2:N42N2=SUMPRODUCT(--($B$2:$B$2000=M2)*($C$2:$C$2000=H2)*($E$2:$F$2000))
P2:P42P2=SUMPRODUCT(--($B$2:$B$2000=O2)*($C$2:$C$2000=H2)*($E$2:$F$2000))
R2:R42R2=SUMPRODUCT(--($B$2:$B$2000=Q2)*($C$2:$C$2000=H2)*($E$2:$F$2000))
T2T2=SUMPRODUCT(--($B$2:$B$2000=S2)*($C$2:$C$2000=H2)*($E$2:$F$2000))
J3:J42J3=SUMPRODUCT(--($B$2:$B$17=I3)*($C$2:$C$17=H3)*($E$2:$F$17))
I13:I42,K3:K42,I4:I11,S3:S42,Q3:Q42,O3:O42,M3:M42K3=IFERROR(VLOOKUP($H3&"/"&K$1,CHOOSE({1,2},$C$2:$C$17&"/"&$D$2:$D$17,$B$2:$B$17),2,0),"")
T3:T42T3=SUMPRODUCT(--($B$2:$B$17=S3)*($C$2:$C$17=H3)*($E$2:$F$17))
I12I12=IFERROR(VLOOKUP($H12&"/"&I$1,CHOOSE({1,2},$C$2:$C$127&"/"&$D$2:$D$127,$B$2:$B$127),2,0),"")
D2:D42D2=IF(C1=C2,D1+1,1)
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,449
Office Version
  1. 2016
Platform
  1. Windows
Hi Rajesh1960,

There appears to be inconsistencies in which formulae are entered with Ctr-Shift-Enter in column I and which rows are used in column J.

From your posted data edit I2 and use Ctr-Shift-Enter. Select I2 and J2 then copy down.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,754
Messages
5,638,170
Members
417,011
Latest member
Amaden95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top