Count items when specific text and date criteria are met

[javamom]

Trying to summarize survey data and need to report the number of
specific text responses given before a date and report those given
on/after a date. The date and response information is contained in one
worksheet while the summary is displayed in another worksheet within
the same workbook (Excel 2000, Win XP). I've named the data ranges in
an attempt to make this easier. For example:

"TDate" "TQ7"
10/11/2004 q7= very easy
11/12/2005 q7= fairly easy
12/14/2004 q7= difficult

I'm trying this formula to get a count of all the question 7 responses
on/after the date:
=SUMPRODUCT(("Tdate">=DATEVALUE("01/25/2005"))*("TQ7"<>""))

I'm trying this formula to count specific responses by date:
=SUMPRODUCT(("Tdate">=DATEVALUE("01/25/2005"))*(COUNTIF(TQ7,"*very
easy"))

To make things even stranger, I've "created" the date for "TDate" by
calculating information from 3 other columns using the following
formula (the date information is buried in a field with lots of other
stuff so this is my attempt to isolate it):
=DATE(D2,B2,C2)

Not sure if this date approach causes a problem with calculations or if
my formulas are just completely off base.

Help greatly appreciated! Trish

 

[William Horton]

The below formula works for me. Typed exactly how shown.

=SUMPRODUCT(--(Tdate>DATEVALUE("01/25/2005")),--(TQ7<>""))

 

[javamom]

Thanks for the response! The above returns a #NUM! error for me.

Do you think that calculating TDate with =DATE(D2,B2,C2) could be
causing a problem?

 

[William Horton]

No. I calculated TDate with the DATE function as well to rule that out. I
took the exact sample that you gave...

"TDate" "TQ7"
10/11/2004 q7= very easy
11/12/2005 q7= fairly easy
12/14/2004 q7= difficult

I then applied the formula
=SUMPRODUCT(--(Tdate>DATEVALUE("01/25/2005")),--(TQ7<>""))

I got the correct answer of 1.

Maybe try it without the names.

 

[Bob Phillips]

The #NUM could be caused by TDate not being the same size as TQ7. Ranges
must be the same size in SP.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[javamom]

This is probably a stupid question, but by "same size" do you mean
defined range or number of filled cells?

TDate range is: 'trainer data'!$E:$E
TQ7 range is: 'trainer data'!$U:$U

There will be some rows with blank cells in TQ7 but data in TDate. Does
that make sense?

Thanks! Trish

 

[Bob Phillips]

Ah, you cannot use full columns with SP, it must be a specific range. I
don't mean filled cells, I mean defined range. It doesn't have to be the
same rows/columns, but they do have to have the same number of rows/columns.
So you should define TDate as 'trainer data'!$E1:$E1000 as an example, or
maybe dynamic

OFFSET('trainer data'!$E$1,,,COUNTA('trainer data'!$E:$E),1)

danger with the latter is ensuring that TDate and TQ7 are the same size, so
maybe use this for TQ7

OFFSET('trainer data'!$U$1,,,COUNTA('trainer data'!$E:$E),1)

this will force them to keep synchronised in size

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[javamom]

Ah ha! Good to know about full columns. Let me play around with your
suggestion for specifying the ranges. These are large (thousands of
lines) and may get somewhat larger.

Will share if I get it working. Thanks! Trish

 

[javamom]

The formula does work -- turns out there was a problem in one of the
cells with the date data and that was throwing the whole thing off.
Thanks so much for all the help! Trish