Solved

# Excel - Countif

Posted on 2011-10-17
247 Views
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
Question by:tahirih

Author Comment

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

LVL 50

Expert Comment

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

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

regards, barry
0

Author Comment

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

LVL 50

Expert Comment

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 Comment

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

LVL 11

Expert Comment

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

LVL 50

Expert Comment

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 Comment

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

LVL 50

Accepted Solution

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

regards, barry
27401274.xls
0

Author Closing Comment

Thank you.
0

## Featured Post

### Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.