Numbers formula help

[Abunaixd]

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.

 

[twerppoet]

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.



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)

 

[Abunaixd]

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

 

[twerppoet]

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.

 

[Abunaixd]

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.

 

[twerppoet]

Made the mistake of opening it up, just to take a look. Oh well, I can sleep in tomorrow, more than usual.

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.



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

 

[twerppoet]

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.

 

[Abunaixd]

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.

 

[twerppoet]

No problem. It was a fun challenge.

 

[Abunaixd]

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.

 

[twerppoet]

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.

 

[Abunaixd]

Wow never knew about that trick, thank you for sharing that twerppoet.

 

[twerppoet]

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.

 

[Mkupslik23]

Can someone please tell me how to navigate to SUMIF ??

 

[twerppoet]

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.