how to caculate a column which type on day?

noriko chan · May 5th, 2002, 08:10 AM

If column D is all type in different date..(for example, 3 may , 6 may, 4 may, 3 may etc), I would like to know the sum of 3 may, can I do it with excel?

Thanks...

eliW · May 5th, 2002, 08:47 AM

Quote:

On 2002-05-05 07:10, noriko chan wrote:
If column D is all type in different date..(for example, 3 may , 6 may, 4 may, 3 may etc), I would like to know the sum of , can I do it with excel?

Thanks...

Hi noriko chan,
If for example you have in D4 the date you want to count (e.g. "3 may")
1) Choose an empty cell
2) Enter the formula: =COUNTIF(D:D,D4)
and you will get the total number of "3 may" in this column.
Eli

Chris Davison · May 5th, 2002, 08:51 AM

=SUMIF(your date range,"03/05",your amounts range)

only if you're wanting to sum amounts which relate to 3 May

noriko chan · May 5th, 2002, 09:02 AM

thanks a lot

which is useful.
Moreover, if the whole column is full of day range, is it mean that I need to make 365 countif to caculate of different date?

eliW · May 5th, 2002, 09:24 AM

Quote:

On 2002-05-05 08:02, noriko chan wrote:
thanks a lot

which is useful.
Moreover, if the whole column is full of day range, is it mean that I need to make 365 countif to caculate of different date?

You can easily use a Pivot Table to get the sum of each date. Use the PivotTable wizard, put the Date into Row area and also in Data erea.
Eli

Chris Davison · May 5th, 2002, 09:26 AM

it would be mean on the guy who designed pivot tables....

Have you had a look at them ? these will "count" groups of dates (or groups of anything you want) where you have lots of data

a handy utility

Nimrod · May 5th, 2002, 09:50 AM

The Sumif solution can be taken one more step to allow it to be more dyanamic. Chris has hard coded the date into the formula:=SUMIF(your date range,"03/05",your amounts range).

Instead of hard coding the Date place a cell reference that will point to the cell where you can type in any date.

For example lets say Dates are in ColA , Amounts for dates in ColB and The Specific date you will type in is in Cell C1 Then formula would be:=SUMIF(A:A,C1,B:B)

[ This Message was edited by: Nimrod on 2002-05-05 08:51 ]

May 5th, 2002, 08:51 AM	#3
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	=SUMIF(your date range,"03/05",your amounts range) only if you're wanting to sum amounts which relate to 3 May __________________ :: Pharma Z - Family drugstore ::

May 5th, 2002, 09:26 AM	#6
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	it would be mean on the guy who designed pivot tables.... Have you had a look at them ? these will "count" groups of dates (or groups of anything you want) where you have lots of data a handy utility __________________ :: Pharma Z - Family drugstore ::

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 5th, 2002, 08:10 AM	#1
noriko chan New Member Join Date: May 2002 Posts: 2	If column D is all type in different date..(for example, 3 may , 6 may, 4 may, 3 may etc), I would like to know the sum of 3 may, can I do it with excel? Thanks...

May 5th, 2002, 09:02 AM	#4
noriko chan New Member Join Date: May 2002 Posts: 2	thanks a lot which is useful. Moreover, if the whole column is full of day range, is it mean that I need to make 365 countif to caculate of different date?

May 5th, 2002, 09:50 AM	#7
Nimrod MrExcel MVP Join Date: Apr 2002 Location: Vancouver BC , Canada Posts: 6,259	The Sumif solution can be taken one more step to allow it to be more dyanamic. Chris has hard coded the date into the formula:=SUMIF(your date range,"03/05",your amounts range). Instead of hard coding the Date place a cell reference that will point to the cell where you can type in any date. For example lets say Dates are in ColA , Amounts for dates in ColB and The Specific date you will type in is in Cell C1 Then formula would be:=SUMIF(A:A,C1,B:B) [ This Message was edited by: Nimrod on 2002-05-05 08:51 ]