Sumifs formula to add multiple columns based on same criteria

[Vadivelan]

Dear Team,

I would like to ask your help to do the sumifs of multiple columns based on the same criteria without the helper column. I had attached the same file for more clarity

Sumifs.xlsx

Earlier I was using Sum product formula but it was very slow, since my live file is having around 10,000+ Rows

Please support

Thanks and Regards
R. Vadivelan

[Phuocam]

Try this:

=SUM(FILTER($C$3:$F$6,($A$3:$A$6=H3)*($B$3:$B$6=I3)))

[windknife]

Another way, clean all expected results.

Please Login or Register  to view this content.

[CARACALLA]

=if($h3<>"",sum(index($c$3:$f$100000,match($h3&$i3,$a$3:$a$100000&$b$3:$b$100000,0),)),"")

[AliGW]

Or this much simpler spill formula:

=VSTACK(HSTACK(A2:B2,"Total"),SORTBY(HSTACK(A3:B6,BYROW(C3:F6,LAMBDA(r,SUM(r)))),A3:A6,,B3:B6,))

[Vadivelan]

Thank You Ali for your support, It works fine

[Vadivelan]

Thanks for your reply, it works

[Vadivelan]

Thanks for your help, it works

[Vadivelan]

Thanks for your simpler formula, it works fine. However I would like to ask your support to apply the same formula with blank columns in between.

I.e In case there sum values are from C:D and G:H then how we can modify the above formula

[AliGW]

Provide an updated sample workbook.

[AliGW]

Actually, if the columns are blank (REALLY blank), then this would work:

=VSTACK(HSTACK(A2:B2,"Total"),SORTBY(HSTACK(A3:B6,BYROW(C3:H6,LAMBDA(r,SUM(r)))),A3:A6,,B3:B6,))

[AliGW]

If the intervening columns are NOT blank, try this:

=VSTACK(HSTACK(A2:B2,"Total"),SORTBY(HSTACK(A3:B6,BYROW(HSTACK(C3:D6,G3:H6),LAMBDA(r,SUM(r)))),A3:A6,,B3:B6,))

[AliGW]

Please continue here for this and the earlier (closely related) XLOOKUP query - they are part and parcel of the same thing. Thanks.

[AliGW]

And in case you want it on another worksheet:

=VSTACK(HSTACK(Sheet1!A2:B2,"Total"),SORTBY(HSTACK(Sheet1!A3:B6,BYROW(HSTACK(Sheet1!C3:D6,Sheet1!G3:H6),LAMBDA(r,SUM(r)))),Sheet1!A3:A6,,Sheet1!B3:B6,))

[AliGW]

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.