excel 2000 - array formulas

[Guest]

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?

 

[willwonka]

Try Sumproduct:

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

Where:

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

HTH.

 

[Guest]

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,

 

[Guest]

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?

 

[Guest]

Thanks. I'll check this out.

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
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.

 

[willwonka]

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

 

[Guest]

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]

Should work... can you post your formula?

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?

 

[Bob Phillips]

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

--
HTH

Bob Phillips

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

 

[Guest]

=SUMPRODUCT((D22754=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(((D22754=2005)*(E2:E2754=1)*(F2:F2754=3)))

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

Thanks for your help.

 

[David F Cox]

$D$2758 $E$2758 ?

=SUMPRODUCT((D22754=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(((D22754=2005)*(E2:E2754=1)*(F2:F2754=3)))

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

Thanks for your help.

 

[Roger Govier]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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