Count numbers in columns

[rbpd5015]

Ok guys I am working on a spreadsheet and could use some help


I got a sheet where I need to pull the numbers listed under the columns


for example I have cut and pasted my data below. It did not paste well however I think you might be able to still figure it out.

I need to know how many Game 1's are being played in each week
and I need to know how many Game 2's are being played in each week.

Matt

Teams Game 1 Week # Game 2 Week # Game 3 Week #
Florida Gators Troy 2 Tennessee 3 At Kentucky 4
Texas Longhorns At Wyoming 2 UTEP 4 Colorado 6
USC Trojans San Jose State 1 At Washington 3 Washington State 4
Oklahoma Sooners Tulsa 3 Baylor 6 Kansas State 9
LSU Tigers At Washington 1 Vanderbilt 2 UL Lafayette 3

[modytrane]

post a sample workbook.
can't figure out from the data pasted.
modytrane

[rbpd5015]

Ok I have posted the workbook.


I now know how to count numbers in column

however I have a big issue.


I need to be able to stop counting the numbers also.


So for that on Sheet 1 I added RESULT

I want if that = "W" then for that not to be counted anymore...

Make Sense?

So Column C has the week number and column D has result "W" "L" or blank
I want when counting to look at the neighboring cell and to see if it is a win or loss before counting.

Matt

[JBeaucaire]

This formula gives you the answer for the Game1 column:

=SUMPRODUCT(--($B$2:$B$7<>""),--($D$2:$D$7<>"W"))

You can take it from there for the other columns, yes?

[rbpd5015]

Jb,

Not home yet but that should work perfect.

Last part if game 1 is a W I don't care about the remaining games so I want to automatically make them = N/A

So if game 1 is a L and game 2 is a W game 3 and game 4 become N/A.

This way I know how many active games I have that week?

Can you walk me through that one.

Matt

[JBeaucaire]

No, I don't really see how this all ties in at all. Perhaps a few sheets showing your sequential desired "results" as you make changes.

[rbpd5015]

JB,


Ok we know the following
Florida Gators Troy 2 Tennessee 3 At Kentucky 4

The Florida Gators play Troy in Week 2 Tennessee in Week 3 and Kentucky in Week 4


Now each game will have a result
W for Win
L for Loss
N/A for Not Applicable
and blank if the game has not been played and needs to be played.

The goal is to win 1 of the 3 games.

So if Florida Wins in week 2 vs Troy then Week 3 and Week 4 result becomes N/A.

if Florida Looses in Week 2 vs Troy then Week 2 result = L and Week 3 and Week 4 stay blank because the games still need to be played.

then if Florida Wins in Week 3 vs Tennessee the following needs to occur.
Week 2 stays L Week 3 becomes W and 4 becomes N/A


Follow,
Matt

[rbpd5015]

JB,

I dont think that sum product work correct. I think it is my fault maybe I didnt explain myself good enough .

Ok I will try again.

I have uploaded my book with 6 teams. All 6 teams have played a season (test of course).

Now I want to be able to see the following.

1) How many games were actually played in WEEK 1. (To tell if a game was actually played it must have a W or L. If it is blank or has a N/A it either has not been played (blank) or it is not needed (N/A).


If you go to sheet 2 of my chart this is where I need to insert it.

So it lists weeks 1-16 on the left side
then up top it shows stage 1-4 that is = game 1-4 in the first page.

For Example if you look at Florida

there stage 3 game is at Kentucky in week 4 and the result is a loss.

so on sheet 2 on row Week four and in the stage 3 column this would count as 1 game. However that column needs to show the total stage 3 games played in week 4.

second example.

Ohio State stage 3 game was Illinois in week 4... HOWEVER it is N/A because Ohio State won their stage 1 game....making stage 2 3 and 4 N/A.

so on sheet 2 this game would not be reflected at all.

Any help you guys could give would be great.

MAtt

[JBeaucaire]

SHEET1:

In G2 and then copied down:
=IF(D2="W","N/A","")

In J2 and then copied down:
=IF(COUNTIF(D2:I2,"W")>0,"N/A","")

In M2 and then copied down:
=IF(COUNTIF(D2:L2,"W")>0,"N/A","")

SHEET2:
Changed A2:A17 and used a custom number format so the only thing actually in that cell is numbers 1-16. This allows the formulas to follow to use these cells as reference.

In C2 and copied down:
=SUMPRODUCT(--(Sheet1!$C$2:$C$600=$A2),--(Sheet1!$D$2:$D$600<>"N/A"))

In D2 and copied down:
=SUMPRODUCT(--(Sheet1!$F$2:$F$600=$A2),--(Sheet1!$G$2:$G$600<>"N/A"))

In E2 and copied down:
=SUMPRODUCT(--(Sheet1!$I$2:$I$600=$A2),--(Sheet1!$K$2:$K$600<>"N/A"))

In F2 and copied down:
=SUMPRODUCT(--(Sheet1!$L$2:$L$600=$A2),--(Sheet1!$M$2:$M$600<>"N/A"))

In L2 and copied down:
=SUMPRODUCT(C2:F2*$C$28:$F$28)


Sheet attached, I've colored all the cells I changed.

[rbpd5015]

Jerry,

Thanks a million so far for your help...

I see 99% of it works however I see a few things that prevent it from working fully.

#1) in the result column there are formulas in the result columns for stage 2 3 and 4. This is good if there is a W in stage 1. However if there is a stage 1 loss, you move on to stage 2 and must then put a W or L. I cant do that because there is only a formula there and when I put a W or L there it copies over the formula.

#2) When I go to each school and make them when their stage #1 game on sheet #1, it works correct on sheet2 by saying there are no future stage 2 games the rest of the year. However it still shows all of the schools stage 3 and stage 4 games even though they are N/A.


Matt

[rbpd5015]

Jerry,

I just corrected one small problem I guess I am not to useless.

#2) When I go to each school and make them when their stage #1 game on sheet #1, it works correct on sheet2 by saying there are no future stage 2 games the rest of the year. However it still shows all of the schools stage 3 and stage 4 games even though they are N/A.

I fixed this

=SUMPRODUCT(--(Sheet1!$I$2:$I$600=$A2),--(Sheet1!$M$2:$M$600<>"N/A"))

Where $M$ is you had $K$

YAY!!!! I learned something.

Still got the problem I listed as number 1. This is a show stopper as of now. If I cant write results in the column I can make it a win or loose. Now I know I can write over the formula, however then I need to go back and re add it each time I do this. Makes it very difficult.

Matt

[JBeaucaire]

#1) in the result column there are formulas in the result columns for stage 2 3 and 4. This is good if there is a W in stage 1. However if there is a stage 1 loss, you move on to stage 2 and must then put a W or L. I cant do that because there is only a formula there and when I put a W or L there it copies over the formula.

Of course. You'll type over the formula, yes? That's the correct thing to do as you use the sheet

Still got the problem I listed as number 1. This is a show stopper as of now. If I cant write results in the column I can make it a win or loose. Now I know I can write over the formula, however then I need to go back and re add it each time I do this. Makes it very difficult.

Re-add it each time? What does that mean? You set up the sheet and then use it through the year. You won't "re-add" until next year, but you'll start with your template, right? You'll keep a clean, ready-to-start version for next time.

#2) When I go to each school and make them when their stage #1 game on sheet #1, it works correct on sheet2 by saying there are no future stage 2 games the rest of the year. However it still shows all of the schools stage 3 and stage 4 games even though they are N/A.

I don't follow that. Show me.