what's wrong with this SUMIFS formula?

[pccamara]

Hey guys,
I have this formula:
=SUMPRODUCT(SUMIFS(Sales[TotalN],Sales[ICM],"*"&AI5:AJ5&"*",Sales[Company],$AH$5,Sales[CC No],$B8,Sales[TipoDoc],{"FACFO","NC"},Sales[Date],">="&E$6,Sales[Date],"<="&EOMONTH(E$6,0)))

This formula has 2 "ORS" on the same "sentence" and works if i only want that Sales[TipoDoc],"FACFO".

Maybe because i have two "ORS" on the same formula and i can't have it? I know i can do this by duplicating this formula with the sign "+", i mean, adding every records that meet all those criterias and also = "FACFO" plus everything but with "NC" on TypeDoc. Example: =SUMPRODUCT(SUMIFS(Sales[TotalN],Sales[ICM],"*"&AI5:AJ5&"*",Sales[Company],$AH$5,Sales[CC No],$B8,Sales[TipoDoc],"FACFO",Sales[Date],">="&E$6,Sales[Date],"<="&EOMONTH(E$6,0)))+=SUMPRODUCT(SUMIFS(Sales[TotalN],Sales[ICM],"*"&AI5:AJ5&"*",Sales[Company],$AH$5,Sales[CC No],$B8,Sales[TipoDoc],"NC",Sales[Date],">="&E$6,Sales[Date],"<="&EOMONTH(E$6,0)))

My question is, i wonder how i could do it on the same formula, or, what is wrong with my formula above?

Thank you very much
Best regards
Pedro

[jason.b75]

Try SUM instead of SUMPRODUCT.

Beyond that, formula looks fine.

SUMPRODUCT with the array, {"FACFO","NC"} is creating an AND condition, not an OR condition.

[pccamara]

Hey Jason,
Didn't know that thing of OR and AND for SUM and SUMPRODUCT. Is this in some microsoft article? Cose i try to find it some time ago and never did. If by any chance you have it, can you please send it to me?
About our issue, i'm sending you a file where you can make your point, cose still not working like i would like to.
Whenever you can take a look, is great.
Thank you so much for your help
Best regards
Pedro

[davsth]

Not withstanding your ICMs are inconsistent to make the match
=SUMPRODUCT((F2:F11)*(ISNUMBER(MATCH(B2:B11,K2:K3,0)))*(ISNUMBER(MATCH(G2:G11,L2:L3,0)))*(ISNUMBER(MATCH(H2:H11,M2:M3,0))))

[pccamara]

Hey Davsth
I'm sorry to say but your solution is not working, according to the structure of my table. With your solution, the ICM criteria field should have all the letters to match.
Isn't there any other way? This creteria, for example, i want all values that match, AB*, cose there is an ABp, ABt, ABu and ABy

[romperstomper]

You could also use DSUM. Enter this into O2, leaving O1 blank:

=AND(C2=$J$2,MATCH(B2,$K$2:$K$3,0),MATCH(G2,$L$2:$L$3,0),SUMPRODUCT(COUNTIF(H2,$M$2:$M$3&"*"))>0)

then for your totals use:

=DSUM(A1:H11,"TotalN",O1:O2)

[jason.b75]

Hey Jason,
Didn't know that thing of OR and AND for SUM and SUMPRODUCT.

Please ignore my previous reply, when I looked at your formula, I missed the second 'OR' condition, "*"&AI5:AJ5&"*"

Having this in SUMIFS, or any similar function is something that I don't think will work too well, if at all (see post #6 for an alternative way).

Regardless of the function used with SUMIFS, the end result will ultimately be the same, the formula will look for records that meet the 1st criteria of the (1st array AND the 1st criteria of the 2nd array) OR (2nd criteria of the 1st array AND 2nd criteria of the 2nd array), etc.

So, using your sample file as a reference,

Please Login or Register  to view this content.

The formula will only return the results for (*FAFCO* AND AS AND *Nor*) OR (*NC* AND JA AND *AB*), but not for any other combination.

When I said that SUMPRODUCT was giving you AND instead of OR, I had not evaluated the formula correctly, on top of that, even if what I had said was correct, my choice of words would have been misleading.

Appologies for going back to basics, but easiest way to explain my flawed logic.

SUMIFS sums rows that meet the given critera.
SUM adds numbers together.
SUMPRODUCT multiplies numbers in each row, then adds up the subtotals.

For some reason I was thinking that using SUMPRODUCT would multiply the conditions met by SUMIFS insetead of adding them, in the same way that =SUMPRODUCT((A2:A10=1)*(A2:A10=2)) would, naturally, you can't have 1 and 2 in the same cell so this would always return 0.
Not long after posting my reply I realised the flaw in my logic, but didn't get chance to return to the forum to correct it. Even if the formula had behaved as I was thinking, it would have multiplied the subtotals produced by SUMIFS rather than the criteria arrays, in the manner of =SUMIFS(...)*(SUMIFS(...)

[samba_ravi]

try below array formula
=SUMPRODUCT(COUNTIF(K2:K3,B2:B11)*COUNTIF(L2:L3,G2:G11)*(LEFT(H2:H11,LEN(TRANSPOSE(M2:M3)))=TRANSPOSE(M2:M3))*(C2:C11=J2)*(F2:F11))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.