Counting text

J

jlandes84

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
 
M

Max

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)
 
P

PapaDos

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

jlandes84

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top