SUM(), SUMIF(), and SUMIFS() explained for Apple Numbers!

[@onedaymoreaboard]

Thanks for the easy to follow tutorial! Very clear and concise. Long time Excel user trying to get to grips with Numbers, and glad I found you!

[@apple-a-day]

That's so great to hear! Glad this was helpful. Thanks for watching!

[@carolinekisby1763]

Thank you this was really helpful. It's the first time I've seen a tutorial that doesn't skip steps or talk over the steps too quickly. This was clear, concise and made sense.

[@apple-a-day]

Thanks for the kind words! I’m glad this video was able to help you out. Thanks for watching!

[@adco]

Exactly 👍 👏👏👏👏👏🏆

[@victorguidez9431]

Amazing video, thanks!! 0:00 Sum 4:42 Sumif 8:38 Sumifs 16:10 Countblank

[@apple-a-day]

Hi there! Thanks so much and thanks for watching!

[@rickfernandez7947]

Thank you, thank you! So very helpful - exactly what I was looking for! I use Numbers every day and I must commend you on the clarity and completeness of your explanations. Keep them coming! Exactly when I am thinking, "but what about," you show the next step that answers my question preemptively! This is tremendously helpful and I'll keep following your tips.

[@apple-a-day]

Thanks so much for the kind words. Glad you liked it. I’ll definitely be doing more Numbers videos in the future. Thanks for watching!

[@JustinKendter]

Thank you for this! This is exactly what I was looking for. It automated and gave me way more accurate numbers for my business spreadsheet vs having to manually count everything.

[@apple-a-day]

That’s great to hear!! Glad it was helpful and thanks for watching!

[@hamothemagnif8529]

It’s easy to just show the right way to do something, but when you showed the “development” sumif copy paste and why cell references should be used added so much depth to this guide. Nicely done.

[@apple-a-day]

Thanks so much for the kind words - much appreciated. And thanks for watching!

[@saskskyboy]

This is an excellent tutorial. I will definitely be following. Thank you so very much.

[@apple-a-day]

That's great to hear! Thanks so much!

[@Abbasfathy]

I have a column with a checkboxs (range b1:b5) and another column (range c1:c5) and I want to count the checkboxs if true in the same time the column (range c1:c5) should be empty (with no values). So, the formula will count the checkboxs (b) which the column (c) in empty. Your HELP is great. Thank you!

[@apple-a-day]

HI there! This can be done using COUNTIFS. Th following formula will count a row if these 2 conditions are met: 1) The CHECKBOX under column B is true 2) The CELL under column C is blank =COUNTIFS(B3:B10, "=TRUE", C3:C10, "=") B3:B10 is the range containing checkboxes C3:C10 is the range containing the cells - the condition "=" checks for blank values Hopefully this is what you were asking. Thanks for watching!

[@Abbasfathy]

@@apple-a-day Thank you for your great HELP. Your answer is correct. 😍

[@apple-a-day]

@@Abbasfathy Awesome! Great to hear!

[@georgerutgers4784]

WOW!!! This was sooo helpful!!! Thank You!

[@apple-a-day]

Thanks so much! Glad it was helpful. Thanks for watching!

[@richardhalling4398]

Brilliantly helpful thank you! SUMIFS are a lot easier than I thought

[@apple-a-day]

Thanks so much! Glad it was helpful!

[@amychin6283]

Thank you so MUCH! This was soooo helpful.

[@apple-a-day]

That's great to hear!! Thanks for watching!

[@a1m1a1a]

This video made my day! Thank you so much!

[@apple-a-day]

You are so welcome! Thanks for letting me know and thanks for watching!

[@OscarGarcia-hc9gs]

Great tutorial! I'm still learning how to use numbers. The goal is to create a dashboard that collects and presents the data from multiple worksheets.

[@apple-a-day]

Thanks! Very cool! Good luck!

[@kayjameson5680]

This video is so clear and concise. Thank you so much for making it.

[@apple-a-day]

Thanks so much! I appreciate the kind words. Thanks for watching!

[@carpaticus5225]

Cheers for the tutorial, very helpful! Keep up the good work :)

[@apple-a-day]

Glad it helped! Thanks for watching!

[@shake-n-bake82]

Excellent tutorial!

[@apple-a-day]

Glad you liked it! Thanks for watching!

[@MulligansDen]

Worthy for a like and subscribe …. Excellent explanations and examples … than you!

[@apple-a-day]

Awesome, thank you!

[@onuhs]

Please could you do statistic function on number ( eg chi , t test etc)

[@apple-a-day]

I'll add it to the list! Thanks!

[@albibushi157]

this was very helpful. i use excel a lot on windows and i would like to buy a Macbook. I have read a lot of things about excel not being complete in terms of functions on Macbook and i was curious how NUMBERS works. Lo and behold it looks almost the same if not more handy (i.e the Sum drag into cell was very interesting). I use sum ifs and pivot tables a lot and this was quite interesting. I hope apple do something about fully integrating excel so that users get the full experience. Since i need it for personal use i guess this is more than ok. Could you please tell me if what you are using is a macbook air or pro and if yes what model do you recomend? is Macbook air enough for such usage? i do not see myself editing video or coding in the near future.

