Numbers formula help

Discussion in 'iWork Forum' started by Abunaixd, Jan 25, 2012.

  1. Abunaixd
    Offline

    Abunaixd iPF Noob

    Joined:
    Jan 25, 2012
    Messages:
    8
    Thanks Received:
    0
    Trophy Points:
    0
    Ratings:
    +0 / 0
    Hello everyone I'm having an issue with my formula on numbers. I'm trying to create a banking log to track my spending. I want to have a check list control weather or not the total given is added to the category or not. But im unable to link the IF and SUMIF statements. Any help would be greatly appreciated.
    photo.jpg
  2. twerppoet
    Offline

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,651
    Thanks Received:
    2,110
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,479 / 1
    Ok, I think I understand what you want to do. From what I can see it should work, but here are a couple things you can try.

    One is to be more explicit with the condition test in the IF statement. I had problems with checkmarks evaluating as TRUE before, even though the bare-bones method you used should work.

    is_true.jpg

    The other is to make a quick test with the same formula, but select ranges of cells instead of entire columns. I've noticed that some functions are unable to ignore Headers in columns, and fail when the header is included in the selection.

    You also might be able to cobble together something with the IFSUM functions instead, though it would be less elegant.

    If none of that works, I'd be happy to fiddle with the spreadsheet if you can provide a link to a copy (minus any personal data)
  3. Abunaixd
    Offline

    Abunaixd iPF Noob

    Joined:
    Jan 25, 2012
    Messages:
    8
    Thanks Received:
    0
    Trophy Points:
    0
    Ratings:
    +0 / 0
    Thank you for your reply I've tried both ways using the IF("Transactions::B=True", as well as selecting the ranges but it still doesnt read the value. Here is the link to my spreadsheet hopefully you have better luck than I do.

    Blank.zip
    Last edited: Jan 25, 2012
  4. twerppoet
    Offline

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,651
    Thanks Received:
    2,110
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,479 / 1
    Wow. 4Shared really makes you jump through hoops if you aren't a member, but I got the file.

    I'll see what I can figure out; no promises. It is getting late, and with my schedule it may be this time tomorrow before I get back to you.
  5. Abunaixd
    Offline

    Abunaixd iPF Noob

    Joined:
    Jan 25, 2012
    Messages:
    8
    Thanks Received:
    0
    Trophy Points:
    0
    Ratings:
    +0 / 0
    Glad you got it, I understand if it takes awhile so please don't feel rushed. I appreciate any help you can offer. Also I didn't realize it was such a hustle sorry about that.
  6. twerppoet
    Offline

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,651
    Thanks Received:
    2,110
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,479 / 1
    Made the mistake of opening it up, just to take a look. Oh well, I can sleep in tomorrow, more than usual. :D

    Anyway, I got it to work.

    The mistake is using the IF function at all. IF can't test an entire column of values. I realized this when I decided to simplify the formula and test each part individually. No matter what I did I could not make IF test as true with more than one cell selected. (Though in hind site I wonder what would happen if I made all cells in the selection true. That could be a handy trick)

    Fortunately, you don't need to. The IFSUMS function can test for multiple conditions. By testing for both (Category = A8:Savings) and (Paid = True) you get the results you want. The picture should make it clearer.

    SavingsFormula.jpg

    I did not test it beyond the one entry, so no guarantees, but I think it will do exactly what you want.
  7. twerppoet
    Offline

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,651
    Thanks Received:
    2,110
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,479 / 1
    BTW, the Paid On formula is not going to do what you want. The date will always be the current date. I have yet to find an automatic way to set a date in a cell. If you find one that works, please post it.
  8. Abunaixd
    Offline

    Abunaixd iPF Noob

    Joined:
    Jan 25, 2012
    Messages:
    8
    Thanks Received:
    0
    Trophy Points:
    0
    Ratings:
    +0 / 0
    Thank you so much for your help twerppoet you sir are my new hero! As for the Paid On formula, I didn't realize the dates didn't stay static. I did do some research on the problem and the answers I found were to use VB coding or use circular referencing which numbers doesn't seem to support.
  9. twerppoet
    Offline

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,651
    Thanks Received:
    2,110
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,479 / 1
    No problem. It was a fun challenge.
  10. Abunaixd
    Offline

    Abunaixd iPF Noob

    Joined:
    Jan 25, 2012
    Messages:
    8
    Thanks Received:
    0
    Trophy Points:
    0
    Ratings:
    +0 / 0
    Well since I was unable to fix the date to make it static i decided to add a count down to give me the days till due and if the check box is checked it displays Paid.

    Incase anyone is interested I changed the "Paid On" cells to this formula : If(B2, "Paid", If(A2=0, " ", A2 - TODAY)) If the cells in A2 are left blank, C2 displays blank also. If a date is entered into A2, the countdown until that date is displayed and if the "Paid" box is checked, C2 will display Paid in its cell.
  11. twerppoet
    Offline

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,651
    Thanks Received:
    2,110
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,479 / 1
    That's an interesting solutions. Thanks for sharing it.

    Here is a quick tip, if you have not discovered it for yourself:

    To quickly enter the current date in to a cell, try this. Instead of entering each item (month, day, year) in order immediately select day. A Today button will appear on the keypad. Tap it an the other two values will be entered as well.

    Makes date entry (almost) bearable.
  12. Abunaixd
    Offline

    Abunaixd iPF Noob

    Joined:
    Jan 25, 2012
    Messages:
    8
    Thanks Received:
    0
    Trophy Points:
    0
    Ratings:
    +0 / 0
    Wow never knew about that trick, thank you for sharing that twerppoet.
  13. twerppoet
    Offline

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,651
    Thanks Received:
    2,110
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,479 / 1
    No problem. I stumbled onto it when I had to enter a large number of dates to test a spreadsheet.

    You can do the same thing with the time using NOW after selecting the hour. Of course that is less useful, but there.
  14. Mkupslik23
    Offline

    Mkupslik23 iPF Noob

    Joined:
    Mar 6, 2012
    Messages:
    2
    Thanks Received:
    0
    Trophy Points:
    0
    Location:
    Tampa
    Ratings:
    +0 / 0
    Can someone please tell me how to navigate to SUMIF ??
  15. twerppoet
    Offline

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,651
    Thanks Received:
    2,110
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,479 / 1
    You tap the Function key when in formula editor mode. On the menu that pops up choose the Categories tab, then either All or Numeric category. Swipe to scroll down until you see the SUMIF function. Tap it to select, or tap the blue arrow to the right of it for a description and examples.

    Once you've selected and used it should show up in the Recent tab.
  16. Mkupslik23
    Offline

    Mkupslik23 iPF Noob

    Joined:
    Mar 6, 2012
    Messages:
    2
    Thanks Received:
    0
    Trophy Points:
    0
    Location:
    Tampa
    Ratings:
    +0 / 0
    Thank you sooo much : ) Still learning my way around....had a blonde moment!
  17. twerppoet
    Offline

    twerppoet iPad Legend

    Joined:
    Jan 8, 2011
    Messages:
    16,651
    Thanks Received:
    2,110
    Trophy Points:
    113
    Location:
    Walla Walla, WA
    Ratings:
    +2,479 / 1
    Not a problem.

Share This Page

Search tags for this page
apple number - sumifs
,
apple numbers checklist current date auto
,
apple numbers formula count down
,
apple numbers formula for countdown
,
if formula in numbers mac
,
mac numbers help
,
numbers
,
numbers app true formula
,
numbers data entry
,

numbers formula help

,
numbers ipad sumif