I have a spreadsheet with golf tournament data. I have an analysis sheet
showing the counts of various scores on each hole. (10 Birdies, 5 Aces, etc).
Now I want to use criteria fields (cells) to filter the data based on another
column. For instance, I want to see the data analysis on players with a
rating between 950 and 1000. I must have cells for the user to input the
criteria he/she wants and not actually filter the data, since more than one
analysis can be made at the same time. (multiple analysis) I already have a
formula for counting the various scores. I have used COUNTIF for the
analysis. I tried using COUNTIFS, but the criteria seems to be only on the
column of data that is to be counted. I need to filter using another column
as well.

Use Advanced Filter
For details look for "Filter by using advanced criteria " in Excel Help

"JLewis" wrote:

I have a spreadsheet with golf tournament data. I have an analysis sheet
showing the counts of various scores on each hole. (10 Birdies, 5 Aces, etc).
Now I want to use criteria fields (cells) to filter the data based on another
column. For instance, I want to see the data analysis on players with a
rating between 950 and 1000. I must have cells for the user to input the
criteria he/she wants and not actually filter the data, since more than one
analysis can be made at the same time. (multiple analysis) I already have a
formula for counting the various scores. I have used COUNTIF for the
analysis. I tried using COUNTIFS, but the criteria seems to be only on the
column of data that is to be counted. I need to filter using another column
as well.

Hi,

Since you are using 2007, COUNTIFS does support multiple criteria columns
(ranges). Suppose you want to count the number of players with ratings
between 950 and 1000. Suppose the rating is in column D1:D100

=COUNTIFS(D1:D100,"=950",D1:D100,"<=1000")

would do it. You also asked about the fact that for countifs "the criteria
seems to be only on the column of data that is to be counted" I need an
example of this to understand the problem, but lets suppose you want to count
the number of golfers with ratings above 1000 who are female. The the data
on gender is in column C, using the ranges mentioned in the first example,
then

=COUNTIFS(D1:D100,"1000",C1:C100,"Female")

Since you said you don't want to actually filter the data, if you are using
Advanced Filter you must choose copy to a new location, otherwise you will be
filtering the data.

FYI - COUNTIFS and SUMIFS and AVERAGEIFS and their single criteria brothers
are powerful but limited, to get around these limit consider the D-FUNCTIONS.
These functions are really unlimited in their power. Their basic syntax is
=DSUM(Database,Column,Criteria)
where database is your data with one row of titles, column is the column you
want to SUM, and criteria is a spreadsheet range where your user can enter as
complicated a criteria as they want. It takes some work learning these but
its worth it. Besides DSUM there is DCOUNT, DMAX, DMIN, ...

If this helps please click the Yes button.


--
Thanks,
Shane Devenshire


"JLewis" wrote:

I have a spreadsheet with golf tournament data. I have an analysis sheet
showing the counts of various scores on each hole. (10 Birdies, 5 Aces, etc).
Now I want to use criteria fields (cells) to filter the data based on another
column. For instance, I want to see the data analysis on players with a
rating between 950 and 1000. I must have cells for the user to input the
criteria he/she wants and not actually filter the data, since more than one
analysis can be made at the same time. (multiple analysis) I already have a
formula for counting the various scores. I have used COUNTIF for the
analysis. I tried using COUNTIFS, but the criteria seems to be only on the
column of data that is to be counted. I need to filter using another column
as well.

Thanks for the info. I have tried using COUNTIFS, but I ran into trouble
trying to reference a cell for the criteria. I found a post that mentioned
using cell references for SUMIF and COUNTIF. The example was for SUMIF, but I
think I understand the bulk of the idea. So if, I used: =COUNTIFS(F3:F203,
""&A1,F3:F203,"<"&A2) would this work? I will try it later when I get back
to my project.
Thanks again,
Jennifer

"ShaneDevenshire" wrote:

Hi,

Since you are using 2007, COUNTIFS does support multiple criteria columns
(ranges). Suppose you want to count the number of players with ratings
between 950 and 1000. Suppose the rating is in column D1:D100

=COUNTIFS(D1:D100,"=950",D1:D100,"<=1000")

