Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP or COUNT IF?
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")*(Analys is!$H$3:$H$2000="excellent=1"),Analysis!$Z$3:$Z$20 00)) I would then apply the formula to a list of dates I need to know the results for. Hope you can help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP or COUNT IF?
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 Rob-WNS wrote: 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")*(Analys is!$H$3:$H$2000="excellent=1"),Analysis!$Z$3:$Z$20 00)) I would then apply the formula to a list of dates I need to know the results for. Hope you can help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP or COUNT IF?
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) "Rob-WNS" wrote in message ... 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")*(Analys is!$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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP or COUNT IF?
Many thanks guys.
It works a treat!!! "vezerid" wrote: 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 Rob-WNS wrote: 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")*(Analys is!$H$3:$H$2000="excellent=1"),Analysis!$Z$3:$Z$20 00)) I would then apply the formula to a list of dates I need to know the results for. Hope you can help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotals by count | Excel Discussion (Misc queries) | |||
Count consecutive dates only | Excel Discussion (Misc queries) | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions |