COUNTIF function to count specific, but non consecutive, cells in a row

0
votes

I have an NFL spreadsheet where each row represents a specific team, and every 5 columns represents weekly information about the team (Column A is the team, Columns B-F are stats for week 1, G-K are stats for week 2, etc.). I am trying to perform different calculations on information from the same stat for each week (I.E. sum of columns B,G...). One column includes the teams margin of victory for each week. In order to calculate the teams Win/Loss record, I attempted to use a COUNTIF() function, counting each Margin of Victory column for each week - count a Win for each week where the MOV is greater than 0, Loss for less than zero.

Sample Data

=COUNTIF((Weeks!E3,Weeks!J3,Weeks!O3,Weeks!T3,Weeks!Y3,Weeks!AD3,Weeks!AI3,Weeks!AN3,Weeks!AS3,Weeks!AX3,Weeks!BC3,Weeks!BH3,Weeks!BM3,Weeks!BR3,Weeks!BW3,Weeks!CB3,Weeks!CG3),>0)

The result is a formula parse error, because I've entered too many arguments for the COUNTIF() function (although my assumption was adding parentheses around the data would make it one argument). The desired outcome, reference the picture for example, would be on another sheet to produce 1 loss and 1 tie (in separate cells) for Arizona (Week 1 margin = 0, week 2 margin = -6), and then replicate this over the course of 17 weeks as implemented in the formula.

2
excelexcel-formula
Could you be looking for COUNTIFS? – cybernetic.nomad

2 Answers

1
votes

Maybe some of these formula's:

For positive results:

=SUMPRODUCT((MOD(COLUMN(A3:CG3),5)=0)*(A3:CG3>0))

Or:

=SUM(INDEX((MOD(COLUMN(A3:CG3),5)=0)*(A3:CG3>0),))

For draws:

=SUMPRODUCT((MOD(COLUMN(A3:CG3),5)=0)*(A3:CG3=0))

Or:

=SUM(INDEX((MOD(COLUMN(A3:CG3),5)=0)*(A3:CG3=0),))

For negative results:

=SUMPRODUCT((MOD(COLUMN(A3:CG3),5)=0)*(A3:CG3<0))

Or:

=SUM(INDEX((MOD(COLUMN(A3:CG3),5)=0)*(A3:CG3<0),))

Change A3:CG3 to whichever range it actually goes to.

0
votes

Instead of making a large countif, you can sum all of the individual countif functions.

=SUM(COUNTIF(Weeks!E3,">0"),COUNTIF(Weeks!J3,">0")...

So on and so forth. Since each countif function will return either a 0 or 1, summing all of those together should hopefully work. It might be a little annoying but this is how I've made formulas like that work. Let me know if this works for you.