help with match and count in sheet

[maryworthington]

Hello,

Have a sheet that I'm trying to do some calculations but failing miserably at it

	A	B
1	Acme	6/16/2016
2	Bat	6/20/2016
3	Acme	6/17/2016
4	Acme	6/26/2016
5	Charlie	6/25/2016
6	Bat	6/11/2016

<tbody>
</tbody>

Created another sheet with just column A without the duplicate names
trying to count each instance of A with date =>6/17/2016

	A	B
1	Acme	2
2	Bat	1
3	Charlie	1

<tbody>
</tbody>

=COUNTIF(Sheet1!B:B,">=6/17/2016") something along that but not sure how to do the search/match based on column A?

Thanks

Mary

 

[Weazel]

Because you're using 2 criteria you'd need to use the countifs function if you have excel 2007 or newer.

If not than you'd want to use the sumproduct.

Excel 2012

	A	B	C	D	E	F
1	Acme	6/16/2016		Acme	2	2
2	Bat	6/20/2016		Bat	1	1
3	Acme	6/17/2016		Charlie	1	1
4	Acme	6/26/2016
5	Charlie	6/25/2016
6	Bat	6/11/2016

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula E1 =COUNTIFS(Sheet1!$A$1:$A$6,D1,Sheet1!$B$1:$B$6,">=6/17/2016") F1 =SUMPRODUCT(($A$1:$A$6=D1)*($B$1:$B$6>="6/17/2016"+0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[maryworthington]

Thanks Weazel
The COUNTIFS works perfectly for this!