SUMIF Array Formula.... [SOLVED]

  • I have three columns containing raw data (RESOURCE codes from 0000 to 9000; ACCOUNT codes from 1000 to 9799; and DOLLAR amount), with 600 rows of data. I named the first column RESOURCE, the second OBJECT, and the third DATA). I'm trying to create two columns:

    The first column contains the sums of resource codes between from 0000 to 2999 that match a particular ACCOUNT Codes (i.e. 1000, 8011, 9799). The second column contains the sums of resource codes from 3000 to 9999, with corresponding objects. I tried using the below array formulas:

    1. FIRST COLUMN: {=sum(if((RESOURCE<=2999)*(OBJECT=D2),DATA))}
    2. SECOND COLUMN: {=sum(if((RESOURCE>=3000)*(OBJECT=D2),DATA))}

    ....where the first condition is to search range RESOURCE for resource codes less than or equals to 2999, and then scan through range OBJECT and match with the object code in cell D2 and total.....but the thing crashed.

    ANY SUGGESTIONS? I'm attaching the test file below.....the finished product should look like the second worksheet.... and I can be contacted at [email protected]

  • there's nothing in d2 on any of the sheets - what condition are you trying to apply?

    As an aside, for numeric data, a simple, generalisable metrhod for conditional computation is as follows:

    Conditional counting:


    Conditional summing:



  • Thanxxx Paddy....

    Using your suggestion as a springboard, this formula worked:


Participate now!

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