Calculate the sum of a column if the month and year is correct.

[johansoder]

So yesterday I created a thread (http://www.excelforum.com/excel-prog...ng-column.html) that would clear a specific content if a date (or rather a day) matched the criteria. (I got helped by TMS and diberlee with a nice macro that helped me with that.)


Today I'm continuing with that document and need to sum the columns if the date in a row is the correct month and year.

Ex. Row 1 contains the date "yyyy-mm-dd" and row 2-5 contains empty cells or the value x. The x values are all random placed.

So, I want to sum all the "x" for februari 2014.

I'd rather use a formula here than a macro/VBA-code but anything will do. I have tried myself with =sumifs and =sumproduct but with no sucess.

See attached file for example and for my =sumproduct formula.

Thanks in advance
/Johan

[Jonmo1]

You were so close
=SUMPRODUCT((YEAR(B1:U1)=2014)*(MONTH(B1:U1=1)*(B2:U5="x")))
should be
=SUMPRODUCT((YEAR(B1:U1)=2014)*(MONTH(B1:U1)=1)*(B2:U5="x"))

[Mysore]

Hi Johansoder

Your formula is correct, except you missed to insert a closed bracket for the month context



Your Formula {SUMPRODUCT((YEAR(B1:U1)=2014)*(MONTH(B1:U1=1)*(B2:U5="x"))}

Revised Formula { SUMPRODUCT((YEAR(B1:U1)=2014)*(MONTH(B1:U1)=1)*(B2:U5="x"))}

Hope this helps

Cheers

[johansoder]

Wow I was close..

But thanks alot for the answers guys, will give rep. I also noticed I calculated for Januari instead of Februari (if anyone gets confused about the month=1 instead of =2).

[Mysore]

No worries. Please change the status to "Solved" if you are happy.

Thanks

[Jonmo1]

You're welcome.