My pleasure



--


Regards,


Peo Sjoblom



"JLewis" wrote in message
...
Oh yeah! That works great. This spreadsheet is finally functional! I
really
owe you! Thanks for all of your help.

"Peo Sjoblom" wrote:

Yes, assume you put the different divisions as text in let's say H1:H10
(I
don't know how many there can be but keep in mind that the bigger size
the
slower spreadsheet when it comes to these kind of formulas)


then this would count it


=SUMPRODUCT(--(D3:D808=D2-1),--(ISNUMBER(MATCH(A3:A808,H1:H10,0))),--(B3:B808A2),--(B3:B808<A3))


Good luck

--


Regards,


Peo Sjoblom

"JLewis" wrote in message
...
A:3:A808. This could be a text, like Pro Amateur, Junior, Women, etc.
For
my
purposes right now, I have it as a number, like 1, 2, 3, etc. I would
like
to
filter on a text eventually. In the criteria cell A1, I only use one
division at a time. I just want to know how to count scores for all
divisions
together if I need to. Can this be done?

"Peo Sjoblom" wrote:

I am not sure I understand, which column is the division?

--


Regards,


Peo Sjoblom

"JLewis" wrote in message
...
One more thing...(I know, I'm needy!). If I wanted to see for
instance,
all
divisions, what would I put in the criteria cell? I know for the
ratings,
I
could put 1 and 2000 or something like that, given ratings are
between
1
and
1100. But divisions are different. Thanks again.


"JLewis" wrote:

Okay, I have finally gotten it to work. I had the criteria cells
mixed
up. I
entered criteria for rating in the division cell. Yeah! It works!
You
are
a
genius. Thank you very much !

"JLewis" wrote:

Yes, I did catch my typo. I was wondering in the formula, where
does
it
state
what to count? I ran a formula analysis and I see the true/false
of
the
criteria search, but it doesn't seem to be counting anything in
the
D
column
where it should.



"Peo Sjoblom" wrote:

I noticed you wrote A1, A2 and A3 whereas your formula showed
BA3
If you really meant A3 instead and if you pasted in the formula
I
gave you
that might explain it.

If that's the case change BA3 to A3 at the end

The formula I gave you will count where D3:D808 equals D2-1 AND
A3:A808
equals A1
AND where B3:B808 is greater than A2 AND less than either BA3
or
A3
whichever it is
supposed to be.

--


Regards,


Peo Sjoblom

"JLewis" wrote in message
...
It only gave me a "0". When I know for the criteria there
should
be
282
Birdies. I really appreciate the help!

"Peo Sjoblom" wrote:

Try

=SUMPRODUCT(--(D3:D808=D2-1),--(A3:A808=A1),--(B3:B808A2),--(B3:B808<BA3))

--


Regards,


Peo Sjoblom

"JLewis" wrote in message
...
Okay, this works great for criteria that references the
data
I'm
counting.
What about criteria based on another column? I have
columns
for
division,
rating, name, and for holes 1-18. I also have the first
row
that
gives
the
hole pars. I have a formula for reporting birdies, bogeys,
etc.
But I'd
like
to more criteria to filter the data. Here is what I have
in
mind, but
it
will
not work.
=COUNTIFS(D3:D808,D2-1,A3:A808,"="&A1,B3:B808,""&A2,B3:B808,"<"&BA3
"D2-1" references the par for the hole less one.
Essentially
a
birdie.
A1,A2,A3 cells are for defining the filter criteria. This
should
give
me
all
of the birdies for the players' scores that fit the
criteria,
shouldn't
it? I
like how you used the formula for being able to use an
earlier
form of
Excel.
I don't know that it would be necessary, but it is still
nice
to
know
that
I
could.

"Peo Sjoblom" wrote:

Yes it would, however I would rather
use this instead

=SUMPRODUCT(--(F3:F203A1),--(F3:F203<A2))


since it is compatible with earlier versions


--


Regards,


Peo Sjoblom