How to count table non blank cells in columns?

[Jakboi]

Hello,

I am trying to count column items that are not blank, how would I do that?

Where the "?" are I would like for it to tally up the non blank cells in the columns.

=Sum(IIf([CLASSIFICATION]="Preliminary" And [PROD1]="?" Or [PROD2]="?" Or [PROD3]="?",1,0))

Thanks.

 

[mhartman]

Hello

You want use the "DSUM" function. It allows criteria to used to ferret out specific items.

Regards
Mark

 

[Matt Greatorex]

If you don't want a total (i.e. all cell values added together), you could use DCount, rather than DSum e.g.

=DCount("CLASSIFICATION]","[I]table name[/I]","([CLASSIFICATION]='Preliminary' And ([PROD1]='?' Or [PROD2]='?' Or [PROD3]='?'"))

This counts the number of Classification values for all records matching the criteria you gave.
DSum would give you a total of a range of numeric values.

To just count the non-blanks, this should work, but I'm assuming you also need the other criteria you gave?

=DCount("CLASSIFICATION]","[I]table name[/I]","Not IsNull([CLASSIFICATION])")

 

[Jakboi]

Thanks for the reply. I guess I am a little confused still as I have tried several things with no luck.

I have 4 columns in this table that I am looking for help with. I want to count the nonblank items in columns PROD1, PROD2, and PROD3. I only want them counted if the column CLASSIFICATION = Preliminary.

=DCount("CLASSIFICATION]","table name","Not IsNull([CLASSIFICATION])"And [PROD1]="?" Or [PROD2]="?" Or [PROD3]="?",1,0)

Is it something like that? What do I put in for the "?", is it Not Isnull?

I do want them just counted and not summed.

Thank you.

 

[Matt Greatorex]

I think this is about right.

=DCount("[CLASSIFICATION]","table name","[CLASSIFICATION] = Preliminary' And (Not IsNull([PROD1]) Or Not IsNull([PROD2]) Or Not IsNull([PROD3])")