=IF(VariousCell="Week",COUNTIF(Combined!U:U,CONCAT ENATE(A6,"Yes")),IF(VariousCell="Month",COUNTIF(Co mbined!X:X,CONCATENATE(A6,"Yes"))

This cell is linked to Various cell which is a drop down containing month,
week, year. If you select Week it counts data in a concatenated column which
is up until that week you have selected. This is possible as the weeks
contain numbers i.e. Week 01, Week 02 and excel is able to read the number
prior to the week you have selected. This is not possible with the month as
it is displayed as text November, December etc and excel cannot give this a
value. Does any oine know how to overcome this problem??????

You have posted this question all over the place. Instead of doing that why
not stick to your original thread where respondents have given their time
freely to anwer your question and await your response on whether their answer
worked.

Mike

"DaveKid" wrote:

=IF(VariousCell="Week",COUNTIF(Combined!U:U,CONCAT ENATE(A6,"Yes")),IF(VariousCell="Month",COUNTIF(Co mbined!X:X,CONCATENATE(A6,"Yes"))

This cell is linked to Various cell which is a drop down containing month,
week, year. If you select Week it counts data in a concatenated column which
is up until that week you have selected. This is possible as the weeks
contain numbers i.e. Week 01, Week 02 and excel is able to read the number
prior to the week you have selected. This is not possible with the month as
it is displayed as text November, December etc and excel cannot give this a
value. Does any oine know how to overcome this problem??????

Hi,

I suppose the range various cells contains numbers? With or without leading
0's? In other words is this text or is this numeric data?

You could array enter this version of your formula

=IF(VariousCell="Week",COUNTIF(Combined!U:U,A6&"Ye s")),IF(VariousCell=--(1&"Month"&0),COUNTIF(Combined!X:X,A6&"Yes"))

Where Month is the cell address containing the month spelled out. This will
return months 1 for January not 01 for January. I fave also replaced
CONCATENATE with & to make the formula shorter.
--
Thanks,
Shane Devenshire


"DaveKid" wrote:

=IF(VariousCell="Week",COUNTIF(Combined!U:U,CONCAT ENATE(A6,"Yes")),IF(VariousCell="Month",COUNTIF(Co mbined!X:X,CONCATENATE(A6,"Yes"))

This cell is linked to Various cell which is a drop down containing month,
week, year. If you select Week it counts data in a concatenated column which
is up until that week you have selected. This is possible as the weeks
contain numbers i.e. Week 01, Week 02 and excel is able to read the number
prior to the week you have selected. This is not possible with the month as
it is displayed as text November, December etc and excel cannot give this a
value. Does any oine know how to overcome this problem??????

In my previous formula I left out the MONTH function around the (--(1&E1&0)
portion.

Please correct
--
Thanks,
Shane Devenshire


"DaveKid" wrote:

=IF(VariousCell="Week",COUNTIF(Combined!U:U,CONCAT ENATE(A6,"Yes")),IF(VariousCell="Month",COUNTIF(Co mbined!X:X,CONCATENATE(A6,"Yes"))

This cell is linked to Various cell which is a drop down containing month,
week, year. If you select Week it counts data in a concatenated column which
is up until that week you have selected. This is possible as the weeks
contain numbers i.e. Week 01, Week 02 and excel is able to read the number
prior to the week you have selected. This is not possible with the month as
it is displayed as text November, December etc and excel cannot give this a
value. Does any oine know how to overcome this problem??????