[@apple-a-day]

Hello! Thanks, I'm glad this was helpful. I think it's more up to Microsoft to keep improving Excel for the Mac. I wonder if they don't bother because Apple has near equivalents (Numbers, Pages, Keynote instead of Excel, Word, Powerpoint) In any case, if you're using a Mac mostly for non-video work, a MacBook Air will be more than adequate and even the smaller one supports an external display so you can use it as a desktop with a larger monitor. Hope this helps. Thanks for watching!

[@albibushi157]

@@apple-a-day yes, it does. Thanks a lot.

[@jansulc1959]

Hi, thanks for the helpful tutorial. Can I use a cell reference in Numbers? Right in the description of the function it says that it is possible to use a link, but I haven't figured out how to use them.

[@apple-a-day]

Thanks, so glad you enjoyed it. If I understand your question correctly, Numbers has a function called INDIRECT which takes a cell reference as a parameter. So if you had =INDIRECT(B4) as a formula and the cell B4 contained the text "C4", the INDIRECT formula would return the contents of cell C4. Hope that makes sense and I hope that's what you're looking for. If not, please provide details. Thanks for watching!

[@jansulc1959]

@@apple-a-day I found a solution yesterday. In the function you need to enter "

[@LilianaPeters-i8q]

Very well explained. Is there a way to do it if you have multiple columns?

[@apple-a-day]

Hi there! Good question. I hadn't had a need for that but I just did a test where values to SUM were in 2 adjacent columns and the condition to test were in 2 other adjacent columns and it works! The multiple columns must be able to be selected as a single range though. This is the formula that worked: SUMIF(C2:D8, "=X", A2:B8) See how the cell ranges spread over 2 columns. Hope this helps! Thanks for watching

[@Graffnix1]

how to have a sum cell follow last input so that i can have a running total? Thx

[@apple-a-day]

Hi there, thanks for your question. So are you adding a new row and want a cell within that new row to be included in a SUM cell? If that's the case then you need to add those rows within the selected SUM cell's range. Adding a row after the last row of the range won't expand the range. But if you add a row above the last row then the range will grow. The best solution is to always have a blank row at the end of the range and then you can always add a new row above that and it will be included in the SUM cell. If this isn't what you meant, please clarify. Thanks for watching!

[@mauricioflores3732]

Honestly Numbers is underrated, you get a lot of value for free 🎉

[@apple-a-day]

It’s so true. I haven’t had to use Excel in years.

[@JRossHunter]

Very clearly explained. Yes, very helpful. Could you show how to calculate age that will update when the spreadsheet is opened?

[@apple-a-day]

Thanks for the feedback! I'll look into that and get back to you.

[@5e34nut]

Brilliant tutorial clear and concise

[@apple-a-day]

Glad it was helpful!

[@3XLtube]

Thanks for this . I am trying to sumif and my condition is variable. Example, there is one common word on the column A "AJUSTE" . How do I sum-vales on column B with the condition that Column A has the word "AJUSTE" and its variables?

[@apple-a-day]

Hello and thanks for the question. If I understand correctly, your condition is variable, so I'm assuming that condition is the value from another cell. I did some testing and this worked for me: =SUMIF(A1:A5, C1, B1:B5) So in the above statement: A1:A5 are the test values C1 is the cell containing the condition which is variable and can change B1:B5 are the sum values So in your example, if C1 contained the text "AJUSTE" then this would sum all values if A contains "AJUSTE". Hope this works for you. Thanks for watching!

[@cuongttu]

Instead of type in development or urgent. Why can’t I refer to the cell ? Is there away to do that instead of type in the word development or urgent???

[@apple-a-day]

Hi there and thanks for the question. I looked into it and was surprised that I couldn't find a way to do this. It seems like an obvious request. I'll keep digging though. Thanks for watching!

[@ritameireles12]

Great video, thank you so much!

[@apple-a-day]

Glad it was helpful! Thanks for watching!

[@tonischmacketelly]

🙏Thank you very much for this easily understandable tutorial. Now I can finish my tax declaration.🤘

[@apple-a-day]

Glad it helped! Thanks for watching!

[@flavorbby]

Hi, I'm trying to use the SUMIFS formula where one of my test values is for months, I'm having issues getting the formula to calculate dates. If I type 1/1 I have my date format set to show up as January 1, but the "actual" format is set as 1/1/23 so it is not updating my formula for me. Is there something I can do to fix this? Is it possible to have my format stay as 1/1/23 and have a test value that only recognized the month aspect of the date? Sorry if I'm not making sense, also thanks for the video!

[@apple-a-day]

