+ Reply to Thread
Results 1 to 8 of 8

Lookup function with mulitple conditions

  1. #1
    Tiffany
    Guest

    Lookup function with mulitple conditions

    Hi,

    I need some help in the lookup function. Below is the excerpt of my
    database. How can I pick up the right row given 3 conditions to satisfy, ie
    Main Cat, Sub Cat and Brand. e.g. if I select Main Cat = AHS, Sub Cat = MINI,
    Brand = PIONEER, what formula should I put in order to pick up Index = 24.
    Kindly advise.


    Index Rank Main Cat Sub Cat Brand MAT 05 MAT 06
    1 1 AHS ALL ALL 112,515 123,050
    2 2 AHS ALL SONY 14,509 15,074
    3 3 AHS ALL LG 13,056 17,837
    4 4 AHS ALL PHILIPS 13,480 17,491
    5 5 AHS ALL PANA 24,121 17,792
    6 6 AHS ALL PIONEER 13,912 17,456
    7 0 AHS HTS ALL 47,947 58,236
    8 0 AHS HTS SONY 4,821 6,094
    9 0 AHS HTS LG 5,505 9,599
    10 0 AHS HTS PIONEER 12,257 16,157
    11 0 AHS HTS PHILIPS 3,058 4,505
    12 0 AHS HTS PANA 9,016 7,462
    13 0 AHS MICRO ALL 40,372 46,906
    14 0 AHS MICRO LG 3,368 6,399
    15 0 AHS MICRO PHILIPS 4,404 8,345
    16 0 AHS MICRO PANA 14,149 9,818
    17 0 AHS MICRO ENZER 1,306 5,940
    18 0 AHS MICRO SONY 5,971 6,181
    19 0 AHS MINI ALL 15,397 7,539
    20 0 AHS MINI SONY 3,035 1,725
    21 0 AHS MINI ENZER 2,022 1,402
    22 0 AHS MINI PANA 920 467
    23 0 AHS MINI JVC 1,506 1,668
    24 0 AHS MINI PIONEER 1,625 496
    25 0 CAM ALL ALL 93,560 87,068
    26 0 CAM ALL SONY 30,611 32,035
    27 0 CAM ALL PANA 24,689 18,065
    28 0 CAM ALL CANON 16,456 15,465
    29 0 CAM ALL JVC 15,359 16,587
    30 0 CAM ALL SAMSUNG 2,186 2,982
    31 0 CAM DVC ALL 80,020 55,815
    32 0 CAM DVC CANON 16,456 12,803
    33 0 CAM DVC PANA 23,139 15,824
    34 0 CAM DVC SONY 21,821 14,421
    35 0 CAM DVC JVC 13,626 9,802
    36 0 CAM DVC SAMSUNG 2,186 2,468
    37 0 CAM DVD ALL 9,830 20,958
    38 0 CAM DVD SONY 7,270 15,514
    39 0 CAM DVD PANA 1,379 1,960
    40 0 CAM DVD CANON 0 2,662
    41 0 CAM DVD HITACHI 1,181 822
    42 0 CAM HDD ALL 0 4,035
    43 0 CAM HDD JVC 0 3,763
    44 0 CAM HDD SONY 0 272
    45 0 CAM HDV ALL 102 1,400
    46 0 CAM HDV SONY 102 1,400
    47 0 CTV ALL ALL 204,332 256,280
    48 0 CTV ALL SAMSUNG 17,551 31,500
    49 0 CTV ALL SONY 19,924 27,473
    50 0 CTV ALL PHILIPS 19,758 23,281
    51 0 CTV ALL TOSHIBA 23,302 25,031
    52 0 CTV ALL SHARP 26,857 30,535
    53 0 CTV CRT ALL 136,889 123,255
    54 0 CTV CRT JVC 15,259 17,459
    55 0 CTV CRT PANA 22,104 19,415
    56 0 CTV CRT SAMSUNG 7,230 8,608
    57 0 CTV CRT SONY 17,442 14,533
    58 0 CTV CRT PHILIPS 10,187 8,319
    59 0 CTV RPTV ALL 7,608 3,048
    60 0 CTV RPTV SAMSUNG 2,099 1,299
    61 0 CTV RPTV TOSHIBA 3,203 917
    62 0 CTV RPTV HITACHI 418 137
    63 0 CTV RPTV PANA 890 251
    64 0 CTV RPTV SONY 231 58
    65 0 CTV FPD ALL 59,835 129,977
    66 0 CTV FPD SAMSUNG 8,222 21,593
    67 0 CTV FPD SONY 2,251 12,882
    68 0 CTV FPD SHARP 12,674 19,104
    69 0 CTV FPD LG 6,379 11,865
    70 0 CTV FPD PHILIPS 9,442 14,957

    Thank you


  2. #2
    Biff
    Guest

    Re: Lookup function with mulitple conditions

    One way:

    J1 = AHS
    K1 = MINI
    L1 = PIONEER

    =SUMPRODUCT(--(C2:C71=J1),--(D2:D71=K1),--(E2:E71=L1),A2:A71)

    Biff

    "Tiffany" <Tiffany@discussions.microsoft.com> wrote in message
    news:787A2318-7576-4382-89BC-49BBB52322D7@microsoft.com...
    > Hi,
    >
    > I need some help in the lookup function. Below is the excerpt of my
    > database. How can I pick up the right row given 3 conditions to satisfy,
    > ie
    > Main Cat, Sub Cat and Brand. e.g. if I select Main Cat = AHS, Sub Cat =
    > MINI,
    > Brand = PIONEER, what formula should I put in order to pick up Index = 24.
    > Kindly advise.
    >
    >
    > Index Rank Main Cat Sub Cat Brand MAT 05 MAT 06
    > 1 1 AHS ALL ALL 112,515 123,050
    > 2 2 AHS ALL SONY 14,509 15,074
    > 3 3 AHS ALL LG 13,056 17,837
    > 4 4 AHS ALL PHILIPS 13,480 17,491
    > 5 5 AHS ALL PANA 24,121 17,792
    > 6 6 AHS ALL PIONEER 13,912 17,456
    > 7 0 AHS HTS ALL 47,947 58,236
    > 8 0 AHS HTS SONY 4,821 6,094
    > 9 0 AHS HTS LG 5,505 9,599
    > 10 0 AHS HTS PIONEER 12,257 16,157
    > 11 0 AHS HTS PHILIPS 3,058 4,505
    > 12 0 AHS HTS PANA 9,016 7,462
    > 13 0 AHS MICRO ALL 40,372 46,906
    > 14 0 AHS MICRO LG 3,368 6,399
    > 15 0 AHS MICRO PHILIPS 4,404 8,345
    > 16 0 AHS MICRO PANA 14,149 9,818
    > 17 0 AHS MICRO ENZER 1,306 5,940
    > 18 0 AHS MICRO SONY 5,971 6,181
    > 19 0 AHS MINI ALL 15,397 7,539
    > 20 0 AHS MINI SONY 3,035 1,725
    > 21 0 AHS MINI ENZER 2,022 1,402
    > 22 0 AHS MINI PANA 920 467
    > 23 0 AHS MINI JVC 1,506 1,668
    > 24 0 AHS MINI PIONEER 1,625 496
    > 25 0 CAM ALL ALL 93,560 87,068
    > 26 0 CAM ALL SONY 30,611 32,035
    > 27 0 CAM ALL PANA 24,689 18,065
    > 28 0 CAM ALL CANON 16,456 15,465
    > 29 0 CAM ALL JVC 15,359 16,587
    > 30 0 CAM ALL SAMSUNG 2,186 2,982
    > 31 0 CAM DVC ALL 80,020 55,815
    > 32 0 CAM DVC CANON 16,456 12,803
    > 33 0 CAM DVC PANA 23,139 15,824
    > 34 0 CAM DVC SONY 21,821 14,421
    > 35 0 CAM DVC JVC 13,626 9,802
    > 36 0 CAM DVC SAMSUNG 2,186 2,468
    > 37 0 CAM DVD ALL 9,830 20,958
    > 38 0 CAM DVD SONY 7,270 15,514
    > 39 0 CAM DVD PANA 1,379 1,960
    > 40 0 CAM DVD CANON 0 2,662
    > 41 0 CAM DVD HITACHI 1,181 822
    > 42 0 CAM HDD ALL 0 4,035
    > 43 0 CAM HDD JVC 0 3,763
    > 44 0 CAM HDD SONY 0 272
    > 45 0 CAM HDV ALL 102 1,400
    > 46 0 CAM HDV SONY 102 1,400
    > 47 0 CTV ALL ALL 204,332 256,280
    > 48 0 CTV ALL SAMSUNG 17,551 31,500
    > 49 0 CTV ALL SONY 19,924 27,473
    > 50 0 CTV ALL PHILIPS 19,758 23,281
    > 51 0 CTV ALL TOSHIBA 23,302 25,031
    > 52 0 CTV ALL SHARP 26,857 30,535
    > 53 0 CTV CRT ALL 136,889 123,255
    > 54 0 CTV CRT JVC 15,259 17,459
    > 55 0 CTV CRT PANA 22,104 19,415
    > 56 0 CTV CRT SAMSUNG 7,230 8,608
    > 57 0 CTV CRT SONY 17,442 14,533
    > 58 0 CTV CRT PHILIPS 10,187 8,319
    > 59 0 CTV RPTV ALL 7,608 3,048
    > 60 0 CTV RPTV SAMSUNG 2,099 1,299
    > 61 0 CTV RPTV TOSHIBA 3,203 917
    > 62 0 CTV RPTV HITACHI 418 137
    > 63 0 CTV RPTV PANA 890 251
    > 64 0 CTV RPTV SONY 231 58
    > 65 0 CTV FPD ALL 59,835 129,977
    > 66 0 CTV FPD SAMSUNG 8,222 21,593
    > 67 0 CTV FPD SONY 2,251 12,882
    > 68 0 CTV FPD SHARP 12,674 19,104
    > 69 0 CTV FPD LG 6,379 11,865
    > 70 0 CTV FPD PHILIPS 9,442 14,957
    >
    > Thank you
    >




  3. #3
    Max
    Guest

    Re: Lookup function with mulitple conditions

    One way ..

    Assuming the table posted is within A1:G71,
    col A = Index, col C = Main Cat, col D = Sub Cat, col E = Brand

    Assuming I1:K1 will house the inputs for Main Cat, Sub Cat & Brand,

    Put in L1's formula bar, then array-enter the formula by pressing
    CTRL+SHIFT+ENTER (instead of just pressing ENTER):
    =IF(COUNTA(I1:K1)<3,"",INDEX($A$2:$A$71,MATCH(1,($C$2:$C$71=I1)*($D$2:$D$71=J1)*($E$2:$E$71=K1),0)))

    As-is, L1 can be copied down to return correspondingly
    for other sets of inputs in I2:K2, I3, K3 etc
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
    "Tiffany" wrote:
    > I need some help in the lookup function. Below is the excerpt of my
    > database. How can I pick up the right row given 3 conditions to satisfy, ie
    > Main Cat, Sub Cat and Brand. e.g. if I select Main Cat = AHS, Sub Cat = MINI,
    > Brand = PIONEER, what formula should I put in order to pick up Index = 24.
    > Kindly advise.
    >
    > Index Rank Main Cat Sub Cat Brand MAT 05 MAT 06
    > 1 1 AHS ALL ALL 112,515 123,050
    > 2 2 AHS ALL SONY 14,509 15,074
    > 3 3 AHS ALL LG 13,056 17,837

    ....
    > 69 0 CTV FPD LG 6,379 11,865
    > 70 0 CTV FPD PHILIPS 9,442 14,957



  4. #4
    Max
    Guest

    Re: Lookup function with mulitple conditions

    Line
    > for other sets of inputs in I2:K2, I3, K3 etc


    should read:
    > for other sets of inputs in I2:K2, I3:K3 etc

    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---

  5. #5
    Tiffany
    Guest

    Re: Lookup function with mulitple conditions

    Hi Max,

    Thank you very much. Your formula works perfectly for me. Cheers!

    "Max" wrote:

    > One way ..
    >
    > Assuming the table posted is within A1:G71,
    > col A = Index, col C = Main Cat, col D = Sub Cat, col E = Brand
    >
    > Assuming I1:K1 will house the inputs for Main Cat, Sub Cat & Brand,
    >
    > Put in L1's formula bar, then array-enter the formula by pressing
    > CTRL+SHIFT+ENTER (instead of just pressing ENTER):
    > =IF(COUNTA(I1:K1)<3,"",INDEX($A$2:$A$71,MATCH(1,($C$2:$C$71=I1)*($D$2:$D$71=J1)*($E$2:$E$71=K1),0)))
    >
    > As-is, L1 can be copied down to return correspondingly
    > for other sets of inputs in I2:K2, I3, K3 etc
    > --
    > Max
    > Singapore
    > http://savefile.com/projects/236895
    > xdemechanik
    > ---
    > "Tiffany" wrote:
    > > I need some help in the lookup function. Below is the excerpt of my
    > > database. How can I pick up the right row given 3 conditions to satisfy, ie
    > > Main Cat, Sub Cat and Brand. e.g. if I select Main Cat = AHS, Sub Cat = MINI,
    > > Brand = PIONEER, what formula should I put in order to pick up Index = 24.
    > > Kindly advise.
    > >
    > > Index Rank Main Cat Sub Cat Brand MAT 05 MAT 06
    > > 1 1 AHS ALL ALL 112,515 123,050
    > > 2 2 AHS ALL SONY 14,509 15,074
    > > 3 3 AHS ALL LG 13,056 17,837

    > ...
    > > 69 0 CTV FPD LG 6,379 11,865
    > > 70 0 CTV FPD PHILIPS 9,442 14,957

    >


  6. #6
    Max
    Guest

    Re: Lookup function with mulitple conditions

    You're welcome, Tiffany.
    Thanks for calling back ..

    Believe Biff's suggestion also works equally well,
    is shorter* and doesn't require array-entering
    *even with the front error trap discounted

    It's always good to know of the various options available ..
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
    "Tiffany" wrote:
    > Hi Max,
    > Thank you very much. Your formula works perfectly for me. Cheers!


  7. #7
    mankoni@gmail.com
    Guest

    Re: Lookup function with mulitple conditions


    Biff wrote:
    > One way:
    >
    > J1 = AHS
    > K1 = MINI
    > L1 = PIONEER
    >
    > =SUMPRODUCT(--(C2:C71=J1),--(D2:D71=K1),--(E2:E71=L1),A2:A71)
    >
    > Biff
    >
    > "Tiffany" <Tiffany@discussions.microsoft.com> wrote in message
    > news:787A2318-7576-4382-89BC-49BBB52322D7@microsoft.com...
    > > Hi,
    > >
    > > I need some help in the lookup function. Below is the excerpt of my
    > > database. How can I pick up the right row given 3 conditions to satisfy,
    > > ie
    > > Main Cat, Sub Cat and Brand. e.g. if I select Main Cat = AHS, Sub Cat =
    > > MINI,
    > > Brand = PIONEER, what formula should I put in order to pick up Index = 24.
    > > Kindly advise.
    > >
    > >
    > > Index Rank Main Cat Sub Cat Brand MAT 05 MAT 06
    > > 1 1 AHS ALL ALL 112,515 123,050
    > > 2 2 AHS ALL SONY 14,509 15,074
    > > 3 3 AHS ALL LG 13,056 17,837
    > > 4 4 AHS ALL PHILIPS 13,480 17,491
    > > 5 5 AHS ALL PANA 24,121 17,792
    > > 6 6 AHS ALL PIONEER 13,912 17,456
    > > 7 0 AHS HTS ALL 47,947 58,236
    > > 8 0 AHS HTS SONY 4,821 6,094
    > > 9 0 AHS HTS LG 5,505 9,599
    > > 10 0 AHS HTS PIONEER 12,257 16,157
    > > 11 0 AHS HTS PHILIPS 3,058 4,505
    > > 12 0 AHS HTS PANA 9,016 7,462
    > > 13 0 AHS MICRO ALL 40,372 46,906
    > > 14 0 AHS MICRO LG 3,368 6,399
    > > 15 0 AHS MICRO PHILIPS 4,404 8,345
    > > 16 0 AHS MICRO PANA 14,149 9,818
    > > 17 0 AHS MICRO ENZER 1,306 5,940
    > > 18 0 AHS MICRO SONY 5,971 6,181
    > > 19 0 AHS MINI ALL 15,397 7,539
    > > 20 0 AHS MINI SONY 3,035 1,725
    > > 21 0 AHS MINI ENZER 2,022 1,402
    > > 22 0 AHS MINI PANA 920 467
    > > 23 0 AHS MINI JVC 1,506 1,668
    > > 24 0 AHS MINI PIONEER 1,625 496
    > > 25 0 CAM ALL ALL 93,560 87,068
    > > 26 0 CAM ALL SONY 30,611 32,035
    > > 27 0 CAM ALL PANA 24,689 18,065
    > > 28 0 CAM ALL CANON 16,456 15,465
    > > 29 0 CAM ALL JVC 15,359 16,587
    > > 30 0 CAM ALL SAMSUNG 2,186 2,982
    > > 31 0 CAM DVC ALL 80,020 55,815
    > > 32 0 CAM DVC CANON 16,456 12,803
    > > 33 0 CAM DVC PANA 23,139 15,824
    > > 34 0 CAM DVC SONY 21,821 14,421
    > > 35 0 CAM DVC JVC 13,626 9,802
    > > 36 0 CAM DVC SAMSUNG 2,186 2,468
    > > 37 0 CAM DVD ALL 9,830 20,958
    > > 38 0 CAM DVD SONY 7,270 15,514
    > > 39 0 CAM DVD PANA 1,379 1,960
    > > 40 0 CAM DVD CANON 0 2,662
    > > 41 0 CAM DVD HITACHI 1,181 822
    > > 42 0 CAM HDD ALL 0 4,035
    > > 43 0 CAM HDD JVC 0 3,763
    > > 44 0 CAM HDD SONY 0 272
    > > 45 0 CAM HDV ALL 102 1,400
    > > 46 0 CAM HDV SONY 102 1,400
    > > 47 0 CTV ALL ALL 204,332 256,280
    > > 48 0 CTV ALL SAMSUNG 17,551 31,500
    > > 49 0 CTV ALL SONY 19,924 27,473
    > > 50 0 CTV ALL PHILIPS 19,758 23,281
    > > 51 0 CTV ALL TOSHIBA 23,302 25,031
    > > 52 0 CTV ALL SHARP 26,857 30,535
    > > 53 0 CTV CRT ALL 136,889 123,255
    > > 54 0 CTV CRT JVC 15,259 17,459
    > > 55 0 CTV CRT PANA 22,104 19,415
    > > 56 0 CTV CRT SAMSUNG 7,230 8,608
    > > 57 0 CTV CRT SONY 17,442 14,533
    > > 58 0 CTV CRT PHILIPS 10,187 8,319
    > > 59 0 CTV RPTV ALL 7,608 3,048
    > > 60 0 CTV RPTV SAMSUNG 2,099 1,299
    > > 61 0 CTV RPTV TOSHIBA 3,203 917
    > > 62 0 CTV RPTV HITACHI 418 137
    > > 63 0 CTV RPTV PANA 890 251
    > > 64 0 CTV RPTV SONY 231 58
    > > 65 0 CTV FPD ALL 59,835 129,977
    > > 66 0 CTV FPD SAMSUNG 8,222 21,593
    > > 67 0 CTV FPD SONY 2,251 12,882
    > > 68 0 CTV FPD SHARP 12,674 19,104
    > > 69 0 CTV FPD LG 6,379 11,865
    > > 70 0 CTV FPD PHILIPS 9,442 14,957
    > >
    > > Thank you
    > >


    Biff,

    Please explain as to how your soln works. I read the sumproduct
    treats non numeric as zeroes.

    THanks


  8. #8
    Biff
    Guest

    Re: Lookup function with mulitple conditions

    > Please explain as to how your soln works. I read the sumproduct
    > treats non numeric as zeroes.


    See this for a detailed explanation:

    http://xldynamic.com/source/xld.SUMPRODUCT.html

    Biff

    <mankoni@gmail.com> wrote in message
    news:1156086235.429926.63340@m79g2000cwm.googlegroups.com...
    >
    > Biff wrote:
    >> One way:
    >>
    >> J1 = AHS
    >> K1 = MINI
    >> L1 = PIONEER
    >>
    >> =SUMPRODUCT(--(C2:C71=J1),--(D2:D71=K1),--(E2:E71=L1),A2:A71)
    >>
    >> Biff
    >>
    >> "Tiffany" <Tiffany@discussions.microsoft.com> wrote in message
    >> news:787A2318-7576-4382-89BC-49BBB52322D7@microsoft.com...
    >> > Hi,
    >> >
    >> > I need some help in the lookup function. Below is the excerpt of my
    >> > database. How can I pick up the right row given 3 conditions to
    >> > satisfy,
    >> > ie
    >> > Main Cat, Sub Cat and Brand. e.g. if I select Main Cat = AHS, Sub Cat =
    >> > MINI,
    >> > Brand = PIONEER, what formula should I put in order to pick up Index =
    >> > 24.
    >> > Kindly advise.
    >> >
    >> >
    >> > Index Rank Main Cat Sub Cat Brand MAT 05 MAT 06
    >> > 1 1 AHS ALL ALL 112,515 123,050
    >> > 2 2 AHS ALL SONY 14,509 15,074
    >> > 3 3 AHS ALL LG 13,056 17,837
    >> > 4 4 AHS ALL PHILIPS 13,480 17,491
    >> > 5 5 AHS ALL PANA 24,121 17,792
    >> > 6 6 AHS ALL PIONEER 13,912 17,456
    >> > 7 0 AHS HTS ALL 47,947 58,236
    >> > 8 0 AHS HTS SONY 4,821 6,094
    >> > 9 0 AHS HTS LG 5,505 9,599
    >> > 10 0 AHS HTS PIONEER 12,257 16,157
    >> > 11 0 AHS HTS PHILIPS 3,058 4,505
    >> > 12 0 AHS HTS PANA 9,016 7,462
    >> > 13 0 AHS MICRO ALL 40,372 46,906
    >> > 14 0 AHS MICRO LG 3,368 6,399
    >> > 15 0 AHS MICRO PHILIPS 4,404 8,345
    >> > 16 0 AHS MICRO PANA 14,149 9,818
    >> > 17 0 AHS MICRO ENZER 1,306 5,940
    >> > 18 0 AHS MICRO SONY 5,971 6,181
    >> > 19 0 AHS MINI ALL 15,397 7,539
    >> > 20 0 AHS MINI SONY 3,035 1,725
    >> > 21 0 AHS MINI ENZER 2,022 1,402
    >> > 22 0 AHS MINI PANA 920 467
    >> > 23 0 AHS MINI JVC 1,506 1,668
    >> > 24 0 AHS MINI PIONEER 1,625 496
    >> > 25 0 CAM ALL ALL 93,560 87,068
    >> > 26 0 CAM ALL SONY 30,611 32,035
    >> > 27 0 CAM ALL PANA 24,689 18,065
    >> > 28 0 CAM ALL CANON 16,456 15,465
    >> > 29 0 CAM ALL JVC 15,359 16,587
    >> > 30 0 CAM ALL SAMSUNG 2,186 2,982
    >> > 31 0 CAM DVC ALL 80,020 55,815
    >> > 32 0 CAM DVC CANON 16,456 12,803
    >> > 33 0 CAM DVC PANA 23,139 15,824
    >> > 34 0 CAM DVC SONY 21,821 14,421
    >> > 35 0 CAM DVC JVC 13,626 9,802
    >> > 36 0 CAM DVC SAMSUNG 2,186 2,468
    >> > 37 0 CAM DVD ALL 9,830 20,958
    >> > 38 0 CAM DVD SONY 7,270 15,514
    >> > 39 0 CAM DVD PANA 1,379 1,960
    >> > 40 0 CAM DVD CANON 0 2,662
    >> > 41 0 CAM DVD HITACHI 1,181 822
    >> > 42 0 CAM HDD ALL 0 4,035
    >> > 43 0 CAM HDD JVC 0 3,763
    >> > 44 0 CAM HDD SONY 0 272
    >> > 45 0 CAM HDV ALL 102 1,400
    >> > 46 0 CAM HDV SONY 102 1,400
    >> > 47 0 CTV ALL ALL 204,332 256,280
    >> > 48 0 CTV ALL SAMSUNG 17,551 31,500
    >> > 49 0 CTV ALL SONY 19,924 27,473
    >> > 50 0 CTV ALL PHILIPS 19,758 23,281
    >> > 51 0 CTV ALL TOSHIBA 23,302 25,031
    >> > 52 0 CTV ALL SHARP 26,857 30,535
    >> > 53 0 CTV CRT ALL 136,889 123,255
    >> > 54 0 CTV CRT JVC 15,259 17,459
    >> > 55 0 CTV CRT PANA 22,104 19,415
    >> > 56 0 CTV CRT SAMSUNG 7,230 8,608
    >> > 57 0 CTV CRT SONY 17,442 14,533
    >> > 58 0 CTV CRT PHILIPS 10,187 8,319
    >> > 59 0 CTV RPTV ALL 7,608 3,048
    >> > 60 0 CTV RPTV SAMSUNG 2,099 1,299
    >> > 61 0 CTV RPTV TOSHIBA 3,203 917
    >> > 62 0 CTV RPTV HITACHI 418 137
    >> > 63 0 CTV RPTV PANA 890 251
    >> > 64 0 CTV RPTV SONY 231 58
    >> > 65 0 CTV FPD ALL 59,835 129,977
    >> > 66 0 CTV FPD SAMSUNG 8,222 21,593
    >> > 67 0 CTV FPD SONY 2,251 12,882
    >> > 68 0 CTV FPD SHARP 12,674 19,104
    >> > 69 0 CTV FPD LG 6,379 11,865
    >> > 70 0 CTV FPD PHILIPS 9,442 14,957
    >> >
    >> > Thank you
    >> >

    >
    > Biff,
    >
    > Please explain as to how your soln works. I read the sumproduct
    > treats non numeric as zeroes.
    >
    > THanks
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1