Countifs to combine multiple variables

[karin23]

I have dates in column W and data in both Column AC and AD. I'd like to count how many "A"s are in both AC and AD prior to Dec 11. I know I'm missing something but can someone take a look at this for me?

=COUNTIFS(Data!W2:W53651,"<171211",Data!AC2:AC53651,"A",+Data!AD2:AD53651,"A")

 

[AhoyNC]

Try

=SUMPRODUCT(--(Data!W2:W53651< DATEVALUE("12/11/17")),--((Data!AC2:AC53651="A")+(Data!AD2:AD53651="A")))

without the space after the < (need to add that so the forum would not think it was HTML code).

 

[karin23]

It came back with a result of True, not a total count.

Try

=SUMPRODUCT(--(Data!W2:W53651< DATEVALUE("12/11/17")),--((Data!AC2:AC53651="A")+(Data!AD2:AD53651="A")))

without the space after the < (need to add that so the forum would not think it was HTML code).

 

[AhoyNC]

It's returning a count for me.

Excel Workbook
	W	X	AB	AC	AD
1	3
2	12/1/2017			A	g
3	12/3/2017			A	l
4	12/15/2017			A	A
5	11/30/2017			f	h
6	11/1/2017			g	A
Sheet

 

[karin23]

Thanks. That worked when I changed the dates to 4 digit year vs 2 digit.

How would you do it if it was a date range such as Dec 16-20?

It's returning a count for me.

	W	X	AB	AC	AD
1	3
2	12/1/2017			A	g
3	12/3/2017			A	l
4	12/15/2017			A	A
5	11/30/2017			f	h
6	11/1/2017			g	A

<colgroup><col style="width:30px; "><col style="width:76px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula W1 =SUMPRODUCT(--(Data!W2:W53651< DATEVALUE("12/11/17")),--((Data!AC2:AC53651="A")+(Data!AD2:AD53651="A"))) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[AhoyNC]

Something like this, you could also put your date range into cells to make it easy to change (example in cell T3 below).

Excel Workbook
	S	T	U	V	W	X	AB	AC	AD
1	Start Date	12/16/2017			3
2	End Date	12/20/2017			12/19/2017			A	g
3		3			12/3/2017			A	l
4					12/18/2017			A	A
5					11/30/2017			f	h
6					12/11/2017			A	A
Sheet

 

[karin23]

Thank you so much! Super helpful. I learn something new all the time on this board.

Karin

Something like this, you could also put your date range into cells to make it easy to change (example in cell T3 below).

	S	T	U	V	W	X	AB	AC	AD
1	Start Date	12/16/2017			3
2	End Date	12/20/2017			12/19/2017			A	g
3		3			12/3/2017			A	l
4					12/18/2017			A	A
5					11/30/2017			f	h
6					12/11/2017			A	A

<colgroup><col style="width:30px; "><col style="width:87px;"><col style="width:87px;"><col style="width:46px;"><col style="width:34px;"><col style="width:76px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula W1 =SUMPRODUCT(--(Data!W2:W53651>DATEVALUE("12/16/17")),--(Data!W2:W53651< DATEVALUE("12/20/17")),--((Data!AC2:AC53651="A")+(Data!AD2:AD53651="A"))) T3 =SUMPRODUCT(--(Data!W2:W53651>$T$1),--(Data!W2:W53651< $T$2),--((Data!AC2:AC53651="A")+(Data!AD2:AD53651="A"))) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[AhoyNC]

You're welcome. Thanks for the feedback.