+ Reply to Thread
Results 1 to 4 of 4

sumproduct of multiple conditions, with 1 column with selective condition

  1. #1
    Registered User
    Join Date
    04-11-2012
    Location
    singapore
    MS-Off Ver
    Excel 2003
    Posts
    26

    sumproduct of multiple conditions, with 1 column with selective condition

    Hi all,

    I do not know where i went wrong. Any help is greatly appreciated.

    I have worksheet D, with many rows (close to 30k) and many columns (around 30 columns).
    I need to find rows with conditions (years, selective words in branch, Grade, status, and reviewed)

    so effectively.

    year branch grade status reviewed
    2010 sg A done yes
    2011 hk B not done yes
    2011 msia A done yes
    2010 thai A done yes
    2010 sg A done yes
    2010 thai A done yes



    By right, my search should sum it up and return "4".

    I have this code

    Please Login or Register  to view this content.
    But no luck. The sum is wrong. Any ideas?
    Last edited by melvyndb; 04-25-2012 at 04:56 AM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: sumproduct of multiple conditions, with 1 column with selective condition

    Try it without the quotes around 2010.

    Hope this helps.

    Pete

  3. #3
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: sumproduct of multiple conditions, with 1 column with selective condition

    This work for me

    =SUMPRODUCT((D!L2:L25550=2010)*(ISNUMBER(SEARCH({"sg","thai"},D!Z2:Z25550)))
    *(D!M2:M25550="A")*(D!AD2:AD25550="done")*(D!AF2:AF25550="yes"))

  4. #4
    Registered User
    Join Date
    04-11-2012
    Location
    singapore
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: sumproduct of multiple conditions, with 1 column with selective condition

    Thanks guys... It worked!!

+ 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