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

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

# 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.

Thank you.
0
tahirih
• 5
• 4
1 Solution

Author 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

Commented:
You'd have to use SUMPRODUCT if you want to differentiate, something like this

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

regards, barry
0

Author 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

Commented:
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

Author 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

Commented:
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

Commented:
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

Author 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

Commented:
Yes, that's what the formula should do - see the attached example

regards, barry
27401274.xls
0

Author Commented:
Thank you.
0

## Featured Post

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