Forum Discussion
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 the sum of all values in column "Value" for the first month of 2014. Something similar to: Look in column "Date", if month of column Date = cell Ref Month 1 and if year of column Date = 2014, then return sum of all values for month 1 year 2014.
I have already tried a formula with a combination of LOOKUP(BigNum,CHOOSE({1,2} ... but the formula is not working. Thanks for any help.
| Date | Value | |||||
| 1/1/2014 | ||||||
| 1/8/2014 | ||||||
| 1/15/2014 | 100 | |||||
| 1/22/2014 | ||||||
| 1/29/2014 | Year | Month | Value | |||
| 2/5/2014 | 150 | 2014 | 1 | ?? | ||
| 2/12/2014 | 2014 | 2 | ??? | |||
| 2/19/2014 | 2014 | 3 | ???? | |||
| 2/26/2014 | 200 | |||||
| 3/5/2014 | ||||||
| 3/12/2014 | ||||||
| 3/19/2014 | ||||||
| 3/26/2014 | 300 | |||||
| 4/2/2014 | ||||||
| 4/9/2014 |
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
8 Replies
A more complicated solution would be a pivot table.
Put "Date" in the rows area and "Value" in the values area.
Group "Date" by year and month.
Detlef_Lewin Thanks Lewin. A pivot table is a great idea. But I like the formula bc I can copy accross and my sheet structure will remain the same. Thank you anyway.
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.
Wow!!!!! Fantastic!!! I have 6,000 rows of data (dates and value) and you' just saved my life!! Thank you a million!!!!! Haytham Amairah
