Gary, you are awesome. Thanks for explaining the different options. One other option I tried was to take the displayed table and paste into a new sheet. It was a one time thing but you got me thinking about options.
Gary thanks for your really helpful videos that are so clearly explained. I have seen several and was wondering when you created a table, such as the one cell Filter table, each time you click in it to enter data, a table frame appears around it for the columns and rows adjustments. Is it possible to set the table to operate as a data field and not have the frame appear when entering data? Thanks.
Your videos are great thanks, very helpful. BUT when trying to sift through entries imported from another location my entries under "type" had various miscellaneous differences, eg a date at the end or a location of, say, the shop used... I had to do a bit of googling and found that an asterisk after a selected piece of the text excluding the variables sorted it out and allowed the formula to group despite those differences. In your example it would have been Travel abc 01/13, travel def 09/15..... etc. as example. By using "Travel*" in the formula it ignored the other stuff.
I have a question -- what if you want to show a combination of the two. For example, instead of marketing or travel, you want to show marketing and travel. How would you do that? Additionally, do you have a video that shows sumifs? Sumifs incorporate a lot more than the singular sumif.
Use the OR function to combine two tests and check for either or. SUMIFS would only work if all of the conditions are met, but you want one OR the other.
@@macmost sorry for being unclear. I want to show both marketing AND travel or office AND marketing. Would I still use the OR function? Would I need the AND function?
@@ColBloodNDN In my example, an item can't be two things at once. But any way you want to do it you just have to figure out the logic and use the OR or AND functions, or a combination of the two.
I wanna also show the sum for the years (2019, 2020, etc..). How would I use the sumif function to display the correct amount when I'm searching either for years or types?
@@djsotosmc Oh, so you don't want to sum the year values, you just want to get the sum of another value based on the year. Create a new column for the year and use the YEAR function to put just the year in it from the date. Then use SUMIF to get the sum for a specific year. Or use Categories to simply group everything by year and you can get the sum of each category.
This is great, but I can't get the first basic sum formula to work. When I type =sum and then click on say column C, I only get the number in the first row, it does not add all numbers in the column. Can't figure out what I'm doing wrong.
Great stuff Gary ! I'm trying to use the SUMIF but resulting Syntax error all the time. I'll explain: I have a column called results with rows of different products. Appart from the sheet I have the Results column I have done 12 other sheets for each month with the respective products named ( sort of a sales follow-up ). What I'd like to do now is to have all the sums from each month added to Results. How do I do this ? With a single sheet it works very well: SUMIF (January::Transactions:: Category) ; (A2 ) ; (January::Transactions::$Amount ) but how do I add February etc. ?
@@macmost I type OR in front of the existing formula, it enters complete unentered formula in front of the existing entry, unlike where you just have the opening bracket appear.
You pointer is like a bee, it floats around sometimes for no reason which is annoying. Way to fast . If you know what you are talking about it makes sense if you don’t then you Are lost with the arrow flying around . Learn to teach better and more people will give it a try but it gets too much although I still need to to learn this !
It's sick how much enjoyment I get out of a well-run spreadsheet.
Excellent as always 👍🏻
These videos are so underrated!
thank you so much for your videos - so helpful!!
Gary, you are awesome. Thanks for explaining the different options. One other option I tried was to take the displayed table and paste into a new sheet. It was a one time thing but you got me thinking about options.
Bro you are clutch I've watched all your videos
This is awesome! Was searching for this for quite some time! Thank you very much :)
This is PERFECT!!!!!! This is exactly what I was looking to do!!!!!! Thanks Gary!!!!
You deserve some funding by companies using Apple Number for making life of their employees so much easier!
Excellent video.... If Numbers supported better filtering and Sub-Total functions (like Excel) this would be so much easier
OK, thanks
Life (and time) saver! Thank you sir!
Thank you. Fabulous tutorial!
Thank you, this helped me a lot 👏🏻
Gary thanks for your really helpful videos that are so clearly explained. I have seen several and was wondering when you created a table, such as the one cell Filter table, each time you click in it to enter data, a table frame appears around it for the columns and rows adjustments.
Is it possible to set the table to operate as a data field and not have the frame appear when entering data? Thanks.
No, that's the basic spreadsheet interface.
@@macmost OK, thanks Gary. Pity it can't be hidden for neater interfaces. Anyway, thanks very much.
Your videos are great thanks, very helpful. BUT when trying to sift through entries imported from another location my entries under "type" had various miscellaneous differences, eg a date at the end or a location of, say, the shop used... I had to do a bit of googling and found that an asterisk after a selected piece of the text excluding the variables sorted it out and allowed the formula to group despite those differences. In your example it would have been Travel abc 01/13, travel def 09/15..... etc. as example. By using "Travel*" in the formula it ignored the other stuff.
Oh, and if the word you mean is n the middle you put * either side within the quotes
Brilliant. Thank you.
As always, great video!
I have a question -- what if you want to show a combination of the two. For example, instead of marketing or travel, you want to show marketing and travel. How would you do that? Additionally, do you have a video that shows sumifs? Sumifs incorporate a lot more than the singular sumif.
Use the OR function to combine two tests and check for either or. SUMIFS would only work if all of the conditions are met, but you want one OR the other.
@@macmost sorry for being unclear. I want to show both marketing AND travel or office AND marketing. Would I still use the OR function? Would I need the AND function?
@@ColBloodNDN In my example, an item can't be two things at once. But any way you want to do it you just have to figure out the logic and use the OR or AND functions, or a combination of the two.
I wanna also show the sum for the years (2019, 2020, etc..). How would I use the sumif function to display the correct amount when I'm searching either for years or types?
I don't understand. Do you have date values? Like March 1, 2020? And you want to add years, like 2020+2017 to get what? 4037? That doesn't make sense.
@@macmost I wanna show the amount corresponding to each year, also the amount corresponding to each type.
@@djsotosmc Oh, so you don't want to sum the year values, you just want to get the sum of another value based on the year. Create a new column for the year and use the YEAR function to put just the year in it from the date. Then use SUMIF to get the sum for a specific year. Or use Categories to simply group everything by year and you can get the sum of each category.
@@macmost Thanx for the response. I really like your videos
This is great, but I can't get the first basic sum formula to work. When I type =sum and then click on say column C, I only get the number in the first row, it does not add all numbers in the column. Can't figure out what I'm doing wrong.
What how I do it carefully in the video. Make sure you are clicking in the right place.
Hello, how you will do the same with filtering Dates?
Do you mean find the total in a range of dates? See ruclips.net/video/p1tqSLkxHdA/видео.html
Great stuff Gary ! I'm trying to use the SUMIF but resulting Syntax error all the time. I'll explain: I have a column called results with rows of different products. Appart from the sheet I have the Results column I have done 12 other sheets for each month with the respective products named ( sort of a sales follow-up ). What I'd like to do now is to have all the sums from each month added to Results. How do I do this ? With a single sheet it works very well: SUMIF (January::Transactions:: Category) ; (A2 ) ; (January::Transactions::$Amount ) but how do I add February etc. ?
I could only get SUMIF to work when the test-values resulted from a pure boolean (as in this example), and not the result of an if...then statement.
how did you modify the cell to include the OR expression? I cant get it to add to the existing formula
I just select the cell and click it to edit it and type into it.
@@macmost I type OR in front of the existing formula, it enters complete unentered formula in front of the existing entry, unlike where you just have the opening bracket appear.
@@jonescrusher1 Make sure you click to place the blinking text cursor before the existing formula.
@@macmost got it, thanks, and thanks for video, very helpful
Don't categories and subtotal do the same thing?
For a lot of use cases, yes.
You pointer is like a bee, it floats around sometimes for no reason which is annoying. Way to fast . If you know what you are talking about it makes sense if you don’t then you
Are lost with the arrow flying around . Learn to teach better and more people will give it a try but it gets too much although I still need to to learn this !