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

[Terry]

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.

 

[JW]

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.

 

[Roger Govier]

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.

 

[Darren Bartrup]

I'm making a couple of assumptions here:

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 D13):
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,

 

[Terry]

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

 

[Terry]

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]

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]

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)

 

[Darren Bartrup]

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.

 

[Roger Govier]

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