Find dates with matching month and year on a row -> return value 2 columns to the right

Originally Posted by :) Sixthsense :)

In B2 cell – Non Array Formula

This formula is worked for me

Formula:

=SUMPRODUCT(--(Case!$C$2:$C$23="x")*--(Case!$B$2:$B$23=$A2)*--(TEXT(Case!$F$2:$BC$23,"MMYYYY")=TEXT(B$1,"MMYYYY")),Case!$J$2:$BG$23)+SUMPRODUCT(--(Case!$C$2:$C$23="x")*--(Case!$B$2:$B$23=$A2)*--(TEXT(Case!$F$2:$BC$23,"MMYYYY")=TEXT(B$1,"MMYYYY")),Case!$K$2:$BH$23)+SUMPRODUCT(--(Case!$C$2:$C$23="x")*--(Case!$B$2:$B$23=$A2)*--(TEXT(Case!$F$2:$BC$23,"MMYYYY")=TEXT(B$1,"MMYYYY")),Case!$L$2:$BI$23)

The below one is for you in B2 cell

Formula:

=SUMPRODUCT(--(Case!$C$2:$C$23="x")*--(Case!$B$2:$B$23=$A2)*--(TEXT(Case!$F$2:$BC$23," KKVVVV")=TEXT(B$1,"KKVVVV")),Case!$J$2:$BG$23)+SUMPRODUCT(--(Case!$C$2:$C$23="x")*--(Case!$B$2:$B$23=$A2)*--(TEXT(Case!$F$2:$BC$23,"KKVVVV")=TEXT(B$1,"KKVVVV")),Case!$K$2:$BH$23)+SUMPRODUCT(--(Case!$C$2:$C$23="x")*--(Case!$B$2:$B$23=$A2)*--(TEXT(Case!$F$2:$BC$23,"KKVVVV")=TEXT(B$1,"KKVVVV")),Case!$L$2:$BI$23)

Drag it down and right.

Wow, it worked! Just had to remove one extra space inside the quote marks in the first sumproduct formula. Other than than, worked perfect. Thanks a lot!

=SUMPRODUCT(--(Case!$C$2:$C$23="x")*--(Case!$B$2:$B$23=$A2)*--(TEXT(Case!$F$2:$BC$23,"KKVVVV")=TEXT(B$1,"KKVVVV")),Case!$J$2:$BG$23)+SUMPRODUCT(--(Case!$C$2:$C$23="x")*--(Case!$B$2:$B$23=$A2)*--(TEXT(Case!$F$2:$BC$23,"KKVVVV")=TEXT(B$1,"KKVVVV")),Case!$K$2:$BH$23)+SUMPRODUCT(--(Case!$C$2:$C$23="x")*--(Case!$B$2:$B$23=$A2)*--(TEXT(Case!$F$2:$BC$23,"KKVVVV")=TEXT(B$1,"KKVVVV")),Case!$L$2:$BI$23)