Sum Sumifs on multiple range with multiple criteria in each range

[Hathahrk]

Hello,

i've been working on a "to-be" dashboard for a long time, and i'm now stuck on a sum i need to do based on the selection of four segment
the criteria are generated in 4 differente tables from a UNIQUE(Filter formula

i need a first sum based on the criteria affecting the column " Client ", " Carrier " and " Temperature " regardless of the range" imputation "
and another one based on the same thing, but with the column " imputation " added

i've tried with Sum(SumIFS , but it seem sumifs can't work with this many criteria

i've tried to look at sumproduct but i don't really understand how it work

could you please look at my file and tell me where i'm going wrong ?

thanks in advance,

[Bernie Deitrick]

You're overthinking it: anything that is all or any can just be ignored in the formula.

Sum everything:

=SUM(K:K)

Sum only the imputation is "Carrier" or "Recipient" etc based on the segment:

=SUMIF(I:I,"Carrier",K:K)
=SUMIF(I:I,"Recipient",K:K)
or either:
=SUM(SUMIF(I:I,{"Carrier","Recipient"},K:K))

[Hathahrk]

You're overthinking it: anything that is all or any can just be ignored in the formula.

Sum everything:

=SUM(K:K)

Sum only the imputation is "Carrier" or "Recipient" etc based on the segment:

=SUMIF(I:I,"Carrier",K:K)
=SUMIF(I:I,"Recipient",K:K)
or either:
=SUM(SUMIF(I:I,{"Carrier","Recipient"},K:K))

Hello, I may not have been clear enough,

The formula need to work with any type of criteria filtered with the segment

I know I could write the criteria directly in the formula, but each filtering change the criteria needed on each of the four rznge
So your solution don't fit with what I need

Or I really don't understand your thinking

[Bernie Deitrick]

Sorry - I mis-interpreted what you need.

If you are filtering your data table, you could use =SUBTOTAL(9,RangeToSum) which will respond to your selection of filters no matter how complex.

Otherwise, I would use a pivot table, and apply filters to it, with the data area set to SUM.

Otherwise, I'm not sure what it is that you want to do....

[Haseeb Avarakkan]

Hello,

Try these formulas.

=SUMPRODUCT(ISNUMBER(MATCH(RemiseSAPTM_avec_imputation[Client],Tableau11[Client],0))*ISNUMBER(MATCH(RemiseSAPTM_avec_imputation[Carrier],Tableau10[Carrier],0))*ISNUMBER(MATCH(RemiseSAPTM_avec_imputation[Temperature],Tableau14[Temperature],0)),RemiseSAPTM_avec_imputation[Total UM])

=SUMPRODUCT(ISNUMBER(MATCH(RemiseSAPTM_avec_imputation[Client],Tableau11[Client],0))*ISNUMBER(MATCH(RemiseSAPTM_avec_imputation[Carrier],Tableau10[Carrier],0))*ISNUMBER(MATCH(RemiseSAPTM_avec_imputation[Temperature],Tableau14[Temperature],0))*ISNUMBER(MATCH(RemiseSAPTM_avec_imputation[Responsibilities],Tableau12[Responsibilities],0)),RemiseSAPTM_avec_imputation[Total UM])

[Hathahrk]

Hello,

Try these formulas.

=SUMPRODUCT(ISNUMBER(MATCH(RemiseSAPTM_avec_imputation[Client],Tableau11[Client],0))*ISNUMBER(MATCH(RemiseSAPTM_avec_imputation[Carrier],Tableau10[Carrier],0))*ISNUMBER(MATCH(RemiseSAPTM_avec_imputation[Temperature],Tableau14[Temperature],0)),RemiseSAPTM_avec_imputation[Total UM])

=SUMPRODUCT(ISNUMBER(MATCH(RemiseSAPTM_avec_imputation[Client],Tableau11[Client],0))*ISNUMBER(MATCH(RemiseSAPTM_avec_imputation[Carrier],Tableau10[Carrier],0))*ISNUMBER(MATCH(RemiseSAPTM_avec_imputation[Temperature],Tableau14[Temperature],0))*ISNUMBER(MATCH(RemiseSAPTM_avec_imputation[Responsibilities],Tableau12[Responsibilities],0)),RemiseSAPTM_avec_imputation[Total UM])

Hello

Thank you , it was the solution i was looking for, i really have to find some time to learn the know-how about SUMPROD.
if i may ask, you wouldn't happen to know of a good guide to this Formula ? ^^

To Bernie Deitrick, i also thank you for your help, i have yet to try the subtotal function, but i will try to play around with it.

[Haseeb Avarakkan]

Glad the formula works.

ISNUMBER(MATCH(RemiseSAPTM_avec_imputation[Client],Tableau11[Client],0))

MATCH formula compare two ranges. Gives its position number where that stand in Client table. If NO match found gives error. So it will be like an array of {1;#N/A;#N/A;6;1;4;#N/A....}

ISNUMBER will convert TRUE if match found, otherwise FALSE. So basically this will create an array of TRUE/FALSE values (ie, {TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;.........}

Repeat the same formula for other conditions. just update the range & table names.

ISNUMBER(MATCH(RemiseSAPTM_avec_imputation[Client],Tableau11[Client],0))*ISNUMBER(MATCH(RemiseSAPTM_avec_imputation[Carrier],Tableau10[Carrier],0))*ISNUMBER(MATCH(RemiseSAPTM_avec_imputation[Temperature],Tableau14[Temperature],0))

These formulas, we get an array of TRUE/FALSE values and multiply them each other will get 1 or 0,

Lets say if the output produced by ISNUMBER(MATCH... is {TRUE;FALSE;TRUE}*{TRUE;FALSE;FALSE}*{TRUE;TRUE;TRUE}

TRUE*TRUE = 1 (All should be TRUE. If any of the result is FALSE, will give 0)
TRUE*FALSE = 0
FALSE*FALSE = 0

So, the array will give values of 0 and 1.

The sum range in the SUMPRODUCT is RemiseSAPTM_avec_imputation[Total UM], so SUMPRODUCT will take value from only where the array result is 1.

In text, If all the conditions mentioned are TRUE, then take appropriate number from the sum range and sum it.

I hope the explanation is helpful. I am not good at explaining something