+ Reply to Thread
Results 1 to 4 of 4

Multiple conditions - SumProduct, SumIfs, CountIfs - Which do I need?

  1. #1
    Registered User
    Join Date
    02-12-2013
    Location
    Philadelphia, P.A.
    MS-Off Ver
    2007
    Posts
    12

    Multiple conditions - SumProduct, SumIfs, CountIfs - Which do I need?

    New Microsoft Excel Worksheet.xlsx

    Hello all.

    The more time I spend looking at formulae the more confused I get.


    I am having a hard time telling when I need to use SumProduct, Countif(s), sumif(s) etc.

    I was already assisted here with the following formula:
    =SUMPRODUCT(--(rngDeskIss>=G3)*(rngDeskIss<=I3)*(rngDeskIss=rngSch1Sub))

    However, it does not seem like I can just ‘plug and play’ with this the way I intended.
    This correctly matches a named_range of cells within a given date range (G/I). If the date is between G&I, and the two items match, sum.
    This works perfectly for my one question, but I cannot seem to apply this to a >, <, for a different kind/set of data.


    For my new example, I am looking to pull data between a set range (J3/L3), and if they data in column A is between J3:L3, count if column B is above B1.
    Or worded differently:
    Count the number of times the values in column B less than cell B1, which is pulled from a range of dates defined by J3/L3.

    How many items between 4/1 and 4/8 are less than '730'?
    4/4/2013 - 732
    4/5/2013 - 730
    4/8/2013 - 725


    I have tried to modify the following examples I've found, without luck:
    '=SUMPRODUCT(--(DataPull!$O$2:$O$10000=DataPull!$P$2:$P$10000))
    '=SUMPRODUCT((COUNTIF(B2:B1000,A2:A1000)=0)*(A2:A1000<>""))
    '=SUMPRODUCT(--(rngDeskIss>=G3)*(rngDeskIss<=I3)*(rngDeskIss=rngDeskSub))
    '=COUNTIFS($A$2:$A$9,">="&$F$1,$A$2:$A$9,"<="&$F$2,B$2:B$9,"="&$F$4)
    '=counfifs(($A$2:$A$100>=V51)*($A66:X$100<=X5)*($B$2:$B$100))

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Multiple conditions - SumProduct, SumIfs, CountIfs - Which do I need?

    Try with this format:
    =SUMPRODUCT((datecolumn>=J3)*(datecolumn<=L3)*(valuecolumn<=value))
    With J3 and L3 is given date.
    Quang PT

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Multiple conditions - SumProduct, SumIfs, CountIfs - Which do I need?

    with an helpcolumn in I.

    in I1 is the result.

    see the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Registered User
    Join Date
    02-12-2013
    Location
    Philadelphia, P.A.
    MS-Off Ver
    2007
    Posts
    12

    Re: Multiple conditions - SumProduct, SumIfs, CountIfs - Which do I need?

    Thank you both.
    "=SUMPRODUCT((A3:A1000>=V5)*(A3:A1000<=X5)*(B3:B1000>730))" seems to resolve my question, but is so so so close to the others that I've tried, that I cannot 'see' what makes this work. Take away the '--' in my original formulae and it is the 'same' (with the exception of the range names and values.)

    I must have typed something wrong a few times, not seeing the error. This works though. =SUMPRODUCT((A3:A1000>=V5)*(A3:A1000<=X5)*(B3:B1000>730))



    Thanks again.

+ 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