Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Vic Vic is offline
external usenet poster
 
Posts: 117
Default Concatenate formula needs a fix

I am applying the following formula to concatenate 2 counters, one from this
sheet and another one from another sheet. This formula is not working. Can
someone please show me how to fix it?
=CONCATENATE(COUNTIF(A10:A500,"USA")," / ",COUNTIF('DFs-Sym'!C90:C1000,"USA"))
I need to see something like this == 38 / 105
I am doing this because I can't split the cell, but I need 2 different
counters for each country.
Thank you.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default Concatenate formula needs a fix

Hi,

Your formula is fine and you don't explain what 'not working' is. I suspect
you have a data problem with (maybe) rogue spaces so try this

=CONCATENATE(COUNTIF(A10:A500,"*USA*")," /
",COUNTIF('DFs-Sym'!C90:C1000,"*USA*"))

Note that will fail if a cell in your range contains something like medUSA
which will be counted so if it doesn't work look to cleaning your data.

Mike

"Vic" wrote:

I am applying the following formula to concatenate 2 counters, one from this
sheet and another one from another sheet. This formula is not working. Can
someone please show me how to fix it?
=CONCATENATE(COUNTIF(A10:A500,"USA")," / ",COUNTIF('DFs-Sym'!C90:C1000,"USA"))
I need to see something like this == 38 / 105
I am doing this because I can't split the cell, but I need 2 different
counters for each country.
Thank you.

  #3   Report Post  
Posted to microsoft.public.excel.misc
Vic Vic is offline
external usenet poster
 
Posts: 117
Default Concatenate formula needs a fix

I still get #VALUE!

"Mike H" wrote:

Hi,

Your formula is fine and you don't explain what 'not working' is. I suspect
you have a data problem with (maybe) rogue spaces so try this

=CONCATENATE(COUNTIF(A10:A500,"*USA*")," /
",COUNTIF('DFs-Sym'!C90:C1000,"*USA*"))

Note that will fail if a cell in your range contains something like medUSA
which will be counted so if it doesn't work look to cleaning your data.

Mike

"Vic" wrote:

I am applying the following formula to concatenate 2 counters, one from this
sheet and another one from another sheet. This formula is not working. Can
someone please show me how to fix it?
=CONCATENATE(COUNTIF(A10:A500,"USA")," / ",COUNTIF('DFs-Sym'!C90:C1000,"USA"))
I need to see something like this == 38 / 105
I am doing this because I can't split the cell, but I need 2 different
counters for each country.
Thank you.

  #4   Report Post  
Posted to microsoft.public.excel.misc
Tel Tel is offline
external usenet poster
 
Posts: 39
Default Concatenate formula needs a fix

Hi Vic,

I'm no expert and I'm sure others will shoot me down if I'm wrong but try
this:

In one spare cell (say A1) enter the formula

=COUNTIF(A10:A500,"USA")

in a second spare cell (say A2) enter the formula

=COUNTIF('DFs-Sym'!C90:C1000,"USA"))

In a third (your target cell) enter

=A1&"/"&A2

Hey presto you've got it. It might be long winded but you get the result.

I often hide cells using white text on a white background then lock the cell
out if you don't want others to see it.

Regards

Tel


"Vic" wrote:

I am applying the following formula to concatenate 2 counters, one from this
sheet and another one from another sheet. This formula is not working. Can
someone please show me how to fix it?
=CONCATENATE(COUNTIF(A10:A500,"USA")," / ",COUNTIF('DFs-Sym'!C90:C1000,"USA"))
I need to see something like this == 38 / 105
I am doing this because I can't split the cell, but I need 2 different
counters for each country.
Thank you.

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default Concatenate formula needs a fix

Hi,

The only way I can get a #VALUE! error is by not having a sheet called
"DFs-Sym"

Check the spelling of your sheet name and ensure you don't have any spaces
at the ends.

Mike

"Vic" wrote:

I still get #VALUE!

"Mike H" wrote:

Hi,

Your formula is fine and you don't explain what 'not working' is. I suspect
you have a data problem with (maybe) rogue spaces so try this

=CONCATENATE(COUNTIF(A10:A500,"*USA*")," /
",COUNTIF('DFs-Sym'!C90:C1000,"*USA*"))

Note that will fail if a cell in your range contains something like medUSA
which will be counted so if it doesn't work look to cleaning your data.

Mike

"Vic" wrote:

I am applying the following formula to concatenate 2 counters, one from this
sheet and another one from another sheet. This formula is not working. Can
someone please show me how to fix it?
=CONCATENATE(COUNTIF(A10:A500,"USA")," / ",COUNTIF('DFs-Sym'!C90:C1000,"USA"))
I need to see something like this == 38 / 105
I am doing this because I can't split the cell, but I need 2 different
counters for each country.
Thank you.



  #6   Report Post  
Posted to microsoft.public.excel.misc
Tel Tel is offline
external usenet poster
 
Posts: 39
Default Concatenate formula needs a fix

or you could do:

=COUNTIF(A10:A500,"USA")&"/"&COUNTIF('DFs-Sym'!C90:C1000,"USA")

Tel


"Vic" wrote:

I am applying the following formula to concatenate 2 counters, one from this
sheet and another one from another sheet. This formula is not working. Can
someone please show me how to fix it?
=CONCATENATE(COUNTIF(A10:A500,"USA")," / ",COUNTIF('DFs-Sym'!C90:C1000,"USA"))
I need to see something like this == 38 / 105
I am doing this because I can't split the cell, but I need 2 different
counters for each country.
Thank you.

  #7   Report Post  
Posted to microsoft.public.excel.misc
Vic Vic is offline
external usenet poster
 
Posts: 117
Default Concatenate formula needs a fix

Mike,
You are right! It is DFS-Sym. What was I thinking?
Thank you.

"Mike H" wrote:

Hi,

The only way I can get a #VALUE! error is by not having a sheet called
"DFs-Sym"

Check the spelling of your sheet name and ensure you don't have any spaces
at the ends.

Mike

"Vic" wrote:

I still get #VALUE!

"Mike H" wrote:

Hi,

Your formula is fine and you don't explain what 'not working' is. I suspect
you have a data problem with (maybe) rogue spaces so try this

=CONCATENATE(COUNTIF(A10:A500,"*USA*")," /
",COUNTIF('DFs-Sym'!C90:C1000,"*USA*"))

Note that will fail if a cell in your range contains something like medUSA
which will be counted so if it doesn't work look to cleaning your data.

Mike

"Vic" wrote:

I am applying the following formula to concatenate 2 counters, one from this
sheet and another one from another sheet. This formula is not working. Can
someone please show me how to fix it?
=CONCATENATE(COUNTIF(A10:A500,"USA")," / ",COUNTIF('DFs-Sym'!C90:C1000,"USA"))
I need to see something like this == 38 / 105
I am doing this because I can't split the cell, but I need 2 different
counters for each country.
Thank you.

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default Concatenate formula needs a fix

Glad I could help and thanks for the feedback

"Vic" wrote:

Mike,
You are right! It is DFS-Sym. What was I thinking?
Thank you.

"Mike H" wrote:

Hi,

The only way I can get a #VALUE! error is by not having a sheet called
"DFs-Sym"

Check the spelling of your sheet name and ensure you don't have any spaces
at the ends.

Mike

"Vic" wrote:

I still get #VALUE!

"Mike H" wrote:

Hi,

Your formula is fine and you don't explain what 'not working' is. I suspect
you have a data problem with (maybe) rogue spaces so try this

=CONCATENATE(COUNTIF(A10:A500,"*USA*")," /
",COUNTIF('DFs-Sym'!C90:C1000,"*USA*"))

Note that will fail if a cell in your range contains something like medUSA
which will be counted so if it doesn't work look to cleaning your data.

Mike

"Vic" wrote:

I am applying the following formula to concatenate 2 counters, one from this
sheet and another one from another sheet. This formula is not working. Can
someone please show me how to fix it?
=CONCATENATE(COUNTIF(A10:A500,"USA")," / ",COUNTIF('DFs-Sym'!C90:C1000,"USA"))
I need to see something like this == 38 / 105
I am doing this because I can't split the cell, but I need 2 different
counters for each country.
Thank you.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Concatenate a formula Steven Excel Worksheet Functions 1 July 23rd 08 04:17 AM
Concatenate formula or & Sam Excel Discussion (Misc queries) 11 January 18th 07 10:54 PM
help with formula...if -- concatenate sedonovan Excel Discussion (Misc queries) 1 June 22nd 06 12:31 PM
Evaluating results of a concatenate formula, as a formula dodger Excel Worksheet Functions 5 September 9th 05 01:55 PM
CONCATENATE formula jkeeton Excel Discussion (Misc queries) 1 April 1st 05 05:33 PM


All times are GMT +1. The time now is 01:12 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"