Forum Discussion
Solved
Help with Excel formula
I need help with Excel to return the sum of the values in a column if certain condition. In the table below, the in the cell next to Month 1, I need to add a formula that will tell Excel to return th...
Hi,
You can use this formula:
=SUMPRODUCT((YEAR($A$2:$A$16)=E7)*(MONTH($A$2:$A$16)=F7),$B$2:$B$16)
Hope that helps
I hope you can help again. Need help with a formula which will exclude zero values based on conditions. The table below explains it. I definitely need to a wider range of data, thus the extended range. I read Small formula can exclude the the lowest value. But don't know how to combine with the formula below. Thank you if you can provide some help. For some reason I can post it to all. Thanks.
| Date | Price | |||||||||||||||
| Tuesday, May 22, 2018 | 1,560 | |||||||||||||||
| Wednesday, May 23, 2018 | 1,550 | Month | 5 | 6 | ||||||||||||
| Thursday, May 24, 2018 | 1,545 | Year | 2018 | 2018 | ||||||||||||
| Friday, May 25, 2018 | 1,545 | MAX | 1,560 | 1,530 | MAX Formula: MAX(INDEX((MONTH($A$2:$A$5000)=$F3)*(YEAR($A$2:$A$5000)=$F4)*$B$2:$B$5000,0)) | |||||||||||
| Monday, May 28, 2018 | 1,430 | MIN | 0 | 0 | MIN Formula: ???? (Excluding zero) | |||||||||||
| Tuesday, May 29, 2018 | 1,430 | |||||||||||||||
| Wednesday, May 30, 2018 | 1,350 | |||||||||||||||
| Thursday, May 31, 2018 | 1,430 | |||||||||||||||
| Friday, June 1, 2018 | 1,430 | |||||||||||||||
| Monday, June 4, 2018 | 1,530 | |||||||||||||||
| Tuesday, June 5, 2018 | 1,530 | |||||||||||||||
| Wednesday, June 6, 2018 | 1,525 | |||||||||||||||
| Thursday, June 7, 2018 | 1,475 | |||||||||||||||
| Friday, June 8, 2018 | 1,475 | |||||||||||||||
| Tuesday, June 12, 2018 | 1,475 | |||||||||||||||
| Wednesday, June 13, 2018 | 1,475 | |||||||||||||||
| Friday, June 15, 2018 | 1,500 | |||||||||||||||
| Monday, June 18, 2018 | 1,500 | |||||||||||||||
| Tuesday, June 19, 2018 | 1,500 | |||||||||||||||
| Wednesday, June 20, 2018 | 1,500 | |||||||||||||||
| Thursday, June 21, 2018 | 1,500 | |||||||||||||||
| Friday, June 22, 2018 | 1,500 | |||||||||||||||
| Monday, June 25, 2018 | 1,500 |
That could be
=AGGREGATE(15,6,1/(MONTH($A$2:$A$5000)=$F3)/(YEAR($A$2:$A$5000)=$F4)*$B$2:$B$5000,1)And if take 14 as first parameter it returns max.
SergeiBaklan Yay!!!! Thank you Sergei!!! It works like a charm! You guys are incredible!
Herrmenan , you are welcome