Can I use Sumproduct arrays and check two separate criteria counting TRUE statements?

Question

I have a table that I'm referencing to count dates that fall within a certain criteria (Feb 2024, Aug 2022, etc.) based on a dropdown box. Currently I am counting this via SUMPRODUCT, checking the text from a column matches said criteria, converting any 'TRUE' statements to a '1' using --, and totalling from there.

I would however like to add a second condition to compare to on this array, and I am not sure the next steps to take to achieve my desired output.

An image of a basic concept table:

The above image is a boiled down table to demonstrate the fields I am comparing against, and below is the formula I currently have in cell AD17.

=SUMPRODUCT(--(TEXT(Table2[Field 1],"MMM YYYY")=TEXT($AE$17,"mmm")&TEXT($AE$17," yyyy")))

It displays 2, which is the anticipated outcome. However, my next step would be only counting if the Field 2 column of that same row equalled "emp".

I have tried using two separate SUMPRODUCT functions and subtracting, but due to the nature of my actual table the output would not be accurate. I have tried multiple AND functions, but cannot get the syntax correct in order to have an actual output.

In the example below, I would want the output to be '1' (one row where Field1 equates to Feb 2024, and where Field2 equates to 'emp')

Answer 1

You can combine multiple AND-conditions with a * like this

=SUMPRODUCT(
              (MONTH(Table2[Field1])=MONTH($AE$17))*
              (YEAR(Table2[Field1])=YEAR($AE$17))*
              (Table2[Field2]="emp")
)

Note: I am expecting a true date in $AE$17 (e.g. 01/02/2024) - then it is possible to use MONTH and YEAR function instead of text functions.