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 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"
wrote in message
...

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
,
joeu2004 writes
On May 29, 3:40*pm, Colin Hayes 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 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
,
joeu2004 writes
On May 29, 4:41*pm, Colin Hayes 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