Using Excel 2000 - I have 2700+ rows of data to analyze, spreadsheet has 15
columns of data. I want to count the rows which meet particular criteria for
the "year" column (number formatted cells) and and the "Semi" column, also a
number formatted cells. I just want a count of the # of rows in which say,
year = 2005, semi = 1. I have not been able to get a "IF" formula to work
nor an array formula. Any ideas? The countif function works when I only
seek one set of data, like "2005", but selecting cell ranges and nesting
functions is not working. Any Ideas?

Try Sumproduct:

=sumproduct((A1:A2700=2005)*(b1:b2700=1))

Whe

Column A is your Year column and
Column B is your Semi column.

HTH.


cdsta wrote:
Using Excel 2000 - I have 2700+ rows of data to analyze, spreadsheet has 15
columns of data. I want to count the rows which meet particular criteria for
the "year" column (number formatted cells) and and the "Semi" column, also a
number formatted cells. I just want a count of the # of rows in which say,
year = 2005, semi = 1. I have not been able to get a "IF" formula to work
nor an array formula. Any ideas? The countif function works when I only
seek one set of data, like "2005", but selecting cell ranges and nesting
functions is not working. Any Ideas?

Thanks. It works but I have another question. Is there a way for me to
refer to a cell that has 2005 in it so that I don't have to manually change
this formula when I want to look at data for 2004?

"willwonka" wrote:

Try Sumproduct:

=sumproduct((A1:A2700=2005)*(b1:b2700=1))

Whe

Column A is your Year column and
Column B is your Semi column.

HTH.


cdsta wrote:
Using Excel 2000 - I have 2700+ rows of data to analyze, spreadsheet has 15
columns of data. I want to count the rows which meet particular criteria for
the "year" column (number formatted cells) and and the "Semi" column, also a
number formatted cells. I just want a count of the # of rows in which say,
year = 2005, semi = 1. I have not been able to get a "IF" formula to work
nor an array formula. Any ideas? The countif function works when I only
seek one set of data, like "2005", but selecting cell ranges and nesting
functions is not working. Any Ideas?

Sure thing. Just replace 2005 with the cell reference and you should
be good to go.

cdsta wrote:
Thanks. It works but I have another question. Is there a way for me to
refer to a cell that has 2005 in it so that I don't have to manually change
this formula when I want to look at data for 2004?

"willwonka" wrote:

Try Sumproduct:

=sumproduct((A1:A2700=2005)*(b1:b2700=1))

Whe

Column A is your Year column and
Column B is your Semi column.

HTH.


cdsta wrote:
Using Excel 2000 - I have 2700+ rows of data to analyze, spreadsheet has 15
columns of data. I want to count the rows which meet particular criteria for
the "year" column (number formatted cells) and and the "Semi" column, also a
number formatted cells. I just want a count of the # of rows in which say,
year = 2005, semi = 1. I have not been able to get a "IF" formula to work
nor an array formula. Any ideas? The countif function works when I only
seek one set of data, like "2005", but selecting cell ranges and nesting
functions is not working. Any Ideas?

Thats where things are getting messed up. When I replace 2005 with the cell
reference D2758, and the 1 with the cell reference E2758, the formula result
is 0. Do I need to format the cell that has the 2005, and the 1 in them,
differently? Do I need to put the cell references in as =value or =T nested
cells?

"willwonka" wrote:

Sure thing. Just replace 2005 with the cell reference and you should
be good to go.

cdsta wrote:
Thanks. It works but I have another question. Is there a way for me to
refer to a cell that has 2005 in it so that I don't have to manually change
this formula when I want to look at data for 2004?

"willwonka" wrote:

Try Sumproduct:

=sumproduct((A1:A2700=2005)*(b1:b2700=1))

Whe

Column A is your Year column and
Column B is your Semi column.

HTH.


cdsta wrote:
Using Excel 2000 - I have 2700+ rows of data to analyze, spreadsheet has 15
columns of data. I want to count the rows which meet particular criteria for
the "year" column (number formatted cells) and and the "Semi" column, also a
number formatted cells. I just want a count of the # of rows in which say,
year = 2005, semi = 1. I have not been able to get a "IF" formula to work
nor an array formula. Any ideas? The countif function works when I only
seek one set of data, like "2005", but selecting cell ranges and nesting
functions is not working. Any Ideas?

Should work... can you post your formula?

