Amending an existing formual to improve grammar

  • Thread starter Thread starter Colin Hayes
  • Start date Start date
C

Colin Hayes

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.
 
="Totals ("&COUNTA(C6:C17)&" Months)" [....]
Can anyone suggest an amendment so that this will read
"Totals (1 Month)" and accommodate the single entry?
Click to expand...

="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.
 
="Totals ("&COUNTA(C6:C17)&" Months)" [....]
Can anyone suggest an amendment so that this will read
"Totals (1 Month)" and accommodate the single entry?
Click to expand...

="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.
Click to expand...


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
 
="Totals (" & COUNTA(C6:C17)
& IF(COUNTA(C6:C17)=1," Month)"," Months)")
Click to expand...
[....]
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.
Click to expand...

=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.
 
="Totals (" & COUNTA(C6:C17)
& IF(COUNTA(C6:C17)=1," Month)"," Months)")
Click to expand...
[....]
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.
Click to expand...

=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.
Click to expand...


Hi

OK that's solved it perfectly.

Thanks again for that - very impressive.

Thanks too for the SUMPRODUCT tip.

Best Wishes
 
Back
Top