Excellent explanation! I noticed the formula that you actually used in the example is summing the desired values in column C, although at the end of the video the formula shown as "solution" in cell B5, which you unhide by changing the font color, sums cells in column B, which is slightly confusing. I don't think that formula would even work for anything given the data in the sheet. Viewers should use the formula in the formula bar during the explanation. Just thought I would mention that if someone gets confused.
Thank you very much. Very clear. Hopefully, MS will find a simplified way to do this. But agree with the other comments, the best explanation I found. Thank you.
I could not get the formalua to work until I realised that I had to copy a cell that said "fastemers" from the array into the B7 cell becasue if you just type the word "fasteners" in B7 , it may look the same but the formala may not recognise it. Great video thank you!!
thanks for the video, it is very nice. easier way: use subtotal with the code 109 instead of 9 and it will automatically provide you the sum of the visible cells only and will ignore the hidden cells .
Great!! Thank you! The formula was not working for me because where you use commas, in my excel is semicolon. It worked after I realized and adapted it.
How do you use SUMIf function based on filtered data, for example i am looking for a negative and positive values in same column but it will give me grand total, but i want a total of both negative and positive values based of a certain date. could you help ?
I tried replicating the formula, but didn't work for me :( =SUMPRODUCT(SUBTOTAL(9,OFFSET(WR0505_WR1012[Labour Cost],ROW(WR0505_WR1012[Labour Cost])-MIN(ROW(WR0505_WR1012[Labour Cost])),,1)),--(WR0505_WR1012[MRP Controller]="41\CSM (Sheet Metal)")) I am doing anything wrong?
This is the best explanation of this scenario I have found anywhere on the web! Well done.
I spent hours failing to figure out this exact problem. Thank you!
Excellent explanation! I noticed the formula that you actually used in the example is summing the desired values in column C, although at the end of the video the formula shown as "solution" in cell B5, which you unhide by changing the font color, sums cells in column B, which is slightly confusing. I don't think that formula would even work for anything given the data in the sheet. Viewers should use the formula in the formula bar during the explanation. Just thought I would mention that if someone gets confused.
Thank you for sharing this tip. Very nicely crafted, well thought out formula indeed!
Thank you very much. Very clear. Hopefully, MS will find a simplified way to do this. But agree with the other comments, the best explanation I found. Thank you.
I could not get the formalua to work until I realised that I had to copy a cell that said "fastemers" from the array into the B7 cell becasue if you just type the word "fasteners" in B7 , it may look the same but the formala may not recognise it. Great video thank you!!
thanks for the video, it is very nice.
easier way: use subtotal with the code 109 instead of 9 and it will automatically provide you the sum of the visible cells only and will ignore the hidden cells .
Very helpful, I have solved my problem, Thank you, Sir, you live forever
worked really well and very good explanation. can this be done to average the values?
Great!! Thank you!
The formula was not working for me because where you use commas, in my excel is semicolon. It worked after I realized and adapted it.
Yes, it is working.Thanks brother.
Thank you man. Just saved my time.
Thanks a lot, saved my day! :)
Thanks sir very Good idea for using this formula
Finally got one that works for me - - thanks
nice and useful knowledge. please put all workbook download link in the description ...
thank you very very much. really you helped me .
Solved the problem, thank you very much!!
Thank you Thank you Thank you helped me out so much
Great Job 👍
Bloody Brilliant. Nuff Said
Excellent. Thank you.
It works. Thanks!
Thanks for your help dear.
Amazing! Thanks very much!!
The best🤩🤩🤩
How do you use SUMIf function based on filtered data, for example i am looking for a negative and positive values in same column but it will give me grand total, but i want a total of both negative and positive values based of a certain date. could you help ?
what about using a drop dwn list on CELL B6, and then use the SUMIF based on CELL B6
I tried replicating the formula, but didn't work for me :( =SUMPRODUCT(SUBTOTAL(9,OFFSET(WR0505_WR1012[Labour Cost],ROW(WR0505_WR1012[Labour Cost])-MIN(ROW(WR0505_WR1012[Labour Cost])),,1)),--(WR0505_WR1012[MRP Controller]="41\CSM (Sheet Metal)")) I am doing anything wrong?
The formula is super complicated. Is there an easier way to do this?
Maybe to use sumifs?
Would be nice if Microsoft just made a subtotal sumif formula, but that would be too easy I guess
Worked perfekt for my purpose :-)
thank you alot thank you thank you
HI
This is working in single row only, wat if the range is in two rows? this is not working in this formula
please help me
ooh wish if excel make st ready for that
ONE SHEET TO ANOTHER SHEET THIS FORMULA USEING NOT WORKING SIR