#ERROR on =IIf(DCount […] with Alphanumeric field
vba,ms-access,access-vba,dcount
First, break out the DCount piece and work on that by itself. It sounds like your COMMENTS.PROJECTID field is text datatype, so add quotes before and after the value you concatenate into the third DCount argument (Criteria): DCount("*", "COMMENTS", "[PROJECTID] = '" & [PROJECTID] & "'") After you have that...
Excel's DCOUNT function doesn't return the correct result
@gtwebb has a simple solution but just adding an A is also simple: =DCOUNTA(O5:Q19,"COLOR",R1:T2) and will not produce the same result as counting NO if C2 values in the relevant range are matched against one or more blanks for COLOR....
Counting Unique Occurences in Excel
There would appear to be 7 unique Sensors in your sample data (13113, 13144, 14018, 13052, 13018, 13019, 13049) for Visit ID=1, not 6. =SUMPRODUCT((B2:B29=1)/(COUNTIFS(B2:B29, 1, A2:A29, A2:A29&"")+(B2:B29<>1))) =SUMPRODUCT((B2:B29=2)/(COUNTIFS(B2:B29, 2, A2:A29, A2:A29&"")+(B2:B29<>2))) =SUMPRODUCT((B2:B29=3)/(COUNTIFS(B2:B29, 3, A2:A29, A2:A29&"")+(B2:B29<>3))) It would probably be best to put the Visit ID into a cell and...