county

[sna]

Hi Dear
I need your help to count date in a range containing text, numbers and dates.
I can use count but it contains also numbers so it is not fine.

Best Wishes

[sna]

Here's the attached

[eyepop99]

Just use counta instead
C3 =COUNTA(A3:A14)

[norkaz]

...

C3

=SUM(--IFERROR(YEAR(A2:A14)<>1900,0))

Ctrl + Shift + Enter

Norkaz

[norkaz]

...

C3

=SUM(--IFERROR(YEAR(A2:A14)<>1900,0))

Ctrl + Shift + Enter

Norkaz

Hi Guy,

The formula that I suggested above might not work properly once the other numbers are the same number of the DATE VALUES.

On the other hand, this problem can be solved by the other method related to Excel 365.

For the previous version, I don't think we cannot avoid the helper column, then do you prefer it?

Norkaz

[sna]

If possible no helper column even office 365

[snasui]

Try with this formula,

=SUM(--(CELL("format",OFFSET(A2,ROW(A2:A14)-ROW(A2),0))="D1"))

[sna]

Hi
I try putting formula cell C1 but it's not working.it returns #Value

[snasui]

The formula above for O365 only. If you found error in this version, please attach the updated file again.

[sna]

Ok.got it
I use office 2019,any formula can solve this?

[norkaz]

...

B2
=CELL("format",A2)

C3
=COUNTIF(B2:B14,"D1")

Norkaz

[Bo_ry]

=SUM(--(CELL("format",INDIRECT(T(IF(1,"A"&ROW(A2:A14)))))="D1"))

Ctrl+Shift+Enter


=COUNTIFS(A2:A14,">10000")

[sna]

Thanks Bo_ry second formula works.
Once more if value in this column over 10000 this won't work fine.

[Bo_ry]

Both formulas work fine. please check the attached file.

After 369 posts, seem like you still don't know how to press Ctrl+Shift+Enter.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

https://support.microsoft.com/en-us/off ... f793412dfa

[sna]

I use office 2019 not M0365