HI there, this is a great question. I tried to do something similar to this a while ago and I couldn't find a way to embed another function within the Condition parameter of SUMIF. In your example, you want to test against the MONTH(). I played around and couldn't make it work. It feels like a very basic request and it's frustrating that it seemingly can't be done. If I ever find a way to do it, I'll let you know. Thanks for watching!

[@flavorbby]

@@apple-a-day yess, very annoying they haven't made it possible. For now I just changed the cell to a text format and it works, out. Hopefully they can make an update someday. Thank you for taking the time to respond, I'll be sure to refer to your videos if I need help in the future! 💕💕💕

[@apple-a-day]

@@flavorbby Glad you found a workaround. Thanks so much and have a great day!

[@cuongdoanmanh4989]

can someone assist me how to use countif in MacBook excel. I cannot do it like normal

[@apple-a-day]

Hi there... sorry for taking so long to reply. Unfortunately I don't even have Excel installed on my Mac so I can't be of much help to you other than I would assume it must work similarly to how it works in Numbers - which is the same way it would work with SUMIF. Good luck!

[@troybrezzy]

Thanks, great video!

[@apple-a-day]

Glad you liked it! Thanks for watching!

[@kevy213]

What if you need to the result to include items that are "Development" and/or "Support" in the same column rather than it being one or the other? Is there a way to add both conditions to the formula?

[@apple-a-day]

This is a great question and Numbers sure doesn't make this easy. There does not seem to be any obvious way to indicate "=Development OR Support". But you can do it using the REGEX function (Regular Expressions). With REGEX there is a way you can check for multiple possibilities. Replace "=Development" with the following: REGEX("Development|Support", false) You can add as many options as you like, each one separated by the | (pipe symbol). Here's what the full statement looks like: SUMIF('Type', REGEX("Development|Support",FALSE), 'Time (Hours)') 'Type' and 'Time (Hours)' are the column names Hopefully this makes sense.

[@kevy213]

@@apple-a-day Amazing! Thank you for the fast response and great explanation. This one thing is a game changer for me.

[@apple-a-day]

@@kevy213 You're welcome! Glad I could help!

[@kevy213]

​@@apple-a-day I'm having a some issues with this formula. The REGEX function works great until I run into a situation where let's say I have "Development" and "Web Development" in the same column. If I use =COUNTIFS('Type',REGEX("Development|Support")) the formula gives me a larger than expected result because it's also including "Web Development" in the result because they both contain the common word Development. If I use =COUNTIFS('Type',REGEX("^(Development|Support)$")) the formula gives me a smaller than expected result because it appears to be excluding "Development" from the result, even though it continues to count "Support" Wrapping my REGEX criteria with "^(Criteria)$" seems to differentiate between similar words like "The" and "These" but not when the same words overlap like in my example above. Are there any work arounds that you know of? Much appreciated!

[@kevy213]

Problem solved.. I didn't realize it, but if you're using REGEX to filter by a specific word and that same word in your spreadsheet has a space after it, the formula won't count it in the result. A large portion of my dataset had a space at the end, which I didn't realize, and that is what threw off my result. The formula =COUNTIFS('Type',REGEX("^(Development|Support)$")) works perfectly to search a specific string of keywords. Just make sure your data is clean!

[@SanjaySinghChauhan007]

Can we record macro just like in excel.....?

[@apple-a-day]

Hi there, unfortunately Numbers does not have the equivalent of Excel Macros. You might be able to achieve some automated behaviour using AppleScript but it's completely different and can be extremely frustrating and will never match what you experience with Macros. This is definitely one feature I wish Numbers would adopt. Thanks for watching!

[@SanjaySinghChauhan007]

@@apple-a-day Then Number never beat Excel ..

[@apple-a-day]

@@SanjaySinghChauhan007 Yeah for this feature it sure does

[@kussface1]

how do I select more than one column to check for the match? keeps giving me error 🥴

[@apple-a-day]

I don't believe this is allowed. You can do a separate check for data in another column using SUMIFS, but that probably won't work for you because each condition that is checked in a multiple condition scenario must be TRUE.

[@kussface1]

thank you

[@apple-a-day]

You're welcome! Thanks for watching!

[@stevenlong2338]

Trying to set up a spreadsheet for my wife's business. I don't know what I'm doing wrong. The total for categories keeps equaling $0.00. Not sure how to resolve this.

[@apple-a-day]

Hi Steven, that's a tough one to figure out without having your spreadsheet in front of me. If you like you can email it to me and I'll take a look. Obviously remove any private information first or make a new one with dummy data that has the same issue.

[@Mahdi.jamalll]

Thx 🙏🏻

[@apple-a-day]

You’re welcome 😊

[@tomgarrodcoaching1056]

IVE DONE THIS AND IT ALWAYS COMES UP WITH A ERROR MESSAGE

[@apple-a-day]

I’m happy to help figure out what went wrong. Please provide the specific error message as well as the formula that caused it.