I'm currently using the =COUNTIFS() function to count the number of values that satisfy two conditions.
=COUNTIFS('2014'!$F:$F, B3, '2014'!$H:$H, A13)
However I no want to also include specific months as a condition, but the range of dates is in the format, 3/11/2014. I have looked up a few different ways that can count specific months from a list of dates using a combination of the =SUMPRODUCT() and MONTH formulas, but unable to find one that works in a COUNTIFS function to satisfy the original conditions also.
I even tried to think outside of the box by using wild cards,
=COUNTIFS('2014'!$F:$F, B3, '2014'!$H:$H, A13, '2014'!$A:$A, "*/11/2014")
Alas to no avail.
Does anyone know if there is a way in which to include counting specific months to a =COUNTIFS() function?
EDIT*
I have a temporary fix, thats not a sure fire solution, but I have created a helper column containing
=TEXT(A1, "DD/MM/YYYY")
And tied this in with my original wild card solution
=COUNTIFS('2014'!$F:$F, B3, '2014'!$H:$H, A13, '2014'!$I:$I, "*/11/2014")
however still after something a little less bodgy!
Since it seems that all your dates for excel are set to the format of dd/mm/yyyy this formula should work:
=COUNTIFS('2014'!$F:$F, B3, '2014'!$H:$H, A13, '2014'!$A:$A, ">="&EOMONTH(DATE(2014,10,1),0)+1,'2014'!$A:$A,"<"&EOMONTH(DATE(2014,11,1),0)+1)
This basically checks the column against the span of the entire month. All dates greater or equal to the start of the desired month, and all dates less than the start of the next month.
To change the month:
=COUNTIFS('2014'!$F:$F, B3, '2014'!$H:$H, A13, '2014'!$A:$A, ">="&EOMONTH(DATE(2014,10,1),0)+1,'2014'!$A:$A,"<"&EOMONTH(DATE(2014,11,1),0)+1)
^^ change 10 to one month prior ^^ change 11 to the month you want.
For example for January you might incorporate something like:
=COUNTIFS(A:A,">"&41639,A:A,"<"&41650)
assuming your dates are numeric rather than strings.
If the month you are testing against is numerical format (January is 1, Feb is 2, etc) you could use the following:
=SUMPRODUCT(('2014'!$F:$F= B3)*('2014'!$H:$H= A13)*(Month('2014'!$A:$A) = 5)*1)
That will look for May values, for instance.
How this works:
SUMPRODUCT works by testing conditions across a range of cells. For each row, in our case, we test if F holds B13's value, H holds A13's data, and the Month of A is 5. Each one of those tests will return a TRUE or a FALSE. A TRUE is also a 1 and a False is also a 0, so testing a rows will look like:
Row 1: TRUE * TRUE * TRUE * 1 = 1
Row 2: FALSE * TRUE * TRUE * 1 = 0
Row 3: TRUE * TRUE * FALSE * 1 = 0
Row 4: TRUE * TRUE * TRUE * 1 = 1
--------------------------------------
TOTAL: 2
Each condition is in it's own paranthetical statement and is multiplied by the other conditions so that it will result in a 1 or 0, which SUMPRODUCT will add up when it's done testing every row in your range.
