Hi computergaga. Thanks for your videos! Is there a way to sum on first cell of a column and the last one ONLY? Being a range of cells that can decrease or increase. I did it how you should it in the video but of course, it sums up all the cells. Many thanks!
Hi, is there a way to dynamic sum certain categories? for example I would like to have the sum of "Condiments" or the sum of "Beverages" when I have different quantity of lines for each
Hi Gal, yes sure. You could use the SUMIF function. And the category could be written in a cell such as J2. If column B was the list of categories and column D was the sales we could write = SUMIF(B:B,J2,D:D)
In the same table, if you want to add Bonus in column H, where in whenever the amount reaches 50000, you mention 500 as bonus.. this should happen for every 50000 mark reached. How do we do this?
You're not missing anything. That works. But an example like in the video will work when a column is not dedicated to only one set of values. Anyway, the video is 9 years old and the modern method is to use a table and reference a table column. Tables are dynamic.
Sounds like you need a SUMIF function. This link will show you how to sum values next to cells that contain text like "UK" - www.computergaga.com/blog/using-wildcard-characters-in-excel-formulas/
@@Computergaga Sir, To be clear in my requirement I am once again wanted to explain. . Sir the data is being input in rows and the corresponding values are put against the next coloum. Let us say if I have input a same word again ,let us say UK thrice then I should have the sum of the UK available for me displayed in a cell which is the sum of the UK value previously entered, so that I can give or enter a value such that the sum Of all UK values are less than a budgeted value. Hope I have put my query. Thank You in advance.
Depends what you are trying to achieve Archana. This is an old video, and a dynamic sum is performed automatically by tables - ua-cam.com/video/3c2en81oivA/v-deo.html
Greetings, If you were to, for example, want the sum to display only those amounts that agree with a filter from a different column, can that be done? For example, if you were to set up a filter, at the top of the sheet, and filter by Customer Code, and you set the filter to ALFKI, could you have the sum reflect the total of only those amounts that reflect your column F filter? And then, when you change the filter to show, for example, ANATR, have the sum reflect that new choice? Thank you.
Sure. You could use the AGGREGATE function - ua-cam.com/video/204fJYMMtQA/v-deo.html This function can sum values and ignore the hidden rows. If the numbers you were summing were in column G, it would be =AGGREGATE(9,5,G:G)
Is it possible to write a function where the total never decreases even when your reference does? for example If I work for a bank and I want to to know the total amount our patrons deposit over a year, regardless of whether they withdraw that money later, how could I write a function for that?
there is a difference between this function and the one shown in the video. sum(g:g) will calculate all the values in the whose column regardless of the current range. while the function in the video will calculate only the values in the currentRange. to check it press a given value in range (G1000), and test the two formulas. thanks!
Thanks, mister. 10years on and it's still relevant and helpful :)
Glad to hear it!
Extremely grateful, this was exactly what i was looking for. God bless you sir.
You're very welcome
You are an absolute legend mate, i was trying everything i could to make this function work.. Thank you very much!
No problem 👍
I had to use semicolons instead of commas and it worked! Thanks.
Hi computergaga. Thanks for your videos!
Is there a way to sum on first cell of a column and the last one ONLY? Being a range of cells that can decrease or increase. I did it how you should it in the video but of course, it sums up all the cells. Many thanks!
great video computergaga, quite useful!
Glad you think so! Thank you.
awesome!! exactly what I was looking for. Thank you for this!
My pleasure!
Thank you very much. This is what I was searching for. Nice video.
Thank you 👍
Hi, is there a way to dynamic sum certain categories? for example I would like to have the sum of "Condiments" or the sum of "Beverages" when I have different quantity of lines for each
Hi Gal, yes sure. You could use the SUMIF function. And the category could be written in a cell such as J2. If column B was the list of categories and column D was the sales we could write = SUMIF(B:B,J2,D:D)
@@Computergaga thank you!!
You're very welcome.
THANK YOU!!!! Omg why isn’t this covered in the hundred other videos
You're welcome, Joshua.
In the same table, if you want to add Bonus in column H, where in whenever the amount reaches 50000, you mention 500 as bonus.. this should happen for every 50000 mark reached. How do we do this?
good question
Maybe I am missing something here, but why not just 'SUM' Column 'G' (in this example), as Excel will ignore empty cells until they have a value ?
You're not missing anything. That works. But an example like in the video will work when a column is not dedicated to only one set of values.
Anyway, the video is 9 years old and the modern method is to use a table and reference a table column. Tables are dynamic.
Helpful and right to the point. Thank you!
You're welcome. Thank you.
Awesome!! Timely help.. thanks Alan
Excellent as usual
Thank you, Sahil.
I want to perform sum with a criteria such that if there are similar cells like UK as above and return the value next to that cell.
Sounds like you need a SUMIF function. This link will show you how to sum values next to cells that contain text like "UK" - www.computergaga.com/blog/using-wildcard-characters-in-excel-formulas/
@@Computergaga Sir, To be clear in my requirement I am once again wanted to explain. . Sir the data is being input in rows and the corresponding values are put against the next coloum. Let us say if I have input a same word again ,let us say UK thrice then I should have the sum of the UK available for me displayed in a cell which is the sum of the UK value previously entered, so that I can give or enter a value such that the sum Of all UK values are less than a budgeted value.
Hope I have put my query.
Thank You in advance.
Thank you. it's a good help
You're very welcome Luke.
Does it work on same colume too? It didn't work for me.
Depends what you are trying to achieve Archana. This is an old video, and a dynamic sum is performed automatically by tables - ua-cam.com/video/3c2en81oivA/v-deo.html
@@Computergaga Thank you! I will refer this link.
Greetings,
If you were to, for example, want the sum to display only those amounts that agree with a filter from a different column, can that be done?
For example, if you were to set up a filter, at the top of the sheet, and filter by Customer Code, and you set the filter to ALFKI, could you have the sum reflect the total of only those amounts that reflect your column F filter? And then, when you change the filter to show, for example, ANATR, have the sum reflect that new choice?
Thank you.
Sure. You could use the AGGREGATE function - ua-cam.com/video/204fJYMMtQA/v-deo.html
This function can sum values and ignore the hidden rows.
If the numbers you were summing were in column G, it would be =AGGREGATE(9,5,G:G)
Excellent!
You earned a new subscriber.
:-)
Thank you.
Awesome. Thank you.
Is it possible to write a function where the total never decreases even when your reference does? for example If I work for a bank and I want to to know the total amount our patrons deposit over a year, regardless of whether they withdraw that money later, how could I write a function for that?
You would need to convert the formula to a value to stop it updating again in the future. You can do this by Copy dn Paste Special Values.
Thanks! Was a great help, quite simple.
Great to hear, thanks jesus.
Thank you so much!
Thanks a lot sir >> good explain
You're welcome Essam. Thank you.
What is sapbe I cant
thank you for the video!
My pleasure Birasa.
Thank you kind sir :) !!!!
No problem tsurek. Happy to help.
thank you
You're welcome.
even sum function also works in same way
İt helped a lot thanks
My pleasure Fatih.
Brilliant 👌👌
Thank you very much.
It's the same us SUM(G:G)
there is a difference between this function and the one shown in the video. sum(g:g) will calculate all the values in the whose column regardless of the current range. while the function in the video will calculate only the values in the currentRange. to check it press a given value in range (G1000), and test the two formulas. thanks!
BOoM
💣💥
worst formula i ever seen.. u can simply do sum("A:A")..
True, as long as you do not have other values in column A that you do not need summed. Different formulas for different scenarios.