Summing the mutiple ranges where the Same Header are there (Tried Sumifs, Index, Xlookup)

[FUN2BALA]

Hi,

Can u pls help me out for the the attached sheet.
There are two sheets one is ABC and other summary of ABC sheet
In ABC sheet columns there are the month where some months are duplicate and some are single.
Need to sum the total of Every single month with two conditions which are mentioned in Column a & b (for eg: in May month the figures are in 2 columns need to sumup the may month and give the total)

Thanks in Advance.

[alansidman]

In your source data, you have some headers which look like dates but are something else. Please explain, for example, Jul-213. I am trying to manipulate your data, but this is not a workable date. Additionally, why are you hiding some rows in the source data? Is this information to be included or excluding from the analysis. The easiest way to summarize is to have consistent date values and then unpivot the data so that it is normalized for analysis. Current layout is not conducive to summarizing your information.

[Estevaoba]

Try in D2 and copy across and down:

Please Login or Register  to view this content.

Good luck!

[FUN2BALA]

Thanks for ur reply.

Checked the date in Source data it showing in first date of the month, can u pls tell how do u check (JUl-213).
Just for clarity only hided it should be included for analysis.

Sorry for the layout this is what we have recd. and maintained will try to change the layout if possible.

we tried the formula of index match with sumifs/sumif fro example in may month in summary sheet it s picking up the data properly for Nos. 6 and type A and for Nos 7 its shows #ref.

[Estevaoba]

Have you tried the formula in post #3?

[alansidman]

@Fun2bala

I have no idea what you mean here.

Checked the date in Source data it showing in first date of the month, can u pls tell how do u check (JUl-213).

[FUN2BALA]

Yes. It working. Thanks

[FUN2BALA]

Explaining the things for the detail u mentioned


for example, Jul-213


For ref i have mentioned in Summary sheet the april month is right and for may month its not working.


Apr-21 May-21
6 A 1,08,493 1,12,110---correct figure
7 A 2,46,575 #REF!-----error showing
8 A 3,20,548 #REF!
9 A 98,630 #REF!
10 A 98,630 #REF!

[JohnTopley]

Try (as per post #3)

in D2

=SUMPRODUCT((ABC!$C$4:$U$19)*(ABC!$A$4:$A$19=$A2)*(ABC!$B$4:$B$19=$B2)*(ABC!$C$2:$U$2=D$1))

[FUN2BALA]

Thanks. JohnTopley its working.

Post 3 also working. Just wanted the formula alternate to sumproduct.

Thanks once again for ur effort and time.