Sum Index Match Months from Quarter/Year Input

I have a table in the following format:

I am looking trying to sum the intersect where input variables:

a) Q3 2023

b) Version 2

I have tried:

=SUMPRODUCT((A2:A13>=DATE(YEAR(F1),CHOOSE(MATCH(F1,{"Q1","Q2","Q3","Q4"},0)*3-2,1,4,7,10),1))*
  (A2:A13<=EOMONTH(DATE(YEAR(F1),CHOOSE(MATCH(F1,{"Q1","Q2","Q3","Q4"},0)*3,3,6,9,12),1),0))*
 (INDEX(B2:E13,0,MATCH(G1,B1:E1,0)))

This doesn’t work because of comparing text and dates. I am not opposed to using helper lookup tables to get the months for each quarter but i am stumped.


Output should be 450.

>Solution :

Try one of the following as I have commented above:

• Formula 1

=SUMPRODUCT(IFERROR(
  (A2:A13>=DATE(RIGHT(F1,4),CHOOSE(TOCOL(MATCH({"Q1*","Q2*","Q3*","Q4*"},F1,0)*3,3),1,4,7,10),1))*
  (A2:A13<=EOMONTH(DATE(RIGHT(F1,4),CHOOSE(TOCOL(MATCH({"Q1*","Q2*","Q3*","Q4*"},F1,0)*3,3),3,6,9,12),1),0))*
  (INDEX(B2:E13,0,MATCH(G1,B1:E1,0))),0))

Or,

• Formula 2

=SUMPRODUCT(IFERROR(
  (A2:A13>=DATE(RIGHT(F1,4),CHOOSE(MATCH(LEFT(F1,2),{"Q1","Q2","Q3","Q4"},0),1,4,7,10),1))*
  (A2:A13<=EOMONTH(DATE(RIGHT(F1,4),CHOOSE(MATCH(LEFT(F1,2),{"Q1","Q2","Q3","Q4"},0),3,6,9,12),1),0))*
  (INDEX(B2:E13,0,MATCH(G1,B1:E1,0))),0))

Or if you are using MS365 then can use the following formula:

• Formula used in cell G3

=SUM(
    TOCOL(
        (
            "Q" &
                ROUNDUP(
                    MONTH(
                        A2:A13
                    ) / 3,
                    0
                ) & " " &
                YEAR(A2:A13) =
                F1
        ) * (G1 = B1:E1) *
            (B2:E13),
        3
    )
)

Or,

=SUM(TOCOL(("Q"&INT((MONTH(A2:A13)-1)/3)+1=LEFT(F1,2))*(G1=B1:E1)*(B2:E13),3))