Dynamic CountIFS Formula

Question

I'm using the below formula to count an particular type of event category on a particular date.

=COUNTIFS($A:$A,"1-Mar-2016",$B:$B,"Carpentary")

To make it more dynamic, I'm looking for a solution where I can replace the specific Date "1-Mar-2016" with day of the month "1" to obtain the result. Please refer below. However this does not work.

=COUNTIFS($A:$A,"1",$B:$B,"Carpentary")

Can anyone please advise how to resolve this? Any sort of suggestions are welcome.

Table:

Date        Category                    1-Mar-16    2-Mar-16
1-Mar-16    Carpentary      Carpentary  2           0
1-Mar-16    Carpentary      Plumbing    2           1
1-Mar-16    Plumbing        Electrical  0           2
1-Mar-16    Plumbing                
2-Mar-16    Plumbing                
2-Mar-16    Electrical              
2-Mar-16    Electrical  

Answer 1

If you are going to go to the trouble of providing column and row header labels, then use them in your formula.

In E2 as a standard formula,

=COUNTIFS($A:$A, E$1,$B:$B, $D2)

Fill right and down as necessary.

        

Answer 2

for this case i'd rather not think of complicated formula.

i'd suggest a helper column for formula =Day([Date]) and use it for the COUNTIFS formula.