i want to find count of text for a date range as criteria 1 and criteria 2 in the array

Question

I want to count number of values (N/D) in the array (below:table: list) for criteria 1 is date range( from date and through Date) and criteria 2 is Shift A, b acros ( as shown in below table-output). I want to fill column D/N with how many times D/N occur for a date range and shift A,B,C,D?

output

From Date   Through Date    Shift   D/N
25-May-19   26-May-19         A      ?
25-May-19   26-May-19         B      ?

Table- list

Dates       A   B   C   D
25-May-19   N   D   -   -
26-May-19   N   D   -   -
27-May-19   -   D   N   -



INDEX(A:E,MATCH(H7:I7,A:E,0),MATCH(J7,A:E,0))

Value -?

Answer 1

Part of the problem you may be having is dates. Make sure your dates are excel dates and not string/text that looks like a date. Simply changing the formatting of a cell does not make it a date, it simply tells excel how to try and display the information in a cell.

Dates in excel are stored as integers and they represent the days since 1900/1/1 with that date being day 1. One of the easiest ways to test if a cell contains a date or a string is:

=ISTEXT(A1)
or
=ISNUMBER(A1)

Where A1 is the cell with the date to be tested.

If it returns TRUE for the first formula it is a string/text. FALSE means it is a number. The opposite TRUE and FALSE results apply for the second formula.

In your formula's when you have something between quotes "", it will be interpreted as a string. SO something like "<=19/05/26" mean its looking for a string less than that, not a date less than that. For doing a date comparison, either concatenate the text comparison with with a cell containing a date to compare to "<="&B2 where B2 has the date or if you want to hard code it use something like "<="&Date(19,05,26)

In order to make the following solution work, your dates all need to be stored as a number. AKA Excel serial date format.

Based on the data being layed out as per the image below, you can use COUNTIFS, INDEX, and MATCH to get the date your are looking for. I recommend find your count of D and N separately and adding them together after for a total. However if you want it in a single cell formula solution it can be achieved as well as demonstrated by the results in column N. however the formula starts to get long and can be difficult potentially read/maintain at a later date.

The core of the solution will be the COUNTIFS functions. The format of the COUNTIFS function is as follows:

COUNTIFS(Range to count 1, Criteria 1, Range to count 2, Criteria 2,...,Range to count n, Criteria n)

Let start building your formula one criteria at a time. The first Criteria will be all dates that are greater than or equal to the from date. If you only want the dates after the from date, drop the equal sign or the criteria.

=COUNTIFS($A$2:$A$4,">="&$G2,

Note the $ to lock the cell references. This is done so that when the formula gets copied, the column or row references beside the $ does not change.

Then second criteria is similar to the first except you want to grab all the dates less than or equal to the through date. Again include/drop the equal sign to suit your needs.

=COUNTIFS($A$2:$A$4,">="&$G2,$A$2:$A$4,"<="&$H2,

The next criteria will be to get all the cells that match D or N the column header. Lets just focus on D for now. The tricky part is to define which column to look in. For now lets call the column to look in XXX which will make the formula become:

=COUNTIFS($A$2:$A$4,">="&$G2,$A$2:$A$4,"<="&$H2,XXX,J$1)
OR
=COUNTIFS($A$2:$A$4,">="&$G2,$A$2:$A$4,"<="&$H2,XXX,"="&J$1)

NOTE: both formulas are the same.  When no comparison operator is provided
      it is taken as "=" by default. 

Now in order to define XXX, INDEX and MATCH will be your friends. An important side note about INDEX is that it does not directly return the value of a cell but instead returns a cell address which in turn pulls a cell value. The basic format of INDEX is:

INDEX(Range to look in, Range's ROW to look in, Range's COLUMN to look in)

That is for a 2 dimensional range. If your range is 1 dimensional, either just a column or just a row, then only the second argument "Range's Row..." need to be provided and it represents how far down the list to go.

What gets interesting about a 2D INDEX is that when 0 is provided for ROW to look in or the Column to look in, instead of throwing an error, it instead returns all rows or columns. THIS IS IMPORTANT because you want all rows of just 1 specific column. That mean your INDEX formula is going to start to look like:

INDEX($B$2:$E$4,0,SPECIFIC COLUMN NUMBER)

So now you need to find a specific column number. That is where MATCH will be your friend. MATCH takes the following format:

MATCH(Value to find, 1D range to look in, what type of search)

You already know you are going to try and match your shift column so that will be your look up value, and the range to look in will be your column headers. The type of search you will want in this case is an exact match which is represented by 0. That means your MATCH formula will look like:

MATCH($I2,$B$1:$E$1,0)

Now to combine the various pieces, throw the MATCH formula into your INDEX and replace the "SPECIFIC COLUMN...". Your INDEX will now look like:

INDEX($B$2:$E$4,0,MATCH($I2,$B$1:$E$1,0))

And the formula above can now replace the XXX in your COUNTIFS formula and you will get:

=COUNTIFS($A$2:$A$4,">="&$G2,$A$2:$A$4,"<="&$H2,INDEX($B$2:$E$4,0,MATCH($I2,$B$1:$E$1,0)),J$1)

Place the above formula in J2 and copy the cell down and to the right.

In L2 use one of the two formulas to get the total of D and N in the date range:

=SUM(J2:K2)
OR
=J2+K2

Now to get your formula all in one cell, look at the second formula above. You can simply go to the contents of cell J2 and copy the entire formula. Then edit cell L2 and replace the cell reference for for J2 with the copied formula. Repeat the process by copy formula in K2 and replacing the reference to K2 in L2. You will wind up with a formula that looks like:

=COUNTIFS($A$2:$A$4,">="&$G2,$A$2:$A$4,"<="&$H2,INDEX($B$2:$E$4,0,MATCH($I2,$B$1:$E$1,0)),J$1)+COUNTIFS($A$2:$A$4,">="&$G2,$A$2:$A$4,"<="&$H2,INDEX($B$2:$E$4,0,MATCH($I2,$B$1:$E$1,0)),K$1)

Much longer and harder to read which is why I recommend breaking the formula down into its parts for D and N separately.

Now as an alternate method you could use SUMPRODUCT and get into array operations. Your SUMPRODUCT formula to place in I2 and copy down and right could be:

=SUMPRODUCT(($A$2:$A$4>=$G2)*($A$2:$A$4<=$H2)*(INDEX($B$2:$E$4,0,MATCH($I2,$B$1:$E$1,0))=J$1))