Amending an existing formual to improve grammar
 

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.

Amending an existing formual to improve grammar
 

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.

Amending an existing formual to improve grammar
 

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.

Amending an existing formual to improve grammar
 

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

Amending an existing formual to improve grammar
 

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.

Amending an existing formual to improve grammar
 

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