Page 1 of 1
county
Posted: Tue Sep 14, 2021 3:43 pm
by 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
Re: county
Posted: Tue Sep 14, 2021 3:44 pm
by sna
Here's the attached
Re: county
Posted: Tue Sep 14, 2021 3:55 pm
by eyepop99
Just use counta instead
C3 =COUNTA(A3:A14)
Re: county
Posted: Tue Sep 14, 2021 5:35 pm
by norkaz
...
C3
=SUM(--IFERROR(YEAR(A2:A14)<>1900,0))
Ctrl + Shift + Enter
Norkaz
Re: county
Posted: Tue Sep 14, 2021 7:29 pm
by norkaz
norkaz wrote: Tue Sep 14, 2021 5:35 pm
...
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
Re: county
Posted: Tue Sep 14, 2021 7:41 pm
by sna
If possible no helper column even office 365
Re: county
Posted: Tue Sep 14, 2021 8:17 pm
by snasui
Try with this formula,
=SUM(--(CELL("format",OFFSET(A2,ROW(A2:A14)-ROW(A2),0))="D1"))
Re: county
Posted: Wed Sep 15, 2021 6:38 am
by sna
Hi
I try putting formula cell C1 but it's not working.it returns #Value
Re: county
Posted: Wed Sep 15, 2021 6:53 am
by snasui
The formula above for O365 only. If you found error in this version, please attach the updated file again.
Re: county
Posted: Wed Sep 15, 2021 8:39 am
by sna
Ok.got it
I use office 2019,any formula can solve this?
Re: county
Posted: Wed Sep 15, 2021 9:04 am
by norkaz
...
B2
=CELL("format",A2)
C3
=COUNTIF(B2:B14,"D1")
Norkaz
Re: county
Posted: Wed Sep 15, 2021 1:19 pm
by Bo_ry
=SUM(--(CELL("format",INDIRECT(T(IF(1,"A"&ROW(A2:A14)))))="D1"))
Ctrl+Shift+Enter
=COUNTIFS(A2:A14,">10000")
Re: county
Posted: Wed Sep 15, 2021 1:59 pm
by sna
Thanks Bo_ry second formula works.
Once more if value in this column over 10000 this won't work fine.
Re: county
Posted: Wed Sep 15, 2021 2:34 pm
by 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
Re: county
Posted: Wed Sep 15, 2021 3:19 pm
by sna
I use office 2019 not M0365