Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Oct 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Array Formulas/Conditional Sums (Excel X for Mac)

    I have a spreadsheet that I use for budget and transaction tracking for various accounts. I have developed what I think Excel calls an array formula (the ones in curly brackets that you have to enter using Apple/Ctrl-Shift-Enter) so that the amounts of certain line items are summed into different summary categories using a SUM (IF) construction. I am now wondering if I can have it check against another column of cells whether the transaction has cleared, and cause it to sum the cleared items and the non-cleared items into different cells. I currently have this as an array formula, for example (non-cleared photocopying):

    =SUM(IF(E21:E108="photocopying",D21108))*IF(G21:G108="x",0,1)

    Which I hoped would check all column E (the categories) for the correct categories, add up the relevant column D data (the amounts) and then only list if column G (cleared/not) is not an x. Instead, it adds up the correct amounts but then only pays attention to the first entry in G - if G21 is blank, the sum goes in the non-cleared column, if G21 is an X, the whole sum of all photocopying entries goes to the cleared column.

    Can anyone suggest a way to rephrase this array formula in order to make what I want happen happen? These formulas have been bending my brain for days and I'm simply not seeing where I should go from here, although I feel like I should be able to! All help is cheerfully welcomed.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Array Formulas/Conditional Sums (Excel X for Mac)

    Is this what you are after?
    This will sum the cells in D when the corresponding cell in E contains "Photocopying" and the corresponding cell in G do NOT contain "x"
    Confirm with ctrl-shift-enter:

    <pre>=SUM(IF((E21:E108="photocopying")*(G21:G108<> "x"),D21108))</pre>


    Steve

  3. #3
    New Lounger
    Join Date
    Oct 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array Formulas/Conditional Sums (Excel X for M

    Steve, I just tried this formula (and =x for the cleared column) and it appears to work in the way I was looking for. Thanks much for your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •