excel-formulalogicarray-formulas

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


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:

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')


Solution

  • 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.