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

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

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

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

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

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

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

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

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

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

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