Consolidate value from non / unpattern data / Sumproduct / Sum IF

[boopathiraja]

Hi All,

I have attached a workbook, I want to sum up codes based on dates in column b, I have manually linked cells to show how the sum up should happen,


1) Column B has the date value,
2) C to G has headers in between rows,occuring in a uncommon pattern,
3) Same dates may occur in two different areas,
4) The actual data contains contains around 2000 rows and 20 columns, I can adapt it to the original based on solution for the attachment,
5) I Prefer formula solution,

Any help will be greatly appreciated !



Boopathi

[boopathiraja]

Note : Result in D21 should be 0, and E21 should be 3, typo...

[Vikas_Gautam]

Here is the formula....But this needs some preliminary activities to be able to run..
=SUM(OFFSET($A$1,
N(INDEX(IF($B$1:$B$14=$B19,ROW($B$1:$B$14)-1),)),
N(INDEX(MMULT(IF($J$1:$N$14=C$18,COLUMN($C$1:$G$1)-1,),1*TRANSPOSE(COLUMN($C$1:$G$1)>0)),))))

Check the attached file..

[boopathiraja]

Excellent !!! , Thanks a ton for this Vikas

[Vikas_Gautam]

Thanks for your quick feedback..!
I am glad..