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

1. 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. 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. 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
•