Concatenate formula needs a fix

  • Thread starter Thread starter Vic
  • Start date Start date
V

Vic

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.
 
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
 
I still get #VALUE!

Mike H said:
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
Click to expand...
 
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
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help: Using CONCATENATE (and a COUNT?) for a total result 3
Concatenate first:last! worksheet 5
IF VLOOKUP & CONCATENATE 7
Excel Need Countifs Formula Help 0
Concatenate formula 2
Concatenation issue - Help, PLEASE! 2
CONCATENATE and SUMPRODUCT in a formula?? 2
Excel Formula - Count Dates from another Sheet 8

Back
Top