countifs and date range

[churmie]

Hi guys

I am a few issues putting a formula (that works) which can count the amount of cells with a value in it based on the date condition.

Here's my example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]FMMD[/TD]
[TD]CMMD[/TD]
[TD]FPA[/TD]
[/TR]
[TR]
[TD]8/3/2018[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28/3/2018[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16/4/2018[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/6/2018[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30/3/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31/8/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]20/7/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/3/2018[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need the results to pull in the table like this:

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Mar 18[/TD]
[TD]Apr 18[/TD]
[TD]May 18[/TD]
[TD]Jun 18[/TD]
[TD]Jul 18[/TD]
[TD]Aug 18[/TD]
[TD]Sep 18[/TD]
[/TR]
[TR]
[TD]FMMD[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CMMD[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FPA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Both these tables are in separate worksheets, so it right to assume the grid reference would start at A1 for both worksheets work sheet 1 and 2 respectively.

I am inexperienced in using date formula so getting it a bit wrapped my neck!

Many Thanks in advance as always!

Churmie

 

[steve the fish]

Re: Need help with a countifs and date range

Copy and pasting those two tables into A1 of a new sheet you can use this in B14:

=COUNTIFS($A$2:$A$9,">="&B$13,$A$2:$A$9,"<"&EOMONTH(B$13,0)+1,INDEX($B$2:$D$9,,MATCH($A14,$B$1:$D$1,0)),"Yes")

This has a caveat. Your dates in the 2nd table eg Mar 18 needs to be true dates ie 1st March 2018 formatted to look like Mar 18.

 

[churmie]

Re: Need help with a countifs and date range

That's excellent steve. You guys are great on here. One last question, where do I place the "" to keep the no value cells blank?

 

[steve the fish]

Re: Need help with a countifs and date range

You could do it a few ways. Heres one. Right click cells in your table. Format cells. Custom. In the type box:

0;-0;;@