SUMIFS Using months

[MTC2016]

Hi all,


Like always, this is probably a nice easy one and yet again I am struggling to see "the wood for the trees" as it were. I have attached a test book which outlines what I am looking for but basically I have data that I need to count based on the type of test but also based on the month. I am sure it is a simple SUMIFS function but I just cant seem to get it to work.

Have a look, see what you think, I am happy to assist where I can if you need any more info.

Thanks again.

[PaulM100]

Try this:
=SUM(--(MONTH($B$11:$Q$11)=B$3),--($B$12:$Q$12=$A4)*$B$13:$Q$13)

[MTC2016]

Hi Paul, many thanks for the response. It appears to work on the test book but when I try and use the formula in my official workbook Im getting an error but cant seem to put my finger on why?
Unfortunately I cant share that workbook due to data protection.

I dont suppose you, or anyone else has an alternative formula I can perhaps try?

[Bo_Ry]

Please try at B4 copy across and down

=SUMIFS($13:$13,$12:$12,$A4,$11:$11,">=1"&B$3,$11:$11,"<="&EOMONTH(1&B$3,0))

or
=SUMIFS($B$13:$Q$13,$B$12:$Q$12,$A4,$B$11:$Q$11,">=1"&B$3,$B$11:$Q$11,"<="&EOMONTH(1&B$3,0))

If you have specific year , please add &year after B$3
Eg B$3&2019

[CARACALLA]

B4=IF(B$3<>"",SUMPRODUCT(($B$12:$Q$12=$A4)*(MONTH($B$11:$Q$11)=MONTH(B$3&0))*($B$13:$Q$13)),"") copy across and down