would do it. You also asked about the fact that for countifs "the criteria
seems to be only on the column of data that is to be counted" I need an
example of this to understand the problem, but lets suppose you want to count
the number of golfers with ratings above 1000 who are female. The the data
on gender is in column C, using the ranges mentioned in the first example,
then

=COUNTIFS(D1:D100,"1000",C1:C100,"Female")

Since you said you don't want to actually filter the data, if you are using
Advanced Filter you must choose copy to a new location, otherwise you will be
filtering the data.

FYI - COUNTIFS and SUMIFS and AVERAGEIFS and their single criteria brothers
are powerful but limited, to get around these limit consider the D-FUNCTIONS.
These functions are really unlimited in their power. Their basic syntax is
=DSUM(Database,Column,Criteria)
where database is your data with one row of titles, column is the column you
want to SUM, and criteria is a spreadsheet range where your user can enter as
complicated a criteria as they want. It takes some work learning these but
its worth it. Besides DSUM there is DCOUNT, DMAX, DMIN, ...

If this helps please click the Yes button.


--
Thanks,
Shane Devenshire


"JLewis" wrote:

I have a spreadsheet with golf tournament data. I have an analysis sheet
showing the counts of various scores on each hole. (10 Birdies, 5 Aces, etc).
Now I want to use criteria fields (cells) to filter the data based on another
column. For instance, I want to see the data analysis on players with a
rating between 950 and 1000. I must have cells for the user to input the
criteria he/she wants and not actually filter the data, since more than one
analysis can be made at the same time. (multiple analysis) I already have a
formula for counting the various scores. I have used COUNTIF for the
analysis. I tried using COUNTIFS, but the criteria seems to be only on the
column of data that is to be counted. I need to filter using another column
as well.

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

"JLewis" wrote in message
...
Thanks for the info. I have tried using COUNTIFS, but I ran into trouble
trying to reference a cell for the criteria. I found a post that mentioned
using cell references for SUMIF and COUNTIF. The example was for SUMIF,
but I
think I understand the bulk of the idea. So if, I used: =COUNTIFS(F3:F203,
""&A1,F3:F203,"<"&A2) would this work? I will try it later when I get
back
to my project.
Thanks again,
Jennifer

"ShaneDevenshire" wrote:

Hi,

Since you are using 2007, COUNTIFS does support multiple criteria columns
(ranges). Suppose you want to count the number of players with ratings
between 950 and 1000. Suppose the rating is in column D1:D100

=COUNTIFS(D1:D100,"=950",D1:D100,"<=1000")

would do it. You also asked about the fact that for countifs "the
criteria
seems to be only on the column of data that is to be counted" I need an
example of this to understand the problem, but lets suppose you want to
count
the number of golfers with ratings above 1000 who are female. The the
data
on gender is in column C, using the ranges mentioned in the first
example,
then

=COUNTIFS(D1:D100,"1000",C1:C100,"Female")

Since you said you don't want to actually filter the data, if you are
using
Advanced Filter you must choose copy to a new location, otherwise you
will be
filtering the data.

FYI - COUNTIFS and SUMIFS and AVERAGEIFS and their single criteria
brothers
are powerful but limited, to get around these limit consider the
D-FUNCTIONS.
These functions are really unlimited in their power. Their basic syntax
is
=DSUM(Database,Column,Criteria)
where database is your data with one row of titles, column is the column
you
want to SUM, and criteria is a spreadsheet range where your user can
enter as
complicated a criteria as they want. It takes some work learning these
but
its worth it. Besides DSUM there is DCOUNT, DMAX, DMIN, ...

If this helps please click the Yes button.


--
Thanks,
Shane Devenshire


"JLewis" wrote:

I have a spreadsheet with golf tournament data. I have an analysis
sheet
showing the counts of various scores on each hole. (10 Birdies, 5 Aces,
etc).
Now I want to use criteria fields (cells) to filter the data based on
another
column. For instance, I want to see the data analysis on players with a
rating between 950 and 1000. I must have cells for the user to input
the
criteria he/she wants and not actually filter the data, since more than
one
analysis can be made at the same time. (multiple analysis) I already
have a
formula for counting the various scores. I have used COUNTIF for the
analysis. I tried using COUNTIFS, but the criteria seems to be only on
the
column of data that is to be counted. I need to filter using another
column
as well.

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

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