SUM+MATCH+INDEX Function

Dear Experts,

I am trying to find out the items value based on below 2 criteria (Company & Month).

Somebody could help me to solve this formula issue

Particulars Company Jan Feb Mar Apr
Salary & allowances AAA 131 88 115 133
Discount allowed AAA 91 125 107 132
Rent expenses AAA 130 107 112 111
Salary & allowances BBB 125 123 102 130
Discount allowed BBB 109 120 100 109
Rent expenses BBB 103 121 103 106

Company ALL
Month Jan-Mar

Discount allowed ???


Formula : SUM(INDEX($C$2:$F$7,MATCH($A13,$A$2:$A$7,0),MATCH(LEFT(B$12,3),$C$1:$F$1,0)):INDEX($C$2:$F$7,MATCH($A13,$A$2:$A$7,0),MAT CH(RIGHT(B$12,3),$C$1:$F$1,0)))

Thank you for your reply

Sample file attached for your easy ref

In D29 and copy down


=SUMPRODUCT(($D$8:$AA$19)*($D$6:$AA$6>=DATE($D$24,MONTH(LEFT(D$25,3)&0),1))*($D$6:$AA$6<=DATE($D$24,MONTH(RIGHT($D$25,3)&0),1))*($D$7:$AA$7=$D$26)*($B$8:$B$19=$D$27)*($C$8:$C$19=$C29))

in E29 and copy down

=SUMPRODUCT(($D$8:$AA$19)*($D$6:$AA$6>=DATE($D$24,MONTH(LEFT(E$25,3)&0),1))*($D$6:$AA$6<=DATE($D$24,MONTH(RIGHT($D$25,3)&0),1))*($D$7:$AA$7=$E$26)*($C$8:$C$19=$C29))

I changed dates in row 6 to Excel dates and formatted as "mmm"

I tried with this formula. but unfortunately its not working for me. Attached my file for your ref.

It works for me

Today is my first day in this group.
I am really happy to be here.
Thank you very much for your great assistance.
I really appreciate it.

@GK: probably from you!!!

It is something that has appeared on this forum fairly frequently and it is one of the few "tricks" I remember when it comes to converting text dates to real dates.

As I am sure you know, I much prefer that real dates are used and then simply format these as required.

(Not sure why change to EOMONTH: dates in row 6 are all 1st of month so <= DATE($D$24,MONTH(RIGHT($D$25,3)&0),1) will work as it effectively a month check: certainly did on the posted file).