cdsta wrote:
Thats where things are getting messed up. When I replace 2005 with the cell
reference D2758, and the 1 with the cell reference E2758, the formula result
is 0. Do I need to format the cell that has the 2005, and the 1 in them,
differently? Do I need to put the cell references in as =value or =T nested
cells?

"willwonka" wrote:

Sure thing. Just replace 2005 with the cell reference and you should
be good to go.

cdsta wrote:
Thanks. It works but I have another question. Is there a way for me to
refer to a cell that has 2005 in it so that I don't have to manually change
this formula when I want to look at data for 2004?

"willwonka" wrote:

Try Sumproduct:

=sumproduct((A1:A2700=2005)*(b1:b2700=1))

Whe

Column A is your Year column and
Column B is your Semi column.

HTH.


cdsta wrote:
Using Excel 2000 - I have 2700+ rows of data to analyze, spreadsheet has 15
columns of data. I want to count the rows which meet particular criteria for
the "year" column (number formatted cells) and and the "Semi" column, also a
number formatted cells. I just want a count of the # of rows in which say,
year = 2005, semi = 1. I have not been able to get a "IF" formula to work
nor an array formula. Any ideas? The countif function works when I only
seek one set of data, like "2005", but selecting cell ranges and nesting
functions is not working. Any Ideas?

Check out Chip Pearson's website.
The URL below brings you to a page that shows you how to use multiple
criteria for both SumIf and CountIf.

http://www.cpearson.com/excel/array.htm

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"cdsta" wrote:

Using Excel 2000 - I have 2700+ rows of data to analyze, spreadsheet has 15
columns of data. I want to count the rows which meet particular criteria for
the "year" column (number formatted cells) and and the "Semi" column, also a
number formatted cells. I just want a count of the # of rows in which say,
year = 2005, semi = 1. I have not been able to get a "IF" formula to work
nor an array formula. Any ideas? The countif function works when I only
seek one set of data, like "2005", but selecting cell ranges and nesting
functions is not working. Any Ideas?

Thanks. I'll check this out.

"Gary L Brown" wrote:

Check out Chip Pearson's website.
The URL below brings you to a page that shows you how to use multiple
criteria for both SumIf and CountIf.

http://www.cpearson.com/excel/array.htm

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"cdsta" wrote:

Using Excel 2000 - I have 2700+ rows of data to analyze, spreadsheet has 15
columns of data. I want to count the rows which meet particular criteria for
the "year" column (number formatted cells) and and the "Semi" column, also a
number formatted cells. I just want a count of the # of rows in which say,
year = 2005, semi = 1. I have not been able to get a "IF" formula to work
nor an array formula. Any ideas? The countif function works when I only
seek one set of data, like "2005", but selecting cell ranges and nesting
functions is not working. Any Ideas?

=SUMPRODUCT(--(A2:A2700=2005),--(C2:C2700=1))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"cdsta" wrote in message
...
Using Excel 2000 - I have 2700+ rows of data to analyze, spreadsheet has
15
columns of data. I want to count the rows which meet particular criteria
for
the "year" column (number formatted cells) and and the "Semi" column, also
a
number formatted cells. I just want a count of the # of rows in which
say,
year = 2005, semi = 1. I have not been able to get a "IF" formula to work
nor an array formula. Any ideas? The countif function works when I only
seek one set of data, like "2005", but selecting cell ranges and nesting
functions is not working. Any Ideas?

Bob, If you look at the other responses I made to other posts about this
issue, you can see I'm pretty beat. But your solution WORKS!!! I don't know
why the -- characters make it work with absolute cell references but it
does!!! In adjoining cells I have =COUNTIF($D$2:$D$2754,$D$2758) working
for the year and it works fine for changing the year. So, since you seem to
have the answer I've spent 2 days on, will this also work for other data in
the spreadsheet even if the criteria is not a number? Also, can you tell me
or point me to a resource that explains the -- characters in the formula?

Thanks

Daniel

"Bob Phillips" wrote:

=SUMPRODUCT(--(A2:A2700=2005),--(C2:C2700=1))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"cdsta" wrote in message
...
Using Excel 2000 - I have 2700+ rows of data to analyze, spreadsheet has
15
columns of data. I want to count the rows which meet particular criteria
for
the "year" column (number formatted cells) and and the "Semi" column, also
a
number formatted cells. I just want a count of the # of rows in which
say,
year = 2005, semi = 1. I have not been able to get a "IF" formula to work
nor an array formula. Any ideas? The countif function works when I only
seek one set of data, like "2005", but selecting cell ranges and nesting
functions is not working. Any Ideas?