count all duplicate values depending on criteria

[shockmox]

Hello there

I'm a Excel newbie so i would really appreciate your help

I have a data sheet with the following data

Date User Type 1 2 3 4 5 6 7

14-01-2018 Brandon Batch No No No No No No No
14-03-2018 Danny Batch No No No No No No No
14-01-2018 Derek Incident Yes No No No No Yes Yes
14-02-2018 Danny Batch No No Yes Yes No No No
14-03-2018 Franco OMi No No No No No No No
14-01-2018 Brandon Batch No No No No Yes Yes No
14-03-2018 Derek OMi No No Yes No No No No

I've tried multiple formulas using =SUMPRODUCT & =COUNTIFS but am unable to get the right results

I would like to get a count of all the No values by month per user and per type
Basically if date = January & User = Danny & Type = Batch - How Many "No" exist in all columns and rows that match that criteria.

Thank you in advance.

 

[DRSteele]

Welcome to the forum.

Your sample data contains no entries for Danny that meet the criteria so I used Brandon.

	A	B	C	D	E	F	G	H	I	J
Brandon	Batch	No	No	No	No	No	No	No
Danny	Batch	No	No	No	No	No	No	No
Derek	Incident	Yes	No	No	No	No	Yes	Yes
Danny	Batch	No	No	Yes	Yes	No	No	No
Franco	OMi	No	No	No	No	No	No	No
Brandon	Batch	No	No	No	No	Yes	Yes	No
Derek	OMi	No	No	Yes	No	No	No	No
Month
User
Type
Condition
Count

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFF2CC"]Date[/TD]
[TD="bgcolor: #FFF2CC"]User[/TD]
[TD="bgcolor: #FFF2CC"]Type[/TD]
[TD="bgcolor: #FFF2CC, align: right"]1[/TD]
[TD="bgcolor: #FFF2CC, align: right"]2[/TD]
[TD="bgcolor: #FFF2CC, align: right"]3[/TD]
[TD="bgcolor: #FFF2CC, align: right"]4[/TD]
[TD="bgcolor: #FFF2CC, align: right"]5[/TD]
[TD="bgcolor: #FFF2CC, align: right"]6[/TD]
[TD="bgcolor: #FFF2CC, align: right"]7[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1/14/18[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3/14/18[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1/14/18[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2/14/18[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]3/14/18[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1/14/18[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]3/14/18[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="bgcolor: #F8CBAD, align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="bgcolor: #F8CBAD"]Brandon[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="bgcolor: #F8CBAD"]Batch[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="bgcolor: #F8CBAD"]No[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="bgcolor: #E2EFDA, align: right"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet41​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B15[/TH]
[TD="align: left"]=SUMPRODUCT((MONTH(A2:A8)=B11)*(B2:B8=B12)*(C2:C8=B13)*(D2:J8=B14))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[shockmox]

Thank you very much, it worked like a charm...

 

[DRSteele]

You're welcome.