How can i sum up numbers which are counted on a date.
 

Hi, I am working with a sheet that has data based on the month and number of
items counted. I want to know the number of items counted per month, how can
i make a formula for this.

How can i sum up numbers which are counted on a date.
 

Need to see the structure of your data, but......
This will count the number of times "January" occurs in column A.
=COUNTIF(A:A,"January")

This will sum the data in column B where column A equals "January".
=SUMIF(A:A,"January",B:B)

Tweak as needed.
Terry wrote:
Hi, I am working with a sheet that has data based on the month and number of
items counted. I want to know the number of items counted per month, how can
i make a formula for this.

How can i sum up numbers which are counted on a date.
 

Hi Terry

Assuming your Months are in A and Items in B
=SUMIF(A:A,"October",B:B)

This assumes that column A contains text of Month names. Of it is month
number, then
=SUMIF(A:A,10,B:B)
for October etc.
--
Regards
Roger Govier



"Terry" wrote in message
...
Hi, I am working with a sheet that has data based on the month and number
of
items counted. I want to know the number of items counted per month, how
can
i make a formula for this.

How can i sum up numbers which are counted on a date.
 

I'm making a couple of assumptions he

If you've got details of your items in column B, with the relevant dates in
column A
(cells A1:B7):
01/02/2007 3
01/02/2007 8
01/02/2007 2
01/05/2007 1
01/05/2007 5
01/06/2007 3
01/06/2007 6

Then in column D you've got a list of unique months
(cells D1:D3):
01/02/2007
01/05/2007
01/06/2007

You could use the following formula in cell E1 to give you a sum of items
appearing in each month:
{=SUM(IF(D1=$A$1:$A$7,$B$1:$B$7))}

This has to be entered as an array formula - press Ctrl~Shift~Enter instead
of just Enter when completing the formula. Drag this down from E1 to E3 for
your totals on each month.

Regards,

How can i sum up numbers which are counted on a date.
 

Hi everyone thanks for the help, I should have been more clear on what I was
actually doing.
Darren your comment was more like the actuall workbook.
Column A on Sheet 1 is dates ie 14-Jan-2007 Column B is the Number I need to
count.
This is then summarized by dates on sheet 3 and i need a seperate formula
for each month then to count the number of items within each date.

So how would i find values within the month rather then based on the title
ie ' October ' For example

"Darren Bartrup" wrote:

I'm making a couple of assumptions he

If you've got details of your items in column B, with the relevant dates in
column A
(cells A1:B7):
01/02/2007 3
01/02/2007 8
01/02/2007 2
01/05/2007 1
01/05/2007 5
01/06/2007 3
01/06/2007 6

Then in column D you've got a list of unique months
(cells D1:D3):
01/02/2007
01/05/2007
01/06/2007

You could use the following formula in cell E1 to give you a sum of items
appearing in each month:
{=SUM(IF(D1=$A$1:$A$7,$B$1:$B$7))}

This has to be entered as an array formula - press Ctrl~Shift~Enter instead
of just Enter when completing the formula. Drag this down from E1 to E3 for
your totals on each month.

Regards,

How can i sum up numbers which are counted on a date.
 

This is the current attempt to count by date for january but its not working
any ideas ?
=SUMIF('Data Input'!A5:A1294,2,'Data Input'!B5:B1294)

"Terry" wrote:

Hi, I am working with a sheet that has data based on the month and number of
items counted. I want to know the number of items counted per month, how can
i make a formula for this.

How can i sum up numbers which are counted on a date.
 

Just one more thing, when i say it is summarized by dates i mean on sheet 3 i
have a list Jan
Feb
Mar ect... so then for each month in column b i need to know how
many items were counted on sheet 1

"Terry" wrote:

Hi everyone thanks for the help, I should have been more clear on what I was
actually doing.
Darren your comment was more like the actuall workbook.
Column A on Sheet 1 is dates ie 14-Jan-2007 Column B is the Number I need to
count.
This is then summarized by dates on sheet 3 and i need a seperate formula
for each month then to count the number of items within each date.

