Aug 23 2019 05:52 PM
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 |
Aug 23 2019 06:50 PM
Solution
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
Aug 23 2019 07:00 PM
Wow!!!!! Fantastic!!! I have 6,000 rows of data (dates and value) and you' just saved my life!! Thank you a million!!!!! @Haytham Amairah
Aug 23 2019 09:53 PM
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.
Aug 24 2019 11:56 AM
@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.
Aug 24 2019 01:40 PM
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 |
Aug 24 2019 02:06 PM
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.
Aug 24 2019 03:34 PM
@Sergei Baklan Yay!!!! Thank you Sergei!!! It works like a charm! You guys are incredible!
Aug 23 2019 06:50 PM
Solution
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