Forum Discussion
Help with Excel formula
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
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!
Wow!!!!! Fantastic!!! I have 6,000 rows of data (dates and value) and you' just saved my life!! Thank you a million!!!!! Haytham Amairah
