SOLVED

Help with Excel formula

Copper Contributor

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. 

 

DateValue     
1/1/2014      
1/8/2014      
1/15/2014100     
1/22/2014      
1/29/2014   YearMonthValue
2/5/2014150  20141??
2/12/2014   20142???
2/19/2014   20143????
2/26/2014200     
3/5/2014      
3/12/2014      
3/19/2014      
3/26/2014300     
4/2/2014      
4/9/2014      
8 Replies
best response confirmed by Herrmenan (Copper Contributor)
Solution

@Herrmenan

 

Hi,

 

You can use this formula:

=SUMPRODUCT((YEAR($A$2:$A$16)=E7)*(MONTH($A$2:$A$16)=F7),$B$2:$B$16)

SUMPRODUCT function.png

 

Hope that helps

Wow!!!!! Fantastic!!! I have 6,000 rows of data (dates and value) and you' just saved my life!! Thank you a million!!!!! @Haytham Amairah 

@Herrmenan 

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. 

@Haytham Amairah 

 

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. 

 

DatePrice               
Tuesday, May 22, 2018           1,560               
Wednesday, May 23, 2018           1,550 Month56           
Thursday, May 24, 2018           1,545 Year20182018           
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 MIN00 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               

@Herrmenan 

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.

@Sergei Baklan Yay!!!! Thank you Sergei!!! It works like a charm! You guys are incredible!

@Herrmenan , you are welcome

1 best response

Accepted Solutions
best response confirmed by Herrmenan (Copper Contributor)
Solution

@Herrmenan

 

Hi,

 

You can use this formula:

=SUMPRODUCT((YEAR($A$2:$A$16)=E7)*(MONTH($A$2:$A$16)=F7),$B$2:$B$16)

SUMPRODUCT function.png

 

Hope that helps

View solution in original post