Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- 394K Members
- 9,756 Online
- 426K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- Help with Excel formula

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-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 |

Labels:

8 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-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

- Tags:
- SUMPRODUCT

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-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 |

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-24-2019 03:34 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Related Conversations

How to Prevent Teams from Auto-Launch

chenrylee
in
Microsoft Teams
on
06-27-2019
89.5K
Views

4 Likes

28 Replies

Early preview of Microsoft Edge group policies

Sean Lyndersay
in
Discussions
on
06-14-2019
18K
Views

20 Likes

65 Replies

*Updated 9/3* Syncing in Microsoft Edge Preview Channels

Elliot Kirk
in
Articles
on
05-01-2019
34.4K
Views

23 Likes

201 Replies

How to download windows server 2019 update to 1903

Cmakar37
in
Windows Server for IT Pro
on
07-03-2019
10.8K
Views

0 Likes

5 Replies

$cred = Get-Credential without asking for prompts in powershell

TejCGS
in
Office 365
on
04-25-2019
19.3K
Views

0 Likes

6 Replies

This form can't be distributed as it is asking for personal or sensitive information

NZLaSalle
in
Microsoft Forms
on
07-19-2019
5,400
Views

4 Likes

56 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © 2017 Microsoft