Formula for couting cells that meet 2 criteria and fall within a date range

[segelson]

I am trying to count the number of cells that fall within a date range and meet 2 different criteria within that date range. Example below


Column A Column B
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Construction Start[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]5/1/2017[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]8/15/2018[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]9/1/2018[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]6/4/2017[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]7/30/2018[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]5/15/2017[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]8/3/2018[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]8/1/2018[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]2/1/2017[/TD]
[TD]N[/TD]
[/TR]
</tbody>[/TABLE]


In the above example, I would need to know how many cells within this quarter (7/1/18 - 9/30/18) meet the criteria of Status = "A" and "N" So the answer would be 4 in the above example

Any help would be greatly appreciated =) Thank you in advance.

 

[Joe4]

Welcome to the Board!

Here is one way:

=COUNTIFS(A2:A10,">=7/1/2018",A2:A10,"<=9/30/2018",B2:B10,"A") + COUNTIFS(A2:A10,">=7/1/2018",A2:A10,"<=9/30/2018",B2:B10,"N")

 

[segelson]

Thanks Joe4, this is much simplier then I was trying to make it out to be.

 

[MARZIOTULLIO]

Hi

try

=COUNTIFS(A2:A10,">=7/1/2018",A2:A10,"<=9/30/2018",B2:B10,"<>P")

 

[segelson]

Hi

try

=COUNTIFS(A2:A10,">=7/1/2018",A2:A10,"<=9/30/2018",B2:B10,"<>P")

Thank you Marziotullio, this is an even simpler solution =)

 

[Joe4]

Thank you Marziotullio, this is an even simpler solution

Yes, if you only ever have those three options (A, N, P), that will work and be shorter.
I just didn't want to make that assumption based on a small sample.

 

[segelson]

Yes, if you only ever have those three options (A, N, P), that will work and be shorter.
I just didn't want to make that assumption based on a small sample.[/COLOR]

So I am running into an error and I am assuming it is because I am not inserting the correct () The problem is coming from I am pulling my data from another workbook.


=COUNTIFS('[Tri Market Sector Adds MasterTracker_6.12.2017.xlsm]Tri Market Tracker'!$W13:$W529,">=7/1/2018”,'[TriMarket Sector Adds Master Tracker_6.12.2017.xlsm]Tri Market Tracker'!$W13:$W529,"<=9/30/2018”,'[Tri Market Sector Adds Master Tracker_6.12.2017.xlsm]Tri Market Tracker'!$X13:$X529,”<>P")

In the above, my dates are coming from the W column and the Status is coming from the X column.

 

[Joe4]

What is the exact error message you are getting?

Did you copy and paste that formula, and do it you type it in manually?
I asked because some of your double-quotes are slanted, and Excel does not like slanted ones. They should all be perfectly vertical, i.e.

[COLOR=#000000][FONT=Calibri]">=7/1/2018”[/FONT][/COLOR]

Beginning one good, ending one bad.

 

[segelson]

What is the exact error message you are getting?

Did you copy and paste that formula, and do it you type it in manually?
I asked because some of your double-quotes are slanted, and Excel does not like slanted ones. They should all be perfectly vertical, i.e.

[COLOR=#000000][FONT=Calibri]">=7/1/2018”[/FONT][/COLOR]

Beginning one good, ending one bad.

I did and that appeared to be my issue, however, I a can get the formula to work if I hard code the date ranges into it, but if I try to reference the same dates on another sheet I get a returned value of 0 (when in my test it should have returned a value of 36) is the formatting of the cell causing the value to be off?


In the above example i have dates stored in another sheet

[TABLE="width: 285"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]Beginning [/TD]
[TD]End[/TD]
[/TR]
[TR]
[TD]Quarter 1[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]3/31/2018[/TD]
[/TR]
[TR]
[TD]Quarter 2[/TD]
[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]6/30/2018[/TD]
[/TR]
[TR]
[TD]Quarter 3[/TD]
[TD="align: right"]7/1/2018[/TD]
[TD="align: right"]9/30/2018[/TD]
[/TR]
[TR]
[TD]Quarter 4[/TD]
[TD="align: right"]10/1/2018[/TD]
[TD="align: right"]12/31/2018[/TD]
[/TR]
</tbody>[/TABLE]


When I use the below formula I get a value of 0,

=COUNTIFS('[Tri Market Sector Adds Master Tracker_6.12.2017.xlsm]Tri Market Tracker'!$S$13:$S$529,">=Dates!B4",'[Tri Market Sector Adds Master Tracker_6.12.2017.xlsm]Tri Market Tracker'!$S$13:$S$529,"<=Dates!C4",'[Tri Market Sector Adds Master Tracker_6.12.2017.xlsm]Tri Market Tracker'!$T$13:$T$529,"<>P")

but if I use the below formula I get the correct value of 36

=COUNTIFS('[Tri Market Sector Adds Master Tracker_6.12.2017.xlsm]Tri Market Tracker'!$S$13:$S$529,">=7/1/2018",'[Tri Market Sector Adds Master Tracker_6.12.2017.xlsm]Tri Market Tracker'!$S$13:$S$529,"<=9/30/2018",'[Tri Market Sector Adds Master Tracker_6.12.2017.xlsm]Tri Market Tracker'!$T$13:$T$529,"<>P")

 

[Joe4]

The issue is that anything between double-quotes is literal text.
So:

[COLOR=#333333]">=Dates!B4"[/COLOR]

Is not "greater than or equal to the value in cell B4 on the dates sheet"; rather it reads "greater than or equal to the word Dates!B4", which makes no sense.

You need to write it like this:

[COLOR=#333333]">=" & Dates!B4[/COLOR]

so that all range references are outside the double-quotes and combined with the other part with the concatenation symbol (&).
So make those changes in all the places, and it should work.