Jump to content


Photo

how to put data under the date month

SUMIF MONTH pivot table tables

  • Please log in to reply
1 reply to this topic

#1 Ruby

Ruby

    Newbie

  • Members
  • Pip
  • 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

    Forums Administrator

  • Administrators
  • 887 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:

 

datesummary.png

 

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:

 

Attached File  DateSummary.xlsx   15.49KB   106 downloads

DateSummary3.png

 

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.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users