Count occurrence of dates in a year

[rajkenya1]

Hi Guys

I am trying to calculate the number of times the dates fall in a year and then the total amount for that year.

For eg in the attached spread sheet i have two dates occurring in 2003 and the total amount is $480 in that year. How do i column C have a count of those dates in say 2003 and the in column D the total amount.

Please see attached. Thanks for your help in Advance.

 

[Smallman]

Hi rajkenya1

For the count of the date something like this

=SUMPRODUCT(--(YEAR($E$5:$E$46)=B5))

for the sum

=SUMPRODUCT((YEAR($E$5:$E$46)=B5)*($F$5:$F$46))

Take care

Smallman

 

[rajkenya1]

Thank you Genius

and here is was struggling with countifs and what not.

Thank you very very much.

 

[Smallman]

No worries Rajkenya1

You are most welcome.

Smallman

 

[Haseeb A]

Hello Rajkenya,

..or you can use COUNTIF & SUMIF

Count: =SUM(COUNTIF(E$5:E$46,">="&DATE(B5+{0,1},1,1))*{1,-1})

Amount: =SUM(SUMIF(E$5:E$46,">="&DATE(B5+{0,1},1,1),F$5:F$46)*{1,-1})

 

[rajkenya1]

Thanks Haseeb. I will use that the next time as i Smallman managed to resolve it.

But thank you for response as well.