+ Reply to Thread
Results 1 to 3 of 3

SUMPRODUCT-formula with changing conditions

Hybrid View

BCB SUMPRODUCT-formula with... 02-08-2009, 05:28 PM
daddylonglegs Re: SUMPRODUCT-formula with... 02-08-2009, 05:35 PM
BCB Re: SUMPRODUCT-formula with... 02-09-2009, 04:49 PM
  1. #1
    Registered User
    Join Date
    05-03-2006
    Location
    Mellerud, Sweden
    MS-Off Ver
    2016
    Posts
    70

    SUMPRODUCT-formula with changing conditions

    Hi, all

    I have a few varieties of SumProduct-formulae that are very helpful when dealing with numbers and/or letters in set conditions.
    Like this one, which counts rows where conditions are met in four columns:
    =SUMPRODUCT(--(Z413:Z432="G");--(AB413:AB432<-3);--(AC413:AC432>0,4)*(AD413:AD432>5))
    and this one, which sums the values of the same
    =SUMPRODUCT(--(Z413:Z432="G");--(AB413:AB432<-3);--(AC413:AC432>0,4);--(AD413:AD432>5);(AD413:AD432))

    How does one make these two do their respective jobs when one or some of the columns are conditions for the next column - with their changing values, and thereby changing conditions?

    Instead of "bigger than 0,4" as the condition for the AC part, it should be something like "bigger than it's AD counterpart" (AD value on the same row).

    I have a few combinations of these "internal" measurements to do (the AB in addition being < than a certain percentage of the AC, and so on) but first of all I need a solution to the placement of the > and < signs. Any tip on that part of the problem would be very useful and much appreciated.

    As mentioned, it will sooner or later also have to deal with the condition being > AD - multiplied with a value in a reference cell. I fear it might take more than just tossing the cell reference into the parenthesis?

    Thanks in advance

    BCB
    Last edited by BCB; 02-09-2009 at 04:53 PM.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,677

    Re: SUMPRODUCT-formula with changing conditions

    You can simply change this

    AC413:AC432>0,4

    to this

    AC413:AC432>AD413:AD432

    If there's some scaling involved you can just multiply by the relevant cell, e.g.

    AB413:AB432<AC413:AC432*Z1

  3. #3
    Registered User
    Join Date
    05-03-2006
    Location
    Mellerud, Sweden
    MS-Off Ver
    2016
    Posts
    70

    Re: SUMPRODUCT-formula with changing conditions

    Hi

    That simple?
    Tried it and it seems to work. Haven't controlled it yet, but it behaves like a formula does when it does what it should.
    Thanks a lot!

    BCB

+ 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