Need help with a formula that counts the number of rows where a specific range of cells sums to x

[tomdavidge]

This has been driving me completely insane. But I just cannot seem to construct a formula which is outputting what I want. I'll give an example of what I'm looking for and then expand further. 

(Say the numbers in the table represent members of the relative teams involved)

Essentially, what I'm looking to do. Is create a formula that counts the number of cases that have x team members attached to them, regardless of the team they're from. 

For this example, the chart I want to create looks like this,

When the formula is complete, the table should look like this,

 I can count the number of cases with x teams involved, but I just can't get the sum total of involved members. I have been staring at this for two hours and my brain is fried I simply do not know how to do it. 

Any help would be much appreciated. I hope this makes sense. If you require further clarity let me know.

Thank you!

[#2]

@tomdavidge 

I'd add a column to the first table with formula

=SUM(Table1[@[Team 1]:[Team 3]])

In the screenshot below, I named it Total.

You can then use

=COUNTIF(Table1[Total],[@[Number of Team Members Total Per Case]])

in the second table.

[#3]

Hello! Thanks for the reply. I did think about this but it is an ever expanding list so I would either have to have a recurring formula that would slow down my already pretty laden spreadsheet or manually add it every time and I was hoping it could be done within the formula

[#4 · Accepted solution]

@tomdavidge 

Excel will store the formula only once for the entire column, and it will automatically be added to new rows, so it's quite efficient actually

[#5]

Oh my god, I'm a complete idiot. So for my actual piece of work. I only had conditional formatting putting it together as a table. I hadn't actually used the table function. So i had written off using reoccurring formulas like that. This has saved my **bleep**. Thank you my friend