Something like sumifs function, please help

[Ivan1984]

Dear all,

i have three different rows with filters, for example, Ronaldo played for his country and club on five different positions.
I want to calculate how much goals and assists score like striker, left wing, right wing etc... but somewhere i wanna know how much he score only for club and two different position... Off course, i have 500 different football players and i wanna know for any of them how many he scores or assist for his country/club, and how many from combination of position...
sorry, i don t know how better to explain what i try to get...
I try with sumifs function, but i cannot get values by two different condition in one column.
Example attached, i would apreciate help.

Sorry for my bad english.

Thank you very much

 

[XOR LX]

Hi,

In J4:

=SUM(SUMIFS(D:D,$A:$A,$G4,$B:$B,IF($H4="International/Club","*",$H4),$C:$C,MID($I4,3*{0;1;2;3;4}+1,2)))

and copied down and right.

This assumes that:

1) The only 2 possibilities for the CAPS column are "International" and "Club".
2) There are only 5 positions: "LW", "RW", "ST", "AM" and "MC" (though the above can easily be modified to accommodate more).

Regards

 

[Ivan1984]

Hello Xor LX,

thank you very much for your effort and quickness.

Something don t work. I have excel 2016 and i must separate conditions with ;. i change that and at the end of formula excel said i have too few arguments: +1,2 this part of formula show me...

On your first comment you sad that i have only 2 possibilities, but in reality is three (club, international, club and international).

Sorry, but i don t get it that...

Thank you very much again.

 

[AliGW]

Change that final comma to a semi-colon - it is NOT a decimal point.

This has nothing to do with the version of Excel - it is your locale that requires you to use semi-colons instead of commas, and commas instead of decimal points (which this isn't).

 

[Ivan1984]

AliGW, thank you very much. It works.

Thank you!

 

[AliGW]

No problem - glad to help.