SUM(), SUMIF(), and SUMIFS() explained for Apple Numbers!
HTML-код
- Опубликовано: 20 ноя 2022
- How to use Numbers on a Mac
This is a thorough tutorial on using the these functions in Apple Numbers:
SUM
SUMIF
SUMIFS
COUNTBLANK
Here's is a DropBox link to the Numbers document used in this tutorial:
www.dropbox.com/t/LJ4ddB4qZ9c...
This tutorial is useful for anyone learning about formulas in Numbers however it is aimed at beginners.
Check out my other tutorials for Apple Numbers:
• HOUR(), MINUTE(), and ...
• DAY(), DAYNAME(), and ...
• EOMONTH() function in ...
• EDATE() function in Ap...
• TODAY() and NOW() func...
• REGEX() and SUMIF() - ...
• DATEVALUE() function i...
• DATEDIF() function in ...
• DATE() function in App...
• Create CHARTS in Apple...
Mac Numbers Numeric Functions
#numbers, #apple, #spreadsheet, #spreadsheettutorial, #sumif, #sumifs, #countblank #sum
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.
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!
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.
That’s great to hear!! Glad it was helpful and thanks for watching!
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.
Thanks for the kind words! I’m glad this video was able to help you out. Thanks for watching!
Exactly 👍 👏👏👏👏👏🏆
Brilliantly helpful thank you! SUMIFS are a lot easier than I thought
Thanks so much! Glad it was helpful!
This video made my day! Thank you so much!
You are so welcome! Thanks for letting me know and thanks for watching!
This video is so clear and concise. Thank you so much for making it.
Thanks so much! I appreciate the kind words. Thanks for watching!
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.
Thanks so much for the kind words - much appreciated. And thanks for watching!
Wow thanks. This was just what I need. I never realised that making a second table will be much better for what I wanted to do.
That’s great to hear! Glad this was helpful. Thanks for watching!
Cheers for the tutorial, very helpful! Keep up the good work :)
Glad it helped! Thanks for watching!
Great video, thank you so much!
Glad it was helpful! Thanks for watching!
Brilliant tutorial clear and concise
Glad it was helpful!
Excellent! Thank you so much.
You're very welcome! Thanks for watching!
🙏Thank you very much for this easily understandable tutorial. Now I can finish my tax declaration.🤘
Glad it helped! Thanks for watching!
Worthy for a like and subscribe …. Excellent explanations and examples … than you!
Awesome, thank you!
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.
Thanks! Very cool! Good luck!
Thanks, great video!
Glad you liked it! Thanks for watching!
Very well explained. Is there a way to do it if you have multiple columns?
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
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?
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!
Honestly Numbers is underrated, you get a lot of value for free 🎉
It’s so true. I haven’t had to use Excel in years.
Very clearly explained. Yes, very helpful.
Could you show how to calculate age that will update when the spreadsheet is opened?
Thanks for the feedback! I'll look into that and get back to you.
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???
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!
Please could you do statistic function on number ( eg chi , t test etc)
I'll add it to the list! Thanks!
how to have a sum cell follow last input so that i can have a running total?
Thx
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!
Thx 🙏🏻
You’re welcome 😊
can someone assist me how to use countif in MacBook excel. I cannot do it like normal
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!
thank you
You're welcome! Thanks for watching!
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!
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!
@@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! 💕💕💕
@@flavorbby Glad you found a workaround. Thanks so much and have a great day!
how do I select more than one column to check for the match? keeps giving me error 🥴
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.
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.
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.
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?
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.
@@apple-a-day Amazing! Thank you for the fast response and great explanation. This one thing is a game changer for me.
@@kevy213 You're welcome! Glad I could help!
@@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!
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!
Can we record macro just like in excel.....?
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!
@@apple-a-day Then Number never beat Excel ..
@@SanjaySinghChauhan007 Yeah for this feature it sure does
IVE DONE THIS AND IT ALWAYS COMES UP WITH A ERROR MESSAGE
I’m happy to help figure out what went wrong. Please provide the specific error message as well as the formula that caused it.