Combining COUNTIFS and SUMPRODUCT

[NikNak74]

Hi

I want to show the number of times each team member has created a certain type of document by month.

ie Nigel created 3 an 'always on' documents in the month of Jan

So I'm looking for a column that shows its Nigel, a column that shows the document is 'always on' and a column that shows the date is some time in January.

I can manage to do the Countifs and the sumproduct seperately, but not together. This is what I have so far but its not working. Any help much appreciated.

COUNTIFS('Step3 Comms Prioritisation'!$D$149:$D$250,<wbr style="font-family: arial, sans-serif; font-size: 12.8px;">"Always on (lights out)",'Step3 Comms Prioritisation'!$M$149:$M$250,<wbr style="font-family: arial, sans-serif; font-size: 12.8px;">"Nigel")AND =SUMPRODUCT(--(MONTH('Step3 Comms Prioritisation'!Y149:Y250)=7))

 

[barry houdini]

Hello NikNak74, welcome to MrExcel

You can either use COUNTIFS with specific start and end dates, e.g. for July 2017

=COUNTIFS('Step3 Comms Prioritisation'!$D$149:$D$250,"Always on (lights out)",'Step3 Comms Prioritisation'!$M$149:$M$250,"Nigel",'Step3 Comms Prioritisation'!Y149:Y250,">="&DATE(2017,7,1),'Step3 Comms Prioritisation'!Y149:Y250,"<"&DATE(2017,8,1))

or use SUMPRODUCT like this for July in any year:

=SUMPRODUCT(('Step3 Comms Prioritisation'!$D$149:$D$250="Always on (lights out)")*('Step3 Comms Prioritisation'!$M$149:$M$250="Nigel")*(MONTH('Step3 Comms Prioritisation'!Y149:Y250)=7))​

​

​

<strike></strike>