Counting text

[Guest]

I'm trying to count a number of specific "W"s in a column.

For example:
The columns are:
A B C
Pick All games
39 RutgersW

Home vs. Away
39 RutgersW

Last 3
39 RutgersW

And so forth for other games of that week.

When I do the =countif(b1:b100,"W"), I get all the W's.

How do I set it up so that I can get just the "All" W's, "Home vs. Away"
W's, and "Last 3" W's separately?

Thanks

 

[Guest]

One way ..

Put in D4:
=IF(B4="W",OFFSET(B4,-3,),"")
Copy down to last row of data in col B

Then with F1:F3 housing the text:
All games
Home vs. Away
Last 3

Place in G1:
=SUMPRODUCT(($B$1:$B$1000="W")*($D$1:$D$1000=F1))
Copy G1 down to G3 to return the required results
(Adapt the ranges to suit)

---

 

[Guest]

Are your "Sections" always 4 lines ?
Is a blank cell always present between sections ?
Why can't you just modify the range used by COUNTIF ?

Give more informations...

 

[Guest]

Max,

Thanks for your reply and I tried your suggestion, but I got 0's instead of
the desired totals.

Here is what I'm wondering. Is there any kind of formula that will total
just W's for wins, of course, (and L's for losses) for 13 to 16 football
games a week (more for college games) in four separate categories: For
example: All, Away vs. home, Last 3 and Vs. Div.?

I"m just showing three columns. There are several others, so I'll want the
results to be displayed at the bottom of the page. Each category is four
lines, with a space every fifth line.

A B C
All Schedule

0 Raiders
W

Away
Vs.
Home Schedule
20 Rams
L

Last
3 Schedule
0 Raiders
W

Vs.
Div. Schedule
20 Rams
L


All Schedule
9 Chiefs
W

Away
Vs.
Home Schedule
9 Chiefs
W


Last
3 Schedule
9 Chiefs
W

Vs.
Div. Schedule
9 Chiefs

W


W L
All Games 16 33 27
Away vs. Home 16 33 27
Last 3 16 33 27
Vs. Division 16 33 27

Again, thanks for you time (and patience).

 

[Max]

Max,
Thanks for your reply and I tried your suggestion,
but I got 0's instead of the desired totals.

Just tidying up here, fwiw <g>
here's a working file to illustrate the earlier suggestion:
http://cjoint.com/?mswmMGpWCz
jlandes84_wks.xls


---