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