Lookup Multiple columns

  • Hi


    I am trying to create a formula that will look in 3 columns containing 300 rows, and give an answer if the criteria in all 3 columns is met on a single row.
    example: A B C
    1 Blue Bag 60
    2 Red Bag 100
    3 Blue Bag 60


    I want to check ALL the rows in the 3 columns that satisfy the criteria "Blue" and "Bag" and "60" and count how many times this occurs.


    I have tried COUNTIF but with no success, unless I am doing it wrong.


    Any help would be much appreciated.

  • Re: Lookup Multiple columns


    Hi Muppley,

    Welcome to the Ozgrid forum.

    You are probably best to use the Sumproduct function, e.g.

    =SUMPRODUCT(--(A1:A300="Blue"),--(B1:B300="Bag"),--(C1:C300=60))

    You can find many other examples of the Sumproduct function if you do a search of the forum for that text.

    Hope this helps.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: Lookup Multiple columns


    Thanks Batman. Worked a treat.


    I spent hours and hours trying to figure this out.


    Thanks for your help.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!