Count based on multiple criteria

Hello everybody. I'm Steve, I'm new here. I thought this would be a good place to get some advice, so here I am.

I'm currently working on a pet project of mine and could definitely use some help with the formulas. I'm not a programmer, not a technician of any kind, so I really am flying by the seat of my pants and "programming" via what I learn off the internet. I'm actually a lifeguard who all things nerdy.

One problem I'm running in to now is with the pet project of mine. I'm working on an NHL Results chart for my favorite team, the Los Angeles Kings. I've attached what I'm working on so far. If you could focus on columns F, G, and H, that'd be wonderful (Please ignore all the colors and so forth, they're just markers for me).

In hockey, a team can either win (in regulation or overtime), win after a shootout (what we call a SOW), lose (in regulation), lose in overtime, (OTL), or lose after a shootout (a SOL). Wins (regulation or SOW) are 2 points in the standings. A loss in regulation is 0 points. A OTL or SOL is 1 point.

I'm having difficulty getting column H "record" to recognize the differences between a loss, and a shootout loss. So far I've been trying the formula =CONCATENATE(COUNTIF($G$2:$G$2,"Win"),"-",COUNTIF($G$2:$G$2,"Loss"),"-",COUNTIF($F$2:$G$2, AND(F="Yes",G="Loss")))

It works great for wins and losses, yet, that does not seem to account for SOL.

I've tried to make the formula look for whether the game was a win or loss, and then if a loss, determining whether it was a shootout or not (hence the need for columns F and G labeled Shootout? and Result).

It would be amazing if there was someone who could devise a formula that would produce a record in the format I like (wins-losses-SOLs) by referencing columns F and G. I am certainly in over my head and can't seem to get it.

Similarly, I would love a formula for cell O3 that can sum all wins minus shootout wins when LA is only the home team (I have wins and losses figured out for when LA is home or away). Ditto cell X3 when LA is away

Any and all help is greatly appreciated.

Hi, welcome to the forum

For future reference, please title your threads with something that describes your problem - I have changed it for you - this time

Not sure of this is what you want, give it a shot...
=COUNTIF($G$2:G2,"Win")&"-"&COUNTIF($G$2:G2,"Loss")&"-"&SUM(COUNTIF($F$2:G2,{"Yes","Loss"}))
copied down

I'm sorry, that doesn't seem to work. When I plug that in to cell H2, it displays as 0-1-1, when it should read 0-1-0.

F2=No, but G2=Loss, so I would have thought that last number would be 1?

Ok then Im not following you lets assume that the 1st 2 numbers are cottect, and we can then just focus on the 3rd number.

My understanding was that you wanted a count of when F = Yes and when G = Loss. Well it looks to me like you have F2=NO and G2=Loss, so to me, that would return a 1?

That last part of the formula does just that...
SUM(COUNTIF($F$2:G2,{"Yes","Loss"}))

What am I missing here?

OK so we are both looking at the same thing.

Are you counting Yes or No?
Are you counting Win or Loss?

in other words, does Yes = 1 or does No = 1, and dows Win = 1 or does Loss = 1

Honestly, you lost me there. I don't know what that means.

The only time we should get a value in the third condition is when there is both yes and loss. It seems the formula is counting anytime "loss" occurs as a value, regardless of whether there is a "yes" or "no" as well, when I need it to count only the combination of "loss" and "yes".

Additional question, in your formula, why have you used braces instead of brackets towards the end of the formula?

OK I think your last post cleared it up for me (I hope lol)...
=COUNTIF($G$2:G2,"Win")&"-"&COUNTIF($G$2:G2,"Loss")&"-"&COUNTIFS($F$2:F2,"Yes",$G$2:G2,"Loss")

lol sorry it took so long, I didnt get that F had to be Yes AND G had to be Loss.

Im just glad we got you where you wanted to be - and thanks for the feedback