• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Something like sumifs function, please help

Ivan1984

New Member
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
 

Attachments

XOR LX

Active Member
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

New Member
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

Active Member
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).
 
Top