Hi All

I use the following formula to count entries into C6:C17 , and say how
many months have been taken up :

="Totals ("&COUNTA(C6:C17)&" Months)"

The syntax is clearly wrong when the there is only one entry , reading
"Totals (1 Months)".

Can anyone suggest an amendment so that this will read "Totals (1
Month)" and accommodate the single entry?

Grateful for any help.

On May 29, 3:40*pm, Colin Hayes <Co...@chayes.demon.co.uk> wrote:
> ="Totals ("&COUNTA(C6:C17)&" Months)"
[....]
> Can anyone suggest an amendment so that this will read
> "Totals (1 Month)" and accommodate the single entry?

="Totals (" & COUNTA(C6:C17)
& IF(COUNTA(C6:C17)=1," Month)"," Months)")

Note that that might display "Totals (0 Months)". That is
syntactically correct. But I wonder if you would prefer something
else entirely in that case.

Another way...
="Totals ("&COUNTA(C6:C17)&MID(" Months",1,7-(COUNTA(C6:C17)=1))&")"

--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(free and commercial excel programs)





"Colin Hayes" <(E-Mail Removed)>
wrote in message
news:(E-Mail Removed)...
>
> Hi All
>
> I use the following formula to count entries into C6:C17 , and say how many months have been taken
> up :
>
> ="Totals ("&COUNTA(C6:C17)&" Months)"
>
> The syntax is clearly wrong when the there is only one entry , reading "Totals (1 Months)".
>
> Can anyone suggest an amendment so that this will read "Totals (1 Month)" and accommodate the
> single entry?
>
> Grateful for any help.

In article
<a11b52bc-9e0c-4c0e-914c-(E-Mail Removed)>,
joeu2004 <(E-Mail Removed)> writes
>On May 29, 3:40*pm, Colin Hayes <Co...@chayes.demon.co.uk> wrote:
>> ="Totals ("&COUNTA(C6:C17)&" Months)"
>[....]
>> Can anyone suggest an amendment so that this will read
>> "Totals (1 Month)" and accommodate the single entry?
>
>="Totals (" & COUNTA(C6:C17)
>& IF(COUNTA(C6:C17)=1," Month)"," Months)")
>
>Note that that might display "Totals (0 Months)". That is
>syntactically correct. But I wonder if you would prefer something
>else entirely in that case.


HI

OK thanks for getting back. This works perfectly first time , and solves
the issue.

I agree that "Totals (0 Months)" is correct but looks odd. I suppose an
improvement would be if it just said "Totals" , or better still if it
were blank where entries are yet to be made in the cells C6:C17. I think
some conditional formatting could fix this.

Anyway thanks again for your time and expertise.

Best Wishes

On May 29, 4:41*pm, Colin Hayes <Co...@chayes.demon.co.uk> wrote:
> >="Totals (" & COUNTA(C6:C17)
> >& IF(COUNTA(C6:C17)=1," Month)"," Months)")
[....]
> I agree that "Totals (0 Months)" is correct but looks odd.
> I suppose an improvement would be if it just said "Totals",
> or better still if it were blank where entries are yet to
> be made in the cells C6:C17.

=IF(COUNTA(C6:C17)=0, "",
"Totals (" & COUNTA(C6:C17)
& IF(COUNTA(C6:C17)=1," Month)"," Months)"))

Caveat: If any of C6:C17 displays the null string (""), as this
formula does sometimes, COUNTA(C6:C17) will not be zero. If you would
like null strings not to be counted (because the cell looks blank),
try:

=IF(SUMPRODUCT(--(C6:C17<>""))=0, "",
"Totals (" & SUMPRODUCT(--(C6:C17<>""))
& IF(COUNTA(C6:C17)=1," Month)"," Months)"))

The double-negative (--) converts TRUE and FALSE to 1 and 0, which
SUMPRODUCT requires.

In article
<3ca5afc3-7a7c-4e6d-8860-(E-Mail Removed)>,
joeu2004 <(E-Mail Removed)> writes
>On May 29, 4:41*pm, Colin Hayes <Co...@chayes.demon.co.uk> wrote:
>> >="Totals (" & COUNTA(C6:C17)
>> >& IF(COUNTA(C6:C17)=1," Month)"," Months)")
>[....]
>> I agree that "Totals (0 Months)" is correct but looks odd.
>> I suppose an improvement would be if it just said "Totals",
>> or better still if it were blank where entries are yet to
>> be made in the cells C6:C17.
>
>=IF(COUNTA(C6:C17)=0, "",
>"Totals (" & COUNTA(C6:C17)
>& IF(COUNTA(C6:C17)=1," Month)"," Months)"))
>
>Caveat: If any of C6:C17 displays the null string (""), as this
>formula does sometimes, COUNTA(C6:C17) will not be zero. If you would
>like null strings not to be counted (because the cell looks blank),
>try:
>
>=IF(SUMPRODUCT(--(C6:C17<>""))=0, "",
>"Totals (" & SUMPRODUCT(--(C6:C17<>""))
>& IF(COUNTA(C6:C17)=1," Month)"," Months)"))
>
>The double-negative (--) converts TRUE and FALSE to 1 and 0, which
>SUMPRODUCT requires.


Hi

OK that's solved it perfectly.

Thanks again for that - very impressive.

Thanks too for the SUMPRODUCT tip.

Best Wishes