So how would i find values within the month rather then based on the title
ie ' October ' For example

"Darren Bartrup" wrote:

I'm making a couple of assumptions he

If you've got details of your items in column B, with the relevant dates in
column A
(cells A1:B7):
01/02/2007 3
01/02/2007 8
01/02/2007 2
01/05/2007 1
01/05/2007 5
01/06/2007 3
01/06/2007 6

Then in column D you've got a list of unique months
(cells D1:D3):
01/02/2007
01/05/2007
01/06/2007

You could use the following formula in cell E1 to give you a sum of items
appearing in each month:
{=SUM(IF(D1=$A$1:$A$7,$B$1:$B$7))}

This has to be entered as an array formula - press Ctrl~Shift~Enter instead
of just Enter when completing the formula. Drag this down from E1 to E3 for
your totals on each month.

Regards,

How can i sum up numbers which are counted on a date.
 

I THINK THE CRITERIA PART IS MY PROBLEM !!!
=SUMIF('Data Input'!$A$5:A1294,"(--('Data
Input'!$A$5:$A$1294=DATE(2007,1,1)),--('Data
Input'!$A$5:$A$1294<=DATE(2007,1,30)))",'Data Input'!$B$5:B1294)

"Terry" wrote:

Hi, I am working with a sheet that has data based on the month and number of
items counted. I want to know the number of items counted per month, how can
i make a formula for this.

How can i sum up numbers which are counted on a date.
 

ok, so if your Sheet1 contains in columns A1:B5
14/02/2007 3
15/02/2007 2
01/03/2007 1
03/05/2007 5
02/03/2007 3

and Sheet3 contains in range A1:A3
01/02/2007
01/03/2007
01/05/2007

then the formula in range B1:B3 should be:
{=SUM(IF(MONTH(A1)=MONTH(Sheet1!$A$1:$A$5),Sheet1! $B$1:$B$5))}

(again an array formula). I tried this using SUMIF, but it didn't like it.
:(

How can i sum up numbers which are counted on a date.
 

Hi Terry

Try entering in B1 of Sheet3
=SUMPRODUCT(--(TEXT(Sheet1!$A$1:$A$100,"mmm")=A1),--(Sheet1!$B$1:$$B100))
This assumes that Jan is in A1 of Sheet3
Copy down to B12

--
Regards
Roger Govier



"Terry" wrote in message
...
Just one more thing, when i say it is summarized by dates i mean on sheet
3 i
have a list Jan
Feb
Mar ect... so then for each month in column b i need to know how
many items were counted on sheet 1

"Terry" wrote:

Hi everyone thanks for the help, I should have been more clear on what I
was
actually doing.
Darren your comment was more like the actuall workbook.
Column A on Sheet 1 is dates ie 14-Jan-2007 Column B is the Number I need
to
count.
This is then summarized by dates on sheet 3 and i need a seperate formula
for each month then to count the number of items within each date.

So how would i find values within the month rather then based on the
title
ie ' October ' For example

"Darren Bartrup" wrote:

I'm making a couple of assumptions he

If you've got details of your items in column B, with the relevant
dates in
column A
(cells A1:B7):
01/02/2007 3
01/02/2007 8
01/02/2007 2
01/05/2007 1
01/05/2007 5
01/06/2007 3
01/06/2007 6

Then in column D you've got a list of unique months
(cells D1:D3):
01/02/2007
01/05/2007
01/06/2007

You could use the following formula in cell E1 to give you a sum of
items
appearing in each month:
{=SUM(IF(D1=$A$1:$A$7,$B$1:$B$7))}

This has to be entered as an array formula - press Ctrl~Shift~Enter
instead
of just Enter when completing the formula. Drag this down from E1 to
E3 for
your totals on each month.

Regards,