Find dupes in column X, sum corresponding values in Y, display answer in Z formula??

T

Ticcer

New Member
Joined
Aug 2, 2011
Messages
37
Excel Workbook
ABCDEFGHIJKLM
5QTRDateDayTimeLocationSKUItem Price Inc. GSTItem Price RRPSale $ Inc GSTR-TOTPmt MethodDay Trans #Trans ID
6110/07/2010Saturday06:55:00RocklandFSB000400N-OC3$ 10.00$ 15.00$ 10.00Cash001000001-20100710-ROC001
7110/07/2010Saturday08:20:00RocklandFSB000400N-OC3$ 15.00$ 15.00$ 25.00Cash002000002-20100710-ROC002
8110/07/2010Saturday08:20:00RocklandFSB000400N-OC3$ 15.00$ 15.00$ 40.00Cash002000002-20100710-ROC002
9110/07/2010Saturday10:25:00RocklandFSB003000N-M2$ 40.00$ 40.00$ 80.00Cash003000003-20100710-ROC003
10110/07/2010Saturday11:08:00RocklandFSB000400N-OC3$ 15.00$ 15.00$ 95.00Cash004000004-20100710-ROC004
11110/07/2010Saturday11:17:00RocklandFSB000400N-OC3$ 10.00$ 10.00$ 105.00Cash005000005-20100710-ROC005
12110/07/2010Saturday11:40:00RocklandFSB001600N-S2$ 20.00$ 20.00$ 125.00Cash006000006-20100710-ROC006
13110/07/2010Saturday11:50:00RocklandFSB001600N-S2$ 20.00$ 20.00$ 145.00Cash007000007-20100710-ROC007
14111/07/2010Sunday07:50:00ChandlinghamFSB000400N-OC3$ 10.00$ 10.00$ 155.00Cash001000008-20100711-CHA001
15111/07/2010Sunday08:30:00ChandlinghamFSB003000T-S1$ 35.00$ 35.00$ 190.00Cash002000009-20100711-CHA002
16111/07/2010Sunday09:45:00ChandlinghamFSB003000N-M2$ 35.00$ 35.00$ 225.00Cash003000010-20100711-CHA003
17111/07/2010Sunday11:22:00ChandlinghamFSB008000N-XL2$ 65.00$ 65.00$ 290.00Cash004000011-20100711-CHA004
18111/07/2010Sunday11:22:00ChandlinghamFSB000400N-OC3$ -$ 15.00$ 290.00Complimentary004000011-20100711-CHA004
19111/07/2010Sunday11:40:00ChandlinghamFSB001600N-S2$ 15.00$ 15.00$ 305.00Cash005000012-20100711-CHA005
20111/07/2010Sunday11:45:00ChandlinghamFSB003000N-S2$ 25.00$ 25.00$ 330.00Cash006000013-20100711-CHA006
21111/07/2010Sunday11:45:00ChandlinghamFSB001600N-S2$ 20.00$ 20.00$ 350.00Cash006000013-20100711-CHA006
22117/07/2010Saturday07:40:00RocklandFSB000400N-OC3$ 15.00$ 20.00$ 365.00Cash001000014-20100717-ROC001
23117/07/2010Saturday07:40:00RocklandFSB001600N-S2$ 21.00$ 21.00$ 386.00Cash001000014-20100717-ROC001
24117/07/2010Saturday08:00:00RocklandFSB003000T-M1$ 45.00$ 45.00$ 431.00Cash002000015-20100717-ROC002
25117/07/2010Saturday08:00:00RocklandFSB000400N-OC3$ -$ 10.00$ 431.00Complimentary002000015-20100717-ROC002
26117/07/2010Saturday11:30:00RocklandFSB008000T-XL1$ 77.00$ 77.00$ 508.00Cash003000016-20100717-ROC003
27117/07/2010Saturday11:55:00RocklandFSB000400N-OC3$ 15.00$ 15.00$ 523.00Cash004000017-20100717-ROC004
28117/07/2010Saturday11:55:00RocklandFSB000400N-OC3$ 17.50$ 17.50$ 540.50Cash004000017-20100717-ROC004
29117/07/2010Saturday12:05:00RocklandFSB003000N-L2$ 43.50$ 48.50$ 584.00Cash005000018-20100717-ROC005
30117/07/2010Saturday12:15:00RocklandFSB000400N-OC3$ 15.00$ 15.00$ 599.00Cash006000019-20100717-ROC006
In the sheet below, each row represents an item sold. Column L is the days transaction number, column M is the overall period transaction ID code. If one customer buys 2 items, that one transaction is represented in 2 rows, similarly, if one customer buys 10 items, that transaction takes 10 rows. The transaction ID in column M will be duplicated for all those rows. I'm trying to find a formula that scans column M looking for duplicates in consecutive cells. If dupes are found, it discovers how many, and in which rows. It then sums the values in column G for those rows and delivers the answer to column I in the last effected row. All other cells in column I relating to that transaction ID are left blank. If a transaction ID is only for a single item sale (i.e. 1 row), the formula simply populates the cell in column I for that row with the value in column G. The result is a running total of each transaction as opposed to the running total for each item in column J Been busting brain cells on this for 3 days.... anyone got any ideas? Cheers. Ticcer De sheet.... TOTAL SALES 2010-2011 (2)
Excel 2010
Cell Formulas
RangeFormula
L6=IF(OR(($L5="Day Trans #"),AND($B5<>$B6,$E5<>$E6),AND($B5=$B6,$E5<>$E6),AND($B5<>$B6,$E5=$E6)),TEXT(1,"000"),IF((AND(($B5=$B6),($D5=$D6))),TEXT($L5,"000"),(TEXT(SUM($L5+1),"000"))))
L7=IF(OR(($L6="Day Trans #"),AND($B6<>$B7,$E6<>$E7),AND($B6=$B7,$E6<>$E7),AND($B6<>$B7,$E6=$E7)),TEXT(1,"000"),IF((AND(($B6=$B7),($D6=$D7))),TEXT($L6,"000"),(TEXT(SUM($L6+1),"000"))))
L8=IF(OR(($L7="Day Trans #"),AND($B7<>$B8,$E7<>$E8),AND($B7=$B8,$E7<>$E8),AND($B7<>$B8,$E7=$E8)),TEXT(1,"000"),IF((AND(($B7=$B8),($D7=$D8))),TEXT($L7,"000"),(TEXT(SUM($L7+1),"000"))))
L9=IF(OR(($L8="Day Trans #"),AND($B8<>$B9,$E8<>$E9),AND($B8=$B9,$E8<>$E9),AND($B8<>$B9,$E8=$E9)),TEXT(1,"000"),IF((AND(($B8=$B9),($D8=$D9))),TEXT($L8,"000"),(TEXT(SUM($L8+1),"000"))))
L10=IF(OR(($L9="Day Trans #"),AND($B9<>$B10,$E9<>$E10),AND($B9=$B10,$E9<>$E10),AND($B9<>$B10,$E9=$E10)),TEXT(1,"000"),IF((AND(($B9=$B10),($D9=$D10))),TEXT($L9,"000"),(TEXT(SUM($L9+1),"000"))))
L11=IF(OR(($L10="Day Trans #"),AND($B10<>$B11,$E10<>$E11),AND($B10=$B11,$E10<>$E11),AND($B10<>$B11,$E10=$E11)),TEXT(1,"000"),IF((AND(($B10=$B11),($D10=$D11))),TEXT($L10,"000"),(TEXT(SUM($L10+1),"000"))))
L12=IF(OR(($L11="Day Trans #"),AND($B11<>$B12,$E11<>$E12),AND($B11=$B12,$E11<>$E12),AND($B11<>$B12,$E11=$E12)),TEXT(1,"000"),IF((AND(($B11=$B12),($D11=$D12))),TEXT($L11,"000"),(TEXT(SUM($L11+1),"000"))))
L13=IF(OR(($L12="Day Trans #"),AND($B12<>$B13,$E12<>$E13),AND($B12=$B13,$E12<>$E13),AND($B12<>$B13,$E12=$E13)),TEXT(1,"000"),IF((AND(($B12=$B13),($D12=$D13))),TEXT($L12,"000"),(TEXT(SUM($L12+1),"000"))))
L14=IF(OR(($L13="Day Trans #"),AND($B13<>$B14,$E13<>$E14),AND($B13=$B14,$E13<>$E14),AND($B13<>$B14,$E13=$E14)),TEXT(1,"000"),IF((AND(($B13=$B14),($D13=$D14))),TEXT($L13,"000"),(TEXT(SUM($L13+1),"000"))))
L15=IF(OR(($L14="Day Trans #"),AND($B14<>$B15,$E14<>$E15),AND($B14=$B15,$E14<>$E15),AND($B14<>$B15,$E14=$E15)),TEXT(1,"000"),IF((AND(($B14=$B15),($D14=$D15))),TEXT($L14,"000"),(TEXT(SUM($L14+1),"000"))))
L16=IF(OR(($L15="Day Trans #"),AND($B15<>$B16,$E15<>$E16),AND($B15=$B16,$E15<>$E16),AND($B15<>$B16,$E15=$E16)),TEXT(1,"000"),IF((AND(($B15=$B16),($D15=$D16))),TEXT($L15,"000"),(TEXT(SUM($L15+1),"000"))))
L17=IF(OR(($L16="Day Trans #"),AND($B16<>$B17,$E16<>$E17),AND($B16=$B17,$E16<>$E17),AND($B16<>$B17,$E16=$E17)),TEXT(1,"000"),IF((AND(($B16=$B17),($D16=$D17))),TEXT($L16,"000"),(TEXT(SUM($L16+1),"000"))))
L18=IF(OR(($L17="Day Trans #"),AND($B17<>$B18,$E17<>$E18),AND($B17=$B18,$E17<>$E18),AND($B17<>$B18,$E17=$E18)),TEXT(1,"000"),IF((AND(($B17=$B18),($D17=$D18))),TEXT($L17,"000"),(TEXT(SUM($L17+1),"000"))))
L19=IF(OR(($L18="Day Trans #"),AND($B18<>$B19,$E18<>$E19),AND($B18=$B19,$E18<>$E19),AND($B18<>$B19,$E18=$E19)),TEXT(1,"000"),IF((AND(($B18=$B19),($D18=$D19))),TEXT($L18,"000"),(TEXT(SUM($L18+1),"000"))))
L20=IF(OR(($L19="Day Trans #"),AND($B19<>$B20,$E19<>$E20),AND($B19=$B20,$E19<>$E20),AND($B19<>$B20,$E19=$E20)),TEXT(1,"000"),IF((AND(($B19=$B20),($D19=$D20))),TEXT($L19,"000"),(TEXT(SUM($L19+1),"000"))))
L21=IF(OR(($L20="Day Trans #"),AND($B20<>$B21,$E20<>$E21),AND($B20=$B21,$E20<>$E21),AND($B20<>$B21,$E20=$E21)),TEXT(1,"000"),IF((AND(($B20=$B21),($D20=$D21))),TEXT($L20,"000"),(TEXT(SUM($L20+1),"000"))))
L22=IF(OR(($L21="Day Trans #"),AND($B21<>$B22,$E21<>$E22),AND($B21=$B22,$E21<>$E22),AND($B21<>$B22,$E21=$E22)),TEXT(1,"000"),IF((AND(($B21=$B22),($D21=$D22))),TEXT($L21,"000"),(TEXT(SUM($L21+1),"000"))))
L23=IF(OR(($L22="Day Trans #"),AND($B22<>$B23,$E22<>$E23),AND($B22=$B23,$E22<>$E23),AND($B22<>$B23,$E22=$E23)),TEXT(1,"000"),IF((AND(($B22=$B23),($D22=$D23))),TEXT($L22,"000"),(TEXT(SUM($L22+1),"000"))))
L24=IF(OR(($L23="Day Trans #"),AND($B23<>$B24,$E23<>$E24),AND($B23=$B24,$E23<>$E24),AND($B23<>$B24,$E23=$E24)),TEXT(1,"000"),IF((AND(($B23=$B24),($D23=$D24))),TEXT($L23,"000"),(TEXT(SUM($L23+1),"000"))))
L25=IF(OR(($L24="Day Trans #"),AND($B24<>$B25,$E24<>$E25),AND($B24=$B25,$E24<>$E25),AND($B24<>$B25,$E24=$E25)),TEXT(1,"000"),IF((AND(($B24=$B25),($D24=$D25))),TEXT($L24,"000"),(TEXT(SUM($L24+1),"000"))))
L26=IF(OR(($L25="Day Trans #"),AND($B25<>$B26,$E25<>$E26),AND($B25=$B26,$E25<>$E26),AND($B25<>$B26,$E25=$E26)),TEXT(1,"000"),IF((AND(($B25=$B26),($D25=$D26))),TEXT($L25,"000"),(TEXT(SUM($L25+1),"000"))))
L27=IF(OR(($L26="Day Trans #"),AND($B26<>$B27,$E26<>$E27),AND($B26=$B27,$E26<>$E27),AND($B26<>$B27,$E26=$E27)),TEXT(1,"000"),IF((AND(($B26=$B27),($D26=$D27))),TEXT($L26,"000"),(TEXT(SUM($L26+1),"000"))))
L28=IF(OR(($L27="Day Trans #"),AND($B27<>$B28,$E27<>$E28),AND($B27=$B28,$E27<>$E28),AND($B27<>$B28,$E27=$E28)),TEXT(1,"000"),IF((AND(($B27=$B28),($D27=$D28))),TEXT($L27,"000"),(TEXT(SUM($L27+1),"000"))))
L29=IF(OR(($L28="Day Trans #"),AND($B28<>$B29,$E28<>$E29),AND($B28=$B29,$E28<>$E29),AND($B28<>$B29,$E28=$E29)),TEXT(1,"000"),IF((AND(($B28=$B29),($D28=$D29))),TEXT($L28,"000"),(TEXT(SUM($L28+1),"000"))))
L30=IF(OR(($L29="Day Trans #"),AND($B29<>$B30,$E29<>$E30),AND($B29=$B30,$E29<>$E30),AND($B29<>$B30,$E29=$E30)),TEXT(1,"000"),IF((AND(($B29=$B30),($D29=$D30))),TEXT($L29,"000"),(TEXT(SUM($L29+1),"000"))))
M6=CONCATENATE((IF($M5="Trans ID",TEXT(1,"000000"),IF((AND(($B5=$B6),($D5=$D6),($E5=$E6))),TEXT(LEFT($M5,6),"000000"),TEXT(LEFT($M5,6)+1,"000000")))),"-",TEXT($B6,"yyyymmdd"),"-",VLOOKUP($E6,LocationID,2,0),TEXT($L6,"000"))
M7=CONCATENATE((IF($M6="Trans ID",TEXT(1,"000000"),IF((AND(($B6=$B7),($D6=$D7),($E6=$E7))),TEXT(LEFT($M6,6),"000000"),TEXT(LEFT($M6,6)+1,"000000")))),"-",TEXT($B7,"yyyymmdd"),"-",VLOOKUP($E7,LocationID,2,0),TEXT($L7,"000"))
M8=CONCATENATE((IF($M7="Trans ID",TEXT(1,"000000"),IF((AND(($B7=$B8),($D7=$D8),($E7=$E8))),TEXT(LEFT($M7,6),"000000"),TEXT(LEFT($M7,6)+1,"000000")))),"-",TEXT($B8,"yyyymmdd"),"-",VLOOKUP($E8,LocationID,2,0),TEXT($L8,"000"))
M9=CONCATENATE((IF($M8="Trans ID",TEXT(1,"000000"),IF((AND(($B8=$B9),($D8=$D9),($E8=$E9))),TEXT(LEFT($M8,6),"000000"),TEXT(LEFT($M8,6)+1,"000000")))),"-",TEXT($B9,"yyyymmdd"),"-",VLOOKUP($E9,LocationID,2,0),TEXT($L9,"000"))
M10=CONCATENATE((IF($M9="Trans ID",TEXT(1,"000000"),IF((AND(($B9=$B10),($D9=$D10),($E9=$E10))),TEXT(LEFT($M9,6),"000000"),TEXT(LEFT($M9,6)+1,"000000")))),"-",TEXT($B10,"yyyymmdd"),"-",VLOOKUP($E10,LocationID,2,0),TEXT($L10,"000"))
M11=CONCATENATE((IF($M10="Trans ID",TEXT(1,"000000"),IF((AND(($B10=$B11),($D10=$D11),($E10=$E11))),TEXT(LEFT($M10,6),"000000"),TEXT(LEFT($M10,6)+1,"000000")))),"-",TEXT($B11,"yyyymmdd"),"-",VLOOKUP($E11,LocationID,2,0),TEXT($L11,"000"))
M12=CONCATENATE((IF($M11="Trans ID",TEXT(1,"000000"),IF((AND(($B11=$B12),($D11=$D12),($E11=$E12))),TEXT(LEFT($M11,6),"000000"),TEXT(LEFT($M11,6)+1,"000000")))),"-",TEXT($B12,"yyyymmdd"),"-",VLOOKUP($E12,LocationID,2,0),TEXT($L12,"000"))
M13=CONCATENATE((IF($M12="Trans ID",TEXT(1,"000000"),IF((AND(($B12=$B13),($D12=$D13),($E12=$E13))),TEXT(LEFT($M12,6),"000000"),TEXT(LEFT($M12,6)+1,"000000")))),"-",TEXT($B13,"yyyymmdd"),"-",VLOOKUP($E13,LocationID,2,0),TEXT($L13,"000"))
M14=CONCATENATE((IF($M13="Trans ID",TEXT(1,"000000"),IF((AND(($B13=$B14),($D13=$D14),($E13=$E14))),TEXT(LEFT($M13,6),"000000"),TEXT(LEFT($M13,6)+1,"000000")))),"-",TEXT($B14,"yyyymmdd"),"-",VLOOKUP($E14,LocationID,2,0),TEXT($L14,"000"))
M15=CONCATENATE((IF($M14="Trans ID",TEXT(1,"000000"),IF((AND(($B14=$B15),($D14=$D15),($E14=$E15))),TEXT(LEFT($M14,6),"000000"),TEXT(LEFT($M14,6)+1,"000000")))),"-",TEXT($B15,"yyyymmdd"),"-",VLOOKUP($E15,LocationID,2,0),TEXT($L15,"000"))
M16=CONCATENATE((IF($M15="Trans ID",TEXT(1,"000000"),IF((AND(($B15=$B16),($D15=$D16),($E15=$E16))),TEXT(LEFT($M15,6),"000000"),TEXT(LEFT($M15,6)+1,"000000")))),"-",TEXT($B16,"yyyymmdd"),"-",VLOOKUP($E16,LocationID,2,0),TEXT($L16,"000"))
M17=CONCATENATE((IF($M16="Trans ID",TEXT(1,"000000"),IF((AND(($B16=$B17),($D16=$D17),($E16=$E17))),TEXT(LEFT($M16,6),"000000"),TEXT(LEFT($M16,6)+1,"000000")))),"-",TEXT($B17,"yyyymmdd"),"-",VLOOKUP($E17,LocationID,2,0),TEXT($L17,"000"))
M18=CONCATENATE((IF($M17="Trans ID",TEXT(1,"000000"),IF((AND(($B17=$B18),($D17=$D18),($E17=$E18))),TEXT(LEFT($M17,6),"000000"),TEXT(LEFT($M17,6)+1,"000000")))),"-",TEXT($B18,"yyyymmdd"),"-",VLOOKUP($E18,LocationID,2,0),TEXT($L18,"000"))
M19=CONCATENATE((IF($M18="Trans ID",TEXT(1,"000000"),IF((AND(($B18=$B19),($D18=$D19),($E18=$E19))),TEXT(LEFT($M18,6),"000000"),TEXT(LEFT($M18,6)+1,"000000")))),"-",TEXT($B19,"yyyymmdd"),"-",VLOOKUP($E19,LocationID,2,0),TEXT($L19,"000"))
M20=CONCATENATE((IF($M19="Trans ID",TEXT(1,"000000"),IF((AND(($B19=$B20),($D19=$D20),($E19=$E20))),TEXT(LEFT($M19,6),"000000"),TEXT(LEFT($M19,6)+1,"000000")))),"-",TEXT($B20,"yyyymmdd"),"-",VLOOKUP($E20,LocationID,2,0),TEXT($L20,"000"))
M21=CONCATENATE((IF($M20="Trans ID",TEXT(1,"000000"),IF((AND(($B20=$B21),($D20=$D21),($E20=$E21))),TEXT(LEFT($M20,6),"000000"),TEXT(LEFT($M20,6)+1,"000000")))),"-",TEXT($B21,"yyyymmdd"),"-",VLOOKUP($E21,LocationID,2,0),TEXT($L21,"000"))
M22=CONCATENATE((IF($M21="Trans ID",TEXT(1,"000000"),IF((AND(($B21=$B22),($D21=$D22),($E21=$E22))),TEXT(LEFT($M21,6),"000000"),TEXT(LEFT($M21,6)+1,"000000")))),"-",TEXT($B22,"yyyymmdd"),"-",VLOOKUP($E22,LocationID,2,0),TEXT($L22,"000"))
M23=CONCATENATE((IF($M22="Trans ID",TEXT(1,"000000"),IF((AND(($B22=$B23),($D22=$D23),($E22=$E23))),TEXT(LEFT($M22,6),"000000"),TEXT(LEFT($M22,6)+1,"000000")))),"-",TEXT($B23,"yyyymmdd"),"-",VLOOKUP($E23,LocationID,2,0),TEXT($L23,"000"))
M24=CONCATENATE((IF($M23="Trans ID",TEXT(1,"000000"),IF((AND(($B23=$B24),($D23=$D24),($E23=$E24))),TEXT(LEFT($M23,6),"000000"),TEXT(LEFT($M23,6)+1,"000000")))),"-",TEXT($B24,"yyyymmdd"),"-",VLOOKUP($E24,LocationID,2,0),TEXT($L24,"000"))
M25=CONCATENATE((IF($M24="Trans ID",TEXT(1,"000000"),IF((AND(($B24=$B25),($D24=$D25),($E24=$E25))),TEXT(LEFT($M24,6),"000000"),TEXT(LEFT($M24,6)+1,"000000")))),"-",TEXT($B25,"yyyymmdd"),"-",VLOOKUP($E25,LocationID,2,0),TEXT($L25,"000"))
M26=CONCATENATE((IF($M25="Trans ID",TEXT(1,"000000"),IF((AND(($B25=$B26),($D25=$D26),($E25=$E26))),TEXT(LEFT($M25,6),"000000"),TEXT(LEFT($M25,6)+1,"000000")))),"-",TEXT($B26,"yyyymmdd"),"-",VLOOKUP($E26,LocationID,2,0),TEXT($L26,"000"))
M27=CONCATENATE((IF($M26="Trans ID",TEXT(1,"000000"),IF((AND(($B26=$B27),($D26=$D27),($E26=$E27))),TEXT(LEFT($M26,6),"000000"),TEXT(LEFT($M26,6)+1,"000000")))),"-",TEXT($B27,"yyyymmdd"),"-",VLOOKUP($E27,LocationID,2,0),TEXT($L27,"000"))
M28=CONCATENATE((IF($M27="Trans ID",TEXT(1,"000000"),IF((AND(($B27=$B28),($D27=$D28),($E27=$E28))),TEXT(LEFT($M27,6),"000000"),TEXT(LEFT($M27,6)+1,"000000")))),"-",TEXT($B28,"yyyymmdd"),"-",VLOOKUP($E28,LocationID,2,0),TEXT($L28,"000"))
M29=CONCATENATE((IF($M28="Trans ID",TEXT(1,"000000"),IF((AND(($B28=$B29),($D28=$D29),($E28=$E29))),TEXT(LEFT($M28,6),"000000"),TEXT(LEFT($M28,6)+1,"000000")))),"-",TEXT($B29,"yyyymmdd"),"-",VLOOKUP($E29,LocationID,2,0),TEXT($L29,"000"))
M30=CONCATENATE((IF($M29="Trans ID",TEXT(1,"000000"),IF((AND(($B29=$B30),($D29=$D30),($E29=$E30))),TEXT(LEFT($M29,6),"000000"),TEXT(LEFT($M29,6)+1,"000000")))),"-",TEXT($B30,"yyyymmdd"),"-",VLOOKUP($E30,LocationID,2,0),TEXT($L30,"000"))
A6=CHOOSE(MONTH(B6),3,3,3,4,4,4,1,1,1,2,2,2)
A7=CHOOSE(MONTH(B7),3,3,3,4,4,4,1,1,1,2,2,2)
A8=CHOOSE(MONTH(B8),3,3,3,4,4,4,1,1,1,2,2,2)
A9=CHOOSE(MONTH(B9),3,3,3,4,4,4,1,1,1,2,2,2)
A10=CHOOSE(MONTH(B10),3,3,3,4,4,4,1,1,1,2,2,2)
A11=CHOOSE(MONTH(B11),3,3,3,4,4,4,1,1,1,2,2,2)
A12=CHOOSE(MONTH(B12),3,3,3,4,4,4,1,1,1,2,2,2)
A13=CHOOSE(MONTH(B13),3,3,3,4,4,4,1,1,1,2,2,2)
A14=CHOOSE(MONTH(B14),3,3,3,4,4,4,1,1,1,2,2,2)
A15=CHOOSE(MONTH(B15),3,3,3,4,4,4,1,1,1,2,2,2)
A16=CHOOSE(MONTH(B16),3,3,3,4,4,4,1,1,1,2,2,2)
A17=CHOOSE(MONTH(B17),3,3,3,4,4,4,1,1,1,2,2,2)
A18=CHOOSE(MONTH(B18),3,3,3,4,4,4,1,1,1,2,2,2)
A19=CHOOSE(MONTH(B19),3,3,3,4,4,4,1,1,1,2,2,2)
A20=CHOOSE(MONTH(B20),3,3,3,4,4,4,1,1,1,2,2,2)
A21=CHOOSE(MONTH(B21),3,3,3,4,4,4,1,1,1,2,2,2)
A22=CHOOSE(MONTH(B22),3,3,3,4,4,4,1,1,1,2,2,2)
A23=CHOOSE(MONTH(B23),3,3,3,4,4,4,1,1,1,2,2,2)
A24=CHOOSE(MONTH(B24),3,3,3,4,4,4,1,1,1,2,2,2)
A25=CHOOSE(MONTH(B25),3,3,3,4,4,4,1,1,1,2,2,2)
A26=CHOOSE(MONTH(B26),3,3,3,4,4,4,1,1,1,2,2,2)
A27=CHOOSE(MONTH(B27),3,3,3,4,4,4,1,1,1,2,2,2)
A28=CHOOSE(MONTH(B28),3,3,3,4,4,4,1,1,1,2,2,2)
A29=CHOOSE(MONTH(B29),3,3,3,4,4,4,1,1,1,2,2,2)
A30=CHOOSE(MONTH(B30),3,3,3,4,4,4,1,1,1,2,2,2)
C6=WEEKDAY(B6)
C7=WEEKDAY(B7)
C8=WEEKDAY(B8)
C9=WEEKDAY(B9)
C10=WEEKDAY(B10)
C11=WEEKDAY(B11)
C12=WEEKDAY(B12)
C13=WEEKDAY(B13)
C14=WEEKDAY(B14)
C15=WEEKDAY(B15)
C16=WEEKDAY(B16)
C17=WEEKDAY(B17)
C18=WEEKDAY(B18)
C19=WEEKDAY(B19)
C20=WEEKDAY(B20)
C21=WEEKDAY(B21)
C22=WEEKDAY(B22)
C23=WEEKDAY(B23)
C24=WEEKDAY(B24)
C25=WEEKDAY(B25)
C26=WEEKDAY(B26)
C27=WEEKDAY(B27)
C28=WEEKDAY(B28)
C29=WEEKDAY(B29)
C30=WEEKDAY(B30)
J6=IF($J5="R-TOT",$G6,($J5+$G6))
J7=IF($J6="R-TOT",$G7,($J6+$G7))
J8=IF($J7="R-TOT",$G8,($J7+$G8))
J9=IF($J8="R-TOT",$G9,($J8+$G9))
J10=IF($J9="R-TOT",$G10,($J9+$G10))
J11=IF($J10="R-TOT",$G11,($J10+$G11))
J12=IF($J11="R-TOT",$G12,($J11+$G12))
J13=IF($J12="R-TOT",$G13,($J12+$G13))
J14=IF($J13="R-TOT",$G14,($J13+$G14))
J15=IF($J14="R-TOT",$G15,($J14+$G15))
J16=IF($J15="R-TOT",$G16,($J15+$G16))
J17=IF($J16="R-TOT",$G17,($J16+$G17))
J18=IF($J17="R-TOT",$G18,($J17+$G18))
J19=IF($J18="R-TOT",$G19,($J18+$G19))
J20=IF($J19="R-TOT",$G20,($J19+$G20))
J21=IF($J20="R-TOT",$G21,($J20+$G21))
J22=IF($J21="R-TOT",$G22,($J21+$G22))
J23=IF($J22="R-TOT",$G23,($J22+$G23))
J24=IF($J23="R-TOT",$G24,($J23+$G24))
J25=IF($J24="R-TOT",$G25,($J24+$G25))
J26=IF($J25="R-TOT",$G26,($J25+$G26))
J27=IF($J26="R-TOT",$G27,($J26+$G27))
J28=IF($J27="R-TOT",$G28,($J27+$G28))
J29=IF($J28="R-TOT",$G29,($J28+$G29))
J30=IF($J29="R-TOT",$G30,($J29+$G30))
Excel Workbook
NameRefers To
'TOTAL SALES 2010-2011 (2)'!LocationID=Data_Location[#All]
Worksheet Defined Names
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
Do this do what you want:
Excel Workbook
ABCDEFGHIJKLM
5QTRDateDayTimeLocationSKUItem Price Inc. GSTItem Price RRPSale $ Inc GSTR-TOTPmt MethodDay Trans #Trans ID
6110/07/2010Saturday06:55:00RocklandFSB000400N-OC3$10.00$15.00$10.00$10.00Cash1000001-20100710-ROC001
7110/07/2010Saturday08:20:00RocklandFSB000400N-OC3$15.00$15.00 $25.00Cash2000002-20100710-ROC002
8110/07/2010Saturday08:20:00RocklandFSB000400N-OC3$15.00$15.00$30.00$40.00Cash2000002-20100710-ROC002
Sheet


?
 
Upvote 0
Hi GlennUK,

That works! Thank you.

I've been trying to find a formula that used the named column arrays in the table, but discovered today that COUNTIF doesn't support arrays, so I'd been stumped.

I was playing round and adding to this formula...
Code: 
=SUM(IF(FREQUENCY(IF(Trans_ID<>"",MATCH("~"&Trans_ID,Trans_ID&"",0)),ROW(Trans_ID)-ROW($O6)+1),1))
... I found in an Excel Magic Trick youtube video, but was going nowhere fast. (To make the sheet fit in my post I cut out unnecessary columns, so the cell refs in the formula are not the same as the example sheet in this thread)

I'd like to believe there is a way to use arrays, but your solution gets me out of a hole, so cheers muchly for that.

Ticcer
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Filtering Data with formulas
2
Replies
11
Views
1K
markmzz
M

Forum statistics

Threads
1,224,602
Messages
6,179,845
Members
452,948
Latest member
UsmanAli786

Share this page

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
Back
Top