Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I have a spreadsheet for my budget. Payments are either drawn from my bank or my Amex card and then my Amex card is drawn from my bank as well. So I add up all my monthly total like this:

=sumif(I3:I20,"<>AMEX",D3:D20)

Where I3:I20 = account bill is paid from and D3:D20 is monthly amount due. So I am not including bills that come from my Amex card in the total since the Amex bill itself covers those.

Next I have a column that has the day of the month 1-10 (when everything gets paid) and it does this:

=sumif(H3:H20,E24:E33,D3:D20)

d|amount due
-------------------
1|$XX.XX
2|$XX.XX
3|$XX.XX
4|$XX.XX
5|$XX.XX
6|$XX.XX
…

Where H3:H20 = date bill is paid and E24:E33 = range from 1-10. What I want to do is make this second part do the same check as the first. Something like this:

=sumif(H3:H20,E24:E33,IF(I3:I20"<>AMEX",D3:D20,0))

But I get error: "Parse error"

What am I doing wrong?

share|improve this question

migrated from superuser.com Jul 4 '12 at 3:00

This question came from our site for computer enthusiasts and power users.

add comment

1 Answer 1

up vote 1 down vote accepted
+50

Assuming you have "BANK" in I3:I20 (as opposed to "AMEX") for bank payments, then I believe you can achieve your goal by reversing your logic (equal to "BANK", rather than not equal to "AMEX")...

Change the formula in F24 to:

=arrayformula(sumif(H3:H20 & I3:I20,E24:E33 & "BANK",D3:D20))

This will then populate cells F24:F33 with the appropriate values. & is the string concatenation operator, so this effectively compares "1BANK" with "1AMEX" (for instance).

share|improve this answer
    
Works beautifully! I have 2 banks I use (one is for charitable things/tithe) so I just used your solution but added another column for the other bank. –  lazfish Jul 9 '12 at 20:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.