Combining COUNTIFS and SUMPRODUCT

NikNak74

New Member
Joined
Jan 16, 2018
Messages
1
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))
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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>
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,916
Members
413,952
Latest member
JGer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top