how to put data under the date month

SUMIF MONTH pivot table tables

#1 Ruby

Ruby

Newbie

• Members
• 1 posts

Posted 12 January 2016 - 03:27 PM

Hello, i have a problem:if date is a column, how to set its value/b colunm to correct c, or d, or e column

a b c d e date  value Jan Feb Mar 2016/1/6 100 100 　 　 2016/2/9 200 　 200 　 2016/3/9 300 　 　 300

i tried "if " formulas,but it seems difficult to make it.

hope some one could coud help.

#2 Jonathan

Jonathan

• 888 posts

Posted 12 January 2016 - 09:08 PM

Hi Ruby,

It's very hard for me to understand what you're asking, but I think you're trying to do something like this:

In other words, you're trying to summarize your data by month.

You could do this with the SUMIF and MONTH functions, but it might be a better solution to use a Pivot Table. Pivot Tables are specifically intended to be used to summarize data like this.

You can learn more about how to create a pivot table in the video tutorial: Expert Skills Lesson 5-1 Create a pivot table.

If you'd prefer to use formulas, the first thing to do is add a Month column to your data and use the MONTH function to extract the month from each date.

For instructions on how to use the MONTH function and other common date functions, see the video lesson: Expert Skills Lesson 3-8 Use the Excel YEAR function and other common date functions.

Once you have the month for each date, you can easily create SUMIF formulas like this:

=SUMIF(Data[Month],1,Data[Value])

For more on SUMIF, see the video lesson: Expert Skills Lesson 3-6 Create an Excel SUMIF function and Excel COUNTIF function.

Data[Month] and Data[Value] are structured table references, which allow you to create formulas that will automatically adjust if more data is added to the table.

You can learn about tables and structured table references in the video lesson: Expert Skills Lesson 1-17 Name a table and create an automatic structured table reference.

I have attached an example workbook showing both approaches in action:

I hope this is the answer you were looking for, but please feel free to reply if you need any more help. Note that you can attach an example workbook to your question by following the instructions here.

Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.

Also tagged with one or more of these keywords: SUMIF, MONTH, pivot table, tables

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users