[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

Excel - Countif

Hi

I have a text formatted column - and am using the Countif formula to assess the number of times a value repeats in this column. Though it is a text formatted column, the information is a number imported from an external text file.

The nuance is that Excel counts '0230' and '00230' as the same value, so the Countif value is 2, but it should be 1 for each.

Please offer suggestions.

Thank you.
0
tahirih
Asked:
tahirih
  • 5
  • 4
1 Solution
 
tahirihAuthor Commented:
I do have the option of adding another digit in front of all value, and this makes Countif accurate, but I was wondering if there is a way to leave the values as is.
0
 
barry houdiniCommented:
You'd have to use SUMPRODUCT if you want to differentiate, something like this

=SUMPRODUCT((A2:A100="0230")+0)

regards, barry
0
 
tahirihAuthor Commented:
This is helpful thank you. Sorry, I did not provide all the information. This will not be limited to 0230, 00230.

The values could also be 0340 00340
                                         0520 00520

Thank you
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
barry houdiniCommented:
well you can adapt the formula to count any of those....or use a cell reference, e.g. in C2 down list all the value you want to count - text formatted - and use this version in D2 copied down

=SUMPRODUCT((A$2:A$100=C2)+0)

Does that do what you want?

regards, barry

0
 
tahirihAuthor Commented:
Sorry I am not completely understanding. The values are listed in column A, and I want the countif/sumproduct formula to be in Column B.
0
 
ScriptAddictCommented:
I'm assuming that the possible values are nearly unlimited.  

Do you need to have all the values at once?  

If you only  needed to see the sum of a single specific value at a time you could use his formula with a cell ref instead of  "0230".

Alternatively you could add a column next to the one including the 0340 that uses the LEN() formula to identify how many characters is in the string.  Then concatenate the value and the LEN() and do a countif using that adjusted number.

Hope that helps!

-John



0
 
barry houdiniCommented:
You could use a variation of the formula I suggested in B2 copied down, i.e.

=SUMPRODUCT((A$2:A$100=A2)+0)

Then that will give you a count for each value in column A

Extend the range as required - you can only use the whole column in SUMPRODUCT if you are using Excel 2007 or a later version

regards, barry

0
 
tahirihAuthor Commented:
This is working part way. Unfortunatly, I am getting a TRUE/FALSE value in B column, rather than the count that the value in A column appears. Example, if 00320 appears in rows 3, 15, and 45 and 0320 appears in 10 and 55, the values in B should be 3 in rows 3, 15, and 45, and 2 in rows 10 and 55.

Thanks
0
 
barry houdiniCommented:
Yes, that's what the formula should do - see the attached example

regards, barry
27401274.xls
0
 
tahirihAuthor Commented:
Thank you.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now