Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum current months values
If Column A is dates for this year (1-1-05, 1-2-05,...) and column C is
values corresponding to those dates. How can I sum this months values only (so that next month it will do the same without changing my sum range). If it helps, I have the start and end day of the month in two cells... Thanks for the help. John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum current months values
Take a look at the sumproduct formula...
http://www.xldynamic.com/source/xld.SUMPRODUCT.html This should work for you. -- HTH... Jim Thomlinson "John" wrote: If Column A is dates for this year (1-1-05, 1-2-05,...) and column C is values corresponding to those dates. How can I sum this months values only (so that next month it will do the same without changing my sum range). If it helps, I have the start and end day of the month in two cells... Thanks for the help. John |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum current months values
=sumif(A:A,"<="&EndDate,C:C)-Sumif(A:A,"<"&StartDate,C:C)
would be one way. -- Regards, Tom Ogilvy "John" wrote in message ... If Column A is dates for this year (1-1-05, 1-2-05,...) and column C is values corresponding to those dates. How can I sum this months values only (so that next month it will do the same without changing my sum range). If it helps, I have the start and end day of the month in two cells... Thanks for the help. John |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum current months values
I figured it out based on other posts but I have no idea why this works....
=SUMPRODUCT((($A$9:$A$371='Sales Credit'!$P$31))*($A$9:$A$371<='Sales Credit'!$P$32),'Sales ID'!C9:C371) what does a sumproduct do? "John" wrote: If Column A is dates for this year (1-1-05, 1-2-05,...) and column C is values corresponding to those dates. How can I sum this months values only (so that next month it will do the same without changing my sum range). If it helps, I have the start and end day of the month in two cells... Thanks for the help. John |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum current months values
Thanks for the replies.
Tom I assume your "enddate" could be a cell value... Thanks again. "Tom Ogilvy" wrote: =sumif(A:A,"<="&EndDate,C:C)-Sumif(A:A,"<"&StartDate,C:C) would be one way. -- Regards, Tom Ogilvy "John" wrote in message ... If Column A is dates for this year (1-1-05, 1-2-05,...) and column C is values corresponding to those dates. How can I sum this months values only (so that next month it will do the same without changing my sum range). If it helps, I have the start and end day of the month in two cells... Thanks for the help. John |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum current months values
On Mon, 22 Aug 2005 11:00:03 -0700, "John"
wrote: If Column A is dates for this year (1-1-05, 1-2-05,...) and column C is values corresponding to those dates. How can I sum this months values only (so that next month it will do the same without changing my sum range). If it helps, I have the start and end day of the month in two cells... Thanks for the help. John =SUMIF(A:A,""&TODAY()-DAY(TODAY()),B:B)- SUMIF(A:A,""& 32+TODAY()-DAY(TODAY())- DAY(32+TODAY()-DAY(TODAY())),B:B) will always show the sum of "this month's" values. The formula states "sum all the values in column B that correspond to dates that are greater than the last day of the previous month; then subtract the sum of all those values in column B that correspond to dates greater than the last day of the current month". --ron |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum current months values
Your formula works because in a computer True is 1 and False is 0 (not
exactly 100% true but good enough). So when you say ($A$9:$A$371='Sales Credit'!$P$31) you end up with an array of essentially 1's and 0's depending if the date is greater than or equal to 'Sales Credit'!$P$31. The same is true of ($A$9:$A$371<='Sales Credit'!$P$32). 0 times anything is going to be zero. The * is equivalent to an and condion so the date will have to be greater than the start date AND less than the end date for the value in C to be included. The web address I posted might be a little more clear on this point than I just was. -- HTH... Jim Thomlinson "John" wrote: I figured it out based on other posts but I have no idea why this works.... =SUMPRODUCT((($A$9:$A$371='Sales Credit'!$P$31))*($A$9:$A$371<='Sales Credit'!$P$32),'Sales ID'!C9:C371) what does a sumproduct do? "John" wrote: If Column A is dates for this year (1-1-05, 1-2-05,...) and column C is values corresponding to those dates. How can I sum this months values only (so that next month it will do the same without changing my sum range). If it helps, I have the start and end day of the month in two cells... Thanks for the help. John |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum current months values
both enddate and start date are meant to be cell references
=sumif(A:A,"<="&M2,C:C)-Sumif(A:A,"<"&M1,C:C) where M2 holds the end date and M1 holds the start date (for example) -- Regards, Tom Ogilvy "John" wrote in message ... Thanks for the replies. Tom I assume your "enddate" could be a cell value... Thanks again. "Tom Ogilvy" wrote: =sumif(A:A,"<="&EndDate,C:C)-Sumif(A:A,"<"&StartDate,C:C) would be one way. -- Regards, Tom Ogilvy "John" wrote in message ... If Column A is dates for this year (1-1-05, 1-2-05,...) and column C is values corresponding to those dates. How can I sum this months values only (so that next month it will do the same without changing my sum range). If it helps, I have the start and end day of the month in two cells... Thanks for the help. John |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum current months values
You can go to the reference provided by Thomlinson, but be advised that
Sumproduct is slower than Sumif. Shouldn't be a perceptible difference in your case. -- Regards, Tom Ogilvy "John" wrote in message ... I figured it out based on other posts but I have no idea why this works.... =SUMPRODUCT((($A$9:$A$371='Sales Credit'!$P$31))*($A$9:$A$371<='Sales Credit'!$P$32),'Sales ID'!C9:C371) what does a sumproduct do? "John" wrote: If Column A is dates for this year (1-1-05, 1-2-05,...) and column C is values corresponding to those dates. How can I sum this months values only (so that next month it will do the same without changing my sum range). If it helps, I have the start and end day of the month in two cells... Thanks for the help. John |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum current months values
I would have thought that 1 sumproduct would be faster than 2 sum if's. I
believe you, but what would the reason behind that be? Or is it just one of those trueths that can be accepted at face value. -- HTH... Jim Thomlinson "Tom Ogilvy" wrote: You can go to the reference provided by Thomlinson, but be advised that Sumproduct is slower than Sumif. Shouldn't be a perceptible difference in your case. -- Regards, Tom Ogilvy "John" wrote in message ... I figured it out based on other posts but I have no idea why this works.... =SUMPRODUCT((($A$9:$A$371='Sales Credit'!$P$31))*($A$9:$A$371<='Sales Credit'!$P$32),'Sales ID'!C9:C371) what does a sumproduct do? "John" wrote: If Column A is dates for this year (1-1-05, 1-2-05,...) and column C is values corresponding to those dates. How can I sum this months values only (so that next month it will do the same without changing my sum range). If it helps, I have the start and end day of the month in two cells... Thanks for the help. John |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum current months values
Thanks again everyone!
"Ron Rosenfeld" wrote: On Mon, 22 Aug 2005 11:00:03 -0700, "John" wrote: If Column A is dates for this year (1-1-05, 1-2-05,...) and column C is values corresponding to those dates. How can I sum this months values only (so that next month it will do the same without changing my sum range). If it helps, I have the start and end day of the month in two cells... Thanks for the help. John =SUMIF(A:A,""&TODAY()-DAY(TODAY()),B:B)- SUMIF(A:A,""& 32+TODAY()-DAY(TODAY())- DAY(32+TODAY()-DAY(TODAY())),B:B) will always show the sum of "this month's" values. The formula states "sum all the values in column B that correspond to dates that are greater than the last day of the previous month; then subtract the sum of all those values in column B that correspond to dates greater than the last day of the current month". --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
current average turnover by months | Excel Worksheet Functions | |||
In a range of months can I capture the most current month entry? | Excel Discussion (Misc queries) | |||
Automatically update charts for only the most current 12 months? | Charts and Charting in Excel | |||
sum automatically from last 12 months based on current date | Excel Worksheet Functions | |||
Dates to months and calculating values for their months | Excel Programming |