Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum only for one month in excel
Hello,
=SUMIF(Sheet1!D:D,Sheet2!C5,Sheet1!S:S) with this formula I have sum for one year,In sheet1 I have data for one year. How cen I get this data only for one month from sheet1 to sheet2. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum only for one month in excel
What you are adding is in column S, so lets assume that One Month is from S15
to S24 then: =SUMIF(Sheet1!D:D,Sheet2!C5,Sheet1!S15:S24) C5 is your criteria, so if you probably have change your C5 to something that will indicate how to identify the month in Column D... -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "kallu kall" wrote: Hello, =SUMIF(Sheet1!D:D,Sheet2!C5,Sheet1!S:S) with this formula I have sum for one year,In sheet1 I have data for one year. How cen I get this data only for one month from sheet1 to sheet2. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum only for one month in excel
You could do it this way:
=SUMPRODUCT(--(MONTH(Sheet1!D$1:D$100)=Sheet2!C5),Sheet2!S$1:S$1 00) where C5 contains a month number (1 to 12). If you have more than one year's worth of data then you will need another condition to catch the year. Note that with SUMPRODUCT you can't use full-column references (unless you have XL2007). Hope this helps. Pete On Dec 16, 7:45*pm, kallu kall wrote: Hello, =SUMIF(Sheet1!D:D,Sheet2!C5,Sheet1!S:S) with this formula I have sum for one year,In sheet1 I have data for one year. How cen I get this data only for one month from sheet1 to sheet2. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum only for one month in excel
Sorry, I think I have misunderstood what you wanted.You already have a
condition, and now you want to apply a second one to limit the sum to one month. You can do that this way: =SUMPRODUCT(--(Sheet1!D$1:D$100=Sheet2!C25),--(MONTH(Sheet1!A$1:A$100) =Sheet2!D25),Sheet2!S$1:S$100) or like this: =SUMPRODUCT((Sheet1!D$1:D$100=Sheet2!C25)*(MONTH(S heet1!A$1:A$100) =Sheet2!D25),Sheet2!S$1:S$100) Both of these assume that your dates are in column A, and that you enter a month number in D25 of Sheet2. Adjust the ranges to suit your data, subject to my earlier caveat about full-column references. Hope this helps. Pete On Dec 16, 8:01*pm, Pete_UK wrote: You could do it this way: =SUMPRODUCT(--(MONTH(Sheet1!D$1:D$100)=Sheet2!C5),Sheet2!S$1:S$1 00) where C5 contains a month number (1 to 12). If you have more than one year's worth of data then you will need another condition to catch the year. Note that with SUMPRODUCT you can't use full-column references (unless you have XL2007). Hope this helps. Pete On Dec 16, 7:45*pm, kallu kall wrote: Hello, =SUMIF(Sheet1!D:D,Sheet2!C5,Sheet1!S:S) with this formula I have sum for one year,In sheet1 I have data for one year. How cen I get this data only for one month from sheet1 to sheet2.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel to make the days cary over month to month automaticly | New Users to Excel | |||
Excel 2003 month to month data change grid | Excel Discussion (Misc queries) | |||
How do I set up 12-month rolling month cells in excel? | Excel Discussion (Misc queries) | |||
=Month function in Excel gives incorrect month | New Users to Excel | |||
show month number as month name in Excel? | Excel Discussion (Misc queries) |