Using dates in Excel formulas

[ashish514]

I have long table which looks like this:[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]Order no.[/TD]
[TD]Value[/TD]
[TD]State[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

When I need to get a subtotal of the values state-wise I use the array forumula:

={Sum(if(D2:D200="Name of State",C2:C200,0))}

Now, when I use the same formula for a month-wise subtotal in following way,

={Sum(if(D2:D200="Name of Month",C2:C200,0))}

I am not getting the desired result. What is the right syntax to use for this purpose?

 

[paddydive]

hi Ashish,

dont you think the column should be "A" and not "D" in your formula. column d has state in it ?

 

[ashish514]

Ahh....yes of course, The second forumula is ={Sum(if(A2:A200="Name of Month",C2:C200,0))}.

Thanks paddydive for pointing it out.

 

[paddydive]

={Sum(if(month(A2:A200)=1,C2:C200,0))}.

This will be for january

 

[Norie]

Why not try a pivot table?

That would allow you to summarize the data by month/state/whatever.

 

[ashish514]

Thanks, that worked perfectly for me

 

[ashish514]

Why not try a pivot table?

That would allow you to summarize the data by month/state/whatever.

Not very well versed with pivot tables, but tried on your advice. It works too, only just a small glitch. How do I group the data monthwise? What I did is selected my table, dropped the date field to column labels and value field to values. The table is giving me date-wise sum of value. How do you get the month-wise sum of value??

 

[Norie]

Right click the date field and select Group...

Then you should get various options of how to group.

 

[ashish514]

Thanks a ton to both of you.