excel 2000 - array formulas
 

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?

excel 2000 - array formulas
 

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?

excel 2000 - array formulas
 

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?

excel 2000 - array formulas
 

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?

excel 2000 - array formulas
 

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?

excel 2000 - array formulas
 

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?

excel 2000 - array formulas
 

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?

excel 2000 - array formulas
 

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?

excel 2000 - array formulas
 

=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?

excel 2000 - array formulas
 

=SUMPRODUCT((D2:D2754=D2758)*(E2:E2754=E2758))

Year Semi Qtr. Month Recorded
2005 1 3 August 2005
307 0 81 29

The following works for quarter 3.
=SUMPRODUCT(((D2:D2754=2005)*(E2:E2754=1)*(F2:F275 4=3)))

I'm hoping to change the year, semi, and quarter and watch formula results
change without changing the formula.

Thanks for your help.

"willwonka" wrote:

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?

excel 2000 - array formulas
 

$D$2758 $E$2758 ?


"cdsta" wrote in message
...
=SUMPRODUCT((D2:D2754=D2758)*(E2:E2754=E2758))

Year Semi Qtr. Month Recorded
2005 1 3 August 2005
307 0 81 29

The following works for quarter 3.
=SUMPRODUCT(((D2:D2754=2005)*(E2:E2754=1)*(F2:F275 4=3)))

I'm hoping to change the year, semi, and quarter and watch formula results
change without changing the formula.

Thanks for your help.

"willwonka" wrote:

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?

excel 2000 - array formulas
 

Hi

If cells D2758, E2758, F2758 hold the numeric values 2005, 1 , 3
respectively, then you should see exactly the same result.
Varying the values in those cells, will then alter the results shown.

If you are copying the cells with the formulae to other cells, you
should make the ranges and the comparative cells absolute.

=SUMPRODUCT(($D$2:$D$2754=$D2758)*
($E2$:$E$2754=$E$2758)*($F$2:$F$2754=$F$2758))

--
Regards

Roger Govier


"cdsta" wrote in message
...
=SUMPRODUCT((D2:D2754=D2758)*(E2:E2754=E2758))

Year Semi Qtr. Month Recorded
2005 1 3 August 2005
307 0 81 29

The following works for quarter 3.
=SUMPRODUCT(((D2:D2754=2005)*(E2:E2754=1)*(F2:F275 4=3)))

I'm hoping to change the year, semi, and quarter and watch formula
results
change without changing the formula.

Thanks for your help.

"willwonka" wrote:

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?

excel 2000 - array formulas
 

Thanks. Unfortunately I've tried this and it still isn't working. I've
tried making the values absolute and it just won't take the cell reference
but will take the numerical value in the formula. Thanks again.

Daniel

"Roger Govier" wrote:

Hi

If cells D2758, E2758, F2758 hold the numeric values 2005, 1 , 3
respectively, then you should see exactly the same result.
Varying the values in those cells, will then alter the results shown.

If you are copying the cells with the formulae to other cells, you
should make the ranges and the comparative cells absolute.

=SUMPRODUCT(($D$2:$D$2754=$D2758)*
($E2$:$E$2754=$E$2758)*($F$2:$F$2754=$F$2758))

--
Regards

Roger Govier


"cdsta" wrote in message
...
=SUMPRODUCT((D2:D2754=D2758)*(E2:E2754=E2758))

Year Semi Qtr. Month Recorded
2005 1 3 August 2005
307 0 81 29

The following works for quarter 3.
=SUMPRODUCT(((D2:D2754=2005)*(E2:E2754=1)*(F2:F275 4=3)))

I'm hoping to change the year, semi, and quarter and watch formula
results
change without changing the formula.

Thanks for your help.

"willwonka" wrote:

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?

excel 2000 - array formulas
 

I tried. Just isn't working. I've tried reformatting the data to general
from numerical and vice versa but still no success. Thanks for the
suggestion. It lets me know I'm on the right track.

Daniel

"David F Cox" wrote:

$D$2758 $E$2758 ?


"cdsta" wrote in message
...
=SUMPRODUCT((D2:D2754=D2758)*(E2:E2754=E2758))

Year Semi Qtr. Month Recorded
2005 1 3 August 2005
307 0 81 29

The following works for quarter 3.
=SUMPRODUCT(((D2:D2754=2005)*(E2:E2754=1)*(F2:F275 4=3)))

I'm hoping to change the year, semi, and quarter and watch formula results
change without changing the formula.

Thanks for your help.

"willwonka" wrote:

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?

excel 2000 - array formulas
 

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?