HELP!! DCOUNTA, ISBLANK and LEN

[LinaVa]

Hi all,

I need to calculate in the H6 cell the number of orders in which the postcode is not available, by using the DCOUNTA database function. I got a task to use DCOUNTA and not a COUNTBLANK function...
In criterion I may either use the ISBLANK function or look for the text with a zero length by using the LEN function.


Please help...

File attached.

addresses.xlsx

[Richard Buttrey]

Hi,

One way in H3

Please Login or Register  to view this content.

[JohnTopley]

For post code the formula should be :

=COUNTBLANK(OFFSET($A$1,0,3,COUNTA(A:A),1))

[Glenn Kennedy]

Or this (which does use DCOUNTA)

=COUNTA(A:A)-(DCOUNTA(A1:F30,D1,D2:D30)+1)

[LinaVa]

It should be something like =DCOUNTA(A1:F30,C1,H2:H3) with criteria in H2:H3 is =ISBLANK($C$2)

but it gives me non-blanks. it should give me blanks as criteria true is for blanks. that's the confusion.. i must be doing a very small silly mistake in here.

[mahju]

Hi

Try this

Please Login or Register  to view this content.

and in criteria H10:H11

=""

See attached

[LinaVa]

Thanks everyone, but it still doesn't give me a correct answer

[Richard Buttrey]

Hi,

You have several answers. Which one are you saying doesn't give you the correct answer?
Upload the workbook so that we can check that you have correctly implemented one of the suggestions given.

[Glenn Kennedy]

Perhaps this - where H2 is an array formula... entered with CTRL-SHIFT-ENTER

[mahju]

Hi

Use this

Please Login or Register  to view this content.

Leave H11 blank

Regards