+ Reply to Thread
Results 1 to 4 of 4

Suming selected cells based on two criteria

  1. #1
    Gerard
    Guest

    Suming selected cells based on two criteria

    I am wishing to search two specific columns (text) in a data base for two
    separate specific criteria (text)and if these are a match then I want to sum
    the numbers in a third colum that match the two criteria.
    It would be like a dual VLOOKUP with a SUMIF attached based on the basis of
    the results of a TRUE outcome of the VLOOKUP.
    Does anyone know if it is possible to search on two criteria and if a match
    sum all items in a third column, that have a match in regards to the two
    search criteria.

    You will be my idol if you are able to solve this one.


  2. #2
    Max
    Guest

    Re: Suming selected cells based on two criteria

    SUMPRODUCT would be your idol here <g>

    Try something along these lines ..
    In say, F1:
    =SUMPRODUCT((A2:A100="Text1")*(B2:B100="Text2"),C2:C100)
    would sum col C where col A = "Text1" and col B = "Text2"
    Note that the 3 ranges need to be identically sized, and SUMPRODUCT dosen't
    accept entire col refs eg: A:A, B:B, C:C

    And instead of hardcoding the criteria in the formula, perhaps better to
    point to cells housing criteria, eg we could have it in F1 as:
    =SUMPRODUCT(($A$2:$A$100=D1)*($B$2:$B$100=E1),$C$2:$C$100)
    where D1, E1 houses the criteria: Text1, Text2
    With the 3 ranges fixed with the dollar signs, F1 could then be copied down
    to return correspondingly for other sets of criteria in D2:E2, D3:E3, etc
    --
    Max
    Singapore
    http://savefile.com/projects/236895
    xdemechanik
    ---
    "Gerard" wrote:
    > I am wishing to search two specific columns (text) in a data base for two
    > separate specific criteria (text)and if these are a match then I want to sum
    > the numbers in a third colum that match the two criteria.
    > It would be like a dual VLOOKUP with a SUMIF attached based on the basis of
    > the results of a TRUE outcome of the VLOOKUP.
    > Does anyone know if it is possible to search on two criteria and if a match
    > sum all items in a third column, that have a match in regards to the two
    > search criteria.
    >
    > You will be my idol if you are able to solve this one.
    >


  3. #3
    Gerard
    Guest

    Re: Suming selected cells based on two criteria

    Max you are my idol. You are an absolute legend and have provided me with a
    much needed solution. Thankyou very much. YEAH!!!!!!!!

    "Max" wrote:

    > SUMPRODUCT would be your idol here <g>
    >
    > Try something along these lines ..
    > In say, F1:
    > =SUMPRODUCT((A2:A100="Text1")*(B2:B100="Text2"),C2:C100)
    > would sum col C where col A = "Text1" and col B = "Text2"
    > Note that the 3 ranges need to be identically sized, and SUMPRODUCT dosen't
    > accept entire col refs eg: A:A, B:B, C:C
    >
    > And instead of hardcoding the criteria in the formula, perhaps better to
    > point to cells housing criteria, eg we could have it in F1 as:
    > =SUMPRODUCT(($A$2:$A$100=D1)*($B$2:$B$100=E1),$C$2:$C$100)
    > where D1, E1 houses the criteria: Text1, Text2
    > With the 3 ranges fixed with the dollar signs, F1 could then be copied down
    > to return correspondingly for other sets of criteria in D2:E2, D3:E3, etc
    > --
    > Max
    > Singapore
    > http://savefile.com/projects/236895
    > xdemechanik
    > ---
    > "Gerard" wrote:
    > > I am wishing to search two specific columns (text) in a data base for two
    > > separate specific criteria (text)and if these are a match then I want to sum
    > > the numbers in a third colum that match the two criteria.
    > > It would be like a dual VLOOKUP with a SUMIF attached based on the basis of
    > > the results of a TRUE outcome of the VLOOKUP.
    > > Does anyone know if it is possible to search on two criteria and if a match
    > > sum all items in a third column, that have a match in regards to the two
    > > search criteria.
    > >
    > > You will be my idol if you are able to solve this one.
    > >


  4. #4
    Max
    Guest

    Re: Suming selected cells based on two criteria

    You're welcome, Gerard!
    Thanks for the spirited call-back <g>
    --
    Max
    Singapore (.. we're 41 today!)
    http://savefile.com/projects/236895
    xdemechanik
    ---
    "Gerard" wrote:
    > Max you are my idol. You are an absolute legend and have provided me with a
    > much needed solution. Thank you very much. YEAH!!!!!!!!


+ 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