SUMIFS with multiple criterias row/colums

[btem]

Good afternoon,

First of all, thank you for this forum that I've been reading from time to time, and helped me to solve Excel issues without the need to create new topics... Thank you for this.

For this one I couln't find any solution to my problem. Maybe someone can help me ?

After extracting data from Business Objects to my Excel spreadsheet (accounting data from various companies), I would like to consolidate (sum) data from several companies.

In rows I would need to use criterias like different accounts numbers (ex : "4110000" + "4861200") AND in colums, I would need to match criteria "company names" (ex : "CIE_1" + "CIE_2").


As far as I'm now, I found 2 possible ways (any other solution you could propose is warmly welcome):

1/ SUMPRODUCT : I managed to obtain a working test formula, but it gets far too complicated to use it as I will need about 25 to 30 criterias from columns and 15 to 20 criterias from rows, coming from different spreadsheets (this formula would be working but you can figure how long to type it would be, as I need to build many of those and source of errors if any modification is needed someday)

2/ SUMIFS : This formula would be perfect IF I could manage to get a one that works in my case.

Here is the formula I build, but it is not working now (returns #VALUE!)

Please Login or Register  to view this content.

If it can help, here are the 2 problems I could identify in my formula, but was not able to solve :
* How can I select a sum_range wider than a simple colum (here the range I need is C8:G20). It seems that SUMIFS fonction returns #VALUE! on wide range even with basic tests.

* I need to select criterias from rows (ex : some accounts in B8:B20) AND columns (ex: some companies in C7:G7).
Here also, it seems that SUMIFS fonction returns #VALUE! on cross criterias rows/columns, even with basic tests.

I joined my excel file, so it will be clearer for anyone who'd like to help...


Please help me to get this formula working, otherwise any other solution is welcome to get the consolidated figures I need...


Thank you for your time and advice

[daddylonglegs]

You won't be able to use SUMIFS with that sort of problem. Try SUMPRODUCT like this

=SUMPRODUCT(C9:G21,ISNUMBER(MATCH(B9:B21,{4110000,4861200},0))*ISNUMBER(MATCH(C7:G7,{"CIE_1","CIE_2"},0)))

You can easily replace the criteria parts with cell ranges listing those so assuming you have the criteria stored in Y2:Y3 and Z2:Z3 that can be

=SUMPRODUCT(C9:G21,ISNUMBER(MATCH(B9:B21,Y2:Y3,0))*ISNUMBER(MATCH(C7:G7,Z2:Z3,0)))

NB: criteria ranges can be as long as you like, a column or row, it doesn't matter........

[btem]

Thanks for your incredibly superfast answer daddylonglegs !

I'll try this one and let you know :-)

Thank you !

[btem]

Hi daddylonglegs !

Thank you for your message. The formula is working well in the tests I've done so far.

You greatly optimized the sumproduct formula I tried to build before trying the SUMIFS (my sumproduct was not very sophisticated - as per below- , and thus it would have been far to long with numbers of criterias needed...)

=SUMPRODUCT((($C$7:$H$7="CIE_1")+($C$7:$H$7="CIE_2")+($C$7:$H$7="CIE_3")+($C$7:$H$7="CIE_4"))*((B9:B21=4110000)+($B$9:$B$21=4861200)+($B$9:$B$21=4861400)+($B$9:$B$21=4861600)),C9:H21)

So thank you very much for your formula ! I'll now try to understand the way you built it, in order to improve my excel skills !

Thanks again

Bryan

[btem]

Do you think it would be possible to use as a criteria some range of accounts, as per below suggestion (either one or the other, or any other option but here is the idea) :

1/ accounts from/to : {4000000:4999999}

2/ any accounts from "4" : 4* (?)

[daddylonglegs]

If there's an upper or lower range for the column B criteria then instead of the previous version I suggested try

=SUMPRODUCT(C9:G21,(B9:B21>=4000000)*(B9:B21<5000000)*ISNUMBER(MATCH(C7:G7,Z2:Z3,0)))

or if you want to check if the number begins with 4 (remembering that that would also include numbers like 47 or 4892) then change to

=SUMPRODUCT(C9:G21,(LEFT(B9:B21)="4")*ISNUMBER(MATCH(C7:G7,Z2:Z3,0)))

Note that I assume that the data in B9:B21 is numeric so 4000000 doesn't need quotes (because the values to match aren't text)......but the "4" in the second formula has quotes because LEFT function always returns text, even when extracted from a number......

[btem]

Got it, understood. Thanks.


Also, i'm now implementing the formula in my "reel" excel file.

For some cases I need to extract the criteria only if the cell result is a negative or positive figure :

Ex : for the account 4861200, some companies show a positive figure and others a negative one.

Would it be possible to include the figure in the sumproduct only if it is either negative or positive ? (not the criteria figure, but the figure returned by the formula)

Could it be something like that ? :

=SUMPRODUCT(C9:G21,ISNUMBER(MATCH(B9:B21,{4110000,4861200},0)<0)*ISNUMBER(MATCH(C7:G7,{"CIE_1","CIE_2" },0)))

[daddylonglegs]

For that you have to add another criteria like this

=SUMPRODUCT(C9:G21,(C9:G21>0)*ISNUMBER(MATCH(B9:B21,{4110000,4861200},0))*ISNUMBER(MATCH(C7:G7,{"CIE_1","CIE_2" },0)))

[btem]

You're just amazing... I wish someday I can "talk" to Excel as you do...

Thank you so much.

Is there any supporting that I could use to improve my skills on building formulas like this ?