I have a survery with 10 answers. How can I write a conditional statement
that will display the number of times a certain answer comes up within a week
that I designate. Entries are added all the time do I don't want it to be
dependent on row position. I've used a COUNTIF statement to count how many
times an answer is given in a certain column but I don't want to have to
designate what that column is every time since I will be adding more rows on
a regular basis.

In regular ol' english, I need it to:
Search a column of dates (sorted in ascending order) and find the rows (each
survey) that fall between 2 dates that I specify in 2 certain cells. Once it
finds the rows between those dates, count the number of times a certain
answer was given in another column.

Try something like this:

With
Dates in A1:A100
Survey answers in B1:B100

F1: (StartDate)
F2: (EndDate)

G1: (survey value to count)
H1: =SUMPRODUCT((A1:A100=F1)*(A1:A100<=F2)*(B1:B100=G 1))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"OlYellerSnow" wrote:

I have a survery with 10 answers. How can I write a conditional statement
that will display the number of times a certain answer comes up within a week
that I designate. Entries are added all the time do I don't want it to be
dependent on row position. I've used a COUNTIF statement to count how many
times an answer is given in a certain column but I don't want to have to
designate what that column is every time since I will be adding more rows on
a regular basis.

In regular ol' english, I need it to:
Search a column of dates (sorted in ascending order) and find the rows (each
survey) that fall between 2 dates that I specify in 2 certain cells. Once it
finds the rows between those dates, count the number of times a certain
answer was given in another column.

Have you tried DCOUNT(database,Field,Criteria)

"OlYellerSnow" wrote:

I have a survery with 10 answers. How can I write a conditional statement
that will display the number of times a certain answer comes up within a week
that I designate. Entries are added all the time do I don't want it to be
dependent on row position. I've used a COUNTIF statement to count how many
times an answer is given in a certain column but I don't want to have to
designate what that column is every time since I will be adding more rows on
a regular basis.

In regular ol' english, I need it to:
Search a column of dates (sorted in ascending order) and find the rows (each
survey) that fall between 2 dates that I specify in 2 certain cells. Once it
finds the rows between those dates, count the number of times a certain
answer was given in another column.

Hey! The SUMPRODUCT method worked perfectly. I was pulling my hair out
over that one. I didn't think it would be so easy. Thanks for the
help.
Zach


--
OlYeller21
------------------------------------------------------------------------
OlYeller21's Profile: http://www.excelforum.com/member.php...o&userid=36309
View this thread: http://www.excelforum.com/showthread...hreadid=560823