SOLVED

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

Copper Contributor

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. 

 

tomdavidge_0-1692361099663.png

(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,

tomdavidge_1-1692361300016.png

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

tomdavidge_2-1692361626148.png

 

 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!

 

 

4 Replies

@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.

HansVogelaar_0-1692362466273.png

 

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
best response confirmed by tomdavidge (Copper Contributor)
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

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
1 best response

Accepted Solutions
best response confirmed by tomdavidge (Copper Contributor)
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

View solution in original post