software

Did anyone figure out how to do this? There is a posting from CommuterVet
that ends with a problem that countif only took the last one.

I have a sales forecast that is by day,month,year, and I want to subtotal by
month or put it into a pivot table and have it aggregate by month, but I
can't seem to be able to do it.

Assumes dates in column A and Sales in B then following will give total for
March.

Change 3 to a cell if you the month value stored in a cell

=SUMPRODUCT(--(MONTH($A$2:$A$100)=3),--($B$2:$B$100))

C2=3

=SUMPRODUCT(--(MONTH($A$2:$A$100)=C2),--($B$2:$B$100))HTH

quot;Johnoppquot; wrote:

gt; Did anyone figure out how to do this? There is a posting from CommuterVet
gt; that ends with a problem that countif only took the last one.
gt;
gt; I have a sales forecast that is by day,month,year, and I want to subtotal by
gt; month or put it into a pivot table and have it aggregate by month, but I
gt; can't seem to be able to do it.

Here is what I have:=SUMPRODUCT((MONTH(G3:G73)=G3),(H3:H73))
I have dates in column G and the product amounts in column H. I am getting
a zero with no message. The date format in G3 is month/year under
quot;datevaluequot; as I had to convert a text date. Do I need to have a month
number only or a month? I got the file from Microsoft CRM.quot;Toppersquot; wrote:

gt; Assumes dates in column A and Sales in B then following will give total for
gt; March.
gt;
gt; Change 3 to a cell if you the month value stored in a cell
gt;
gt; =SUMPRODUCT(--(MONTH($A$2:$A$100)=3),--($B$2:$B$100))
gt;
gt; C2=3
gt;
gt; =SUMPRODUCT(--(MONTH($A$2:$A$100)=C2),--($B$2:$B$100))
gt;
gt;
gt; HTH
gt;
gt; quot;Johnoppquot; wrote:
gt;
gt; gt; Did anyone figure out how to do this? There is a posting from CommuterVet
gt; gt; that ends with a problem that countif only took the last one.
gt; gt;
gt; gt; I have a sales forecast that is by day,month,year, and I want to subtotal by
gt; gt; month or put it into a pivot table and have it aggregate by month, but I
gt; gt; can't seem to be able to do it.

Try:

=SUMPRODUCT(--(MONTH(G3:G73)=Month(G3)),--(H3:H73))quot;Johnoppquot; wrote:

gt; Here is what I have:=SUMPRODUCT((MONTH(G3:G73)=G3),(H3:H73))
gt; I have dates in column G and the product amounts in column H. I am getting
gt; a zero with no message. The date format in G3 is month/year under
gt; quot;datevaluequot; as I had to convert a text date. Do I need to have a month
gt; number only or a month? I got the file from Microsoft CRM.
gt;
gt;
gt; quot;Toppersquot; wrote:
gt;
gt; gt; Assumes dates in column A and Sales in B then following will give total for
gt; gt; March.
gt; gt;
gt; gt; Change 3 to a cell if you the month value stored in a cell
gt; gt;
gt; gt; =SUMPRODUCT(--(MONTH($A$2:$A$100)=3),--($B$2:$B$100))
gt; gt;
gt; gt; C2=3
gt; gt;
gt; gt; =SUMPRODUCT(--(MONTH($A$2:$A$100)=C2),--($B$2:$B$100))
gt; gt;
gt; gt;
gt; gt; HTH
gt; gt;
gt; gt; quot;Johnoppquot; wrote:
gt; gt;
gt; gt; gt; Did anyone figure out how to do this? There is a posting from CommuterVet
gt; gt; gt; that ends with a problem that countif only took the last one.
gt; gt; gt;
gt; gt; gt; I have a sales forecast that is by day,month,year, and I want to subtotal by
gt; gt; gt; month or put it into a pivot table and have it aggregate by month, but I
gt; gt; gt; can't seem to be able to do it.

That worked. Thank you so much. Your quick response was great.

quot;Toppersquot; wrote:

gt; Try:
gt;
gt; =SUMPRODUCT(--(MONTH(G3:G73)=Month(G3)),--(H3:H73))
gt;
gt;
gt; quot;Johnoppquot; wrote:
gt;
gt; gt; Here is what I have:=SUMPRODUCT((MONTH(G3:G73)=G3),(H3:H73))
gt; gt; I have dates in column G and the product amounts in column H. I am getting
gt; gt; a zero with no message. The date format in G3 is month/year under
gt; gt; quot;datevaluequot; as I had to convert a text date. Do I need to have a month
gt; gt; number only or a month? I got the file from Microsoft CRM.
gt; gt;
gt; gt;
gt; gt; quot;Toppersquot; wrote:
gt; gt;
gt; gt; gt; Assumes dates in column A and Sales in B then following will give total for
gt; gt; gt; March.
gt; gt; gt;
gt; gt; gt; Change 3 to a cell if you the month value stored in a cell
gt; gt; gt;
gt; gt; gt; =SUMPRODUCT(--(MONTH($A$2:$A$100)=3),--($B$2:$B$100))
gt; gt; gt;
gt; gt; gt; C2=3
gt; gt; gt;
gt; gt; gt; =SUMPRODUCT(--(MONTH($A$2:$A$100)=C2),--($B$2:$B$100))
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; HTH
gt; gt; gt;
gt; gt; gt; quot;Johnoppquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Did anyone figure out how to do this? There is a posting from CommuterVet
gt; gt; gt; gt; that ends with a problem that countif only took the last one.
gt; gt; gt; gt;
gt; gt; gt; gt; I have a sales forecast that is by day,month,year, and I want to subtotal by
gt; gt; gt; gt; month or put it into a pivot table and have it aggregate by month, but I
gt; gt; gt; gt; can't seem to be able to do it.


If you want to do the aggregation by month in the pivot table you could
just use the 'group by' pivot table functionality, or add an extra
column to the data as =text(lt;dategt;,quot;yyyy-mmquot;) then use that field
directly in the pivot table.

regards..--
steven1001
------------------------------------------------------------------------
steven1001's Profile: www.excelforum.com/member.php...oamp;userid=30757
View this thread: www.excelforum.com/showthread...hreadid=519145