CODEWITHSUNDEEP
exceldatesum
Martin Rezyne
Martin Rezyne

Reputation: 445

Excel Formula to sum value if date falls in particular month

I am trying to make a table in excel which calculate sum of some values if a date falls in a particular month.

Image to my spreadsheet

On the column A are dates, on column B some values, on column E are only listed numbers from 1 to 12 and in column F there are cells with =SUM(IF(MONTH($A$1:$A$10)=E11; $B$1:$B$10; 0)) formula.

I dont really know why on F9 the value is 0 even if checked with the debuger (F9 key) and saw that the result of the foruma is 22, which is the expected value. Also, I dont know why on F11 and F12 I receive an error: A value used in the formula is of the wrong data type

What can I do to solve this problems? Thank you

Upvotes: 1

Views: 4640

Answers (2)

pnuts
pnuts

Reputation: 59440

For versatility and perhaps speed in a large spreadsheet I think you might consider a PivotTable. So label your two columns (say Date and Value), insert a PT with Date for ROWS and Sum of Value for VALUES, then Group..., Years (avoids summation of the same month but different year) and Months.

Upvotes: 0

Martin Rezyne
Martin Rezyne

Reputation: 445

I solve the problem. I surounded the foruma with curly brackets ( { } ) and now works

Upvotes: 1

Related Questions