VLOOKUP or COUNT IF?

R

Rob-WNS

I am trying to sort through a survey of results.
each row starts with the date and the information is gathered in a pre set
values. i.e good, verygood etc.(Note there are several rows with the same
date)

I have tried V look up yet I need the search criteria of the look up to be
able to cope with the dates being the same.

Altenativly I used the COUNTIF option, yet I need to know how I can make the
"A38949" reference below. to change with the value I have in the results list.

(Note A38949 is the date with an A added so that Count IF function
recognised a date.)

=COUNT(IF((Analysis!$A$3:$A$2000="A38949")*(Analysis!$H$3:$H$2000="excellent=1"),Analysis!$Z$3:$Z$2000))

I would then apply the formula to a list of dates I need to know the results
for.

Hope you can help.
 
V

vezerid

Rob,

first of all, appending an A before a date does not make it a date. The
number 38949 IS a date, only not formatted as such. If cell A1 contains
Aug 8, 2006, then the formula:

=A1=38949

will return TRUE.

Conditional counting can be done with COUNT or with SUMPRODUCT.
Assuming your date is in K1 and rating (e.g. excellent) in K2, then

=SUMPRODUCT((Analysis!$A$3:$A$2000=$K$1)*(Analysis!$H$3:$H$2000=K2))

This will count how many records have the date and excellent.

What do you have in column Z? Does it affect counting conditions?

HTH
Kostis Vezerides
 
B

Bob Phillips

I think that you mean

=SUMPRODUCT(--(Analysis!$A$3:$A$2000="A38949"),--(Analysis!$H$3:$H$2000="exc
ellent=1"),Analysis!$Z$3:$Z$2000)


--
HTH

Bob Phillips

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

Rob-WNS

Many thanks guys.

It works a treat!!!

vezerid said:
Rob,

first of all, appending an A before a date does not make it a date. The
number 38949 IS a date, only not formatted as such. If cell A1 contains
Aug 8, 2006, then the formula:

=A1=38949

will return TRUE.

Conditional counting can be done with COUNT or with SUMPRODUCT.
Assuming your date is in K1 and rating (e.g. excellent) in K2, then

=SUMPRODUCT((Analysis!$A$3:$A$2000=$K$1)*(Analysis!$H$3:$H$2000=K2))

This will count how many records have the date and excellent.

What do you have in column Z? Does it affect counting conditions?

HTH
Kostis Vezerides
Click to expand...
 
Top