I am trying to get a sum of column C if A is abc or cba and B is def:
=SUMIFS(C2:C51;A2:A51;{"abc","cba"};B2:B51;"def")
But the formula is not valid, not sure where is my mistake since this was proposed in a quick google search.
Thank you for your suggestions.
The formula is valid for me, but this might be an issue with your delimiter. Depending on your excel, windows or location settings you might need to use a comma , as a delimiter, instead of a semicolon ;.
As for your formula, for completion I've done the same google search and ended up with this reference. It seems your logic in the formula is correct apart from one crucial step, the SUM( wrapping around your formula. This means if your formula works, it will only take the first hit into account, but with the sum, it will count every entry where your logic is True. Syntax:
=SUM(SUMIFS(C2:C51,A2:A51,{"abc","cba"},B2:B51,"def"))
Or semicolon delimited:
=SUM(SUMIFS(C2:C51;A2:A51;{"abc";"cba"};B2:B51;"def"))
Since the {array} option does not seem to be working for you, I propose a workaround as follows:
=SUMIFS(C1:C15;A1:A15;"abc";B1:B15;"def")+SUMIFS(C1:C15;A1:A15;"cba";B1:B15;"def")
This is a more clunky function, but reaches the same result by splitting up the data in two SUMIFS( functions and adding the results together.