Thread: counting certain cells correctly (Excel 2000)

1. counting certain cells correctly (Excel 2000)

I am getting the opposite answer in my formula from what I need. Could someone look at my formula and see what I have backwards or wrong. I tried throwing the NOT command in there, but to no avail... I have 160 records with LWCHRG in 16 of them. I am trying to count just those cells. However, am getting the opposite answer of 144.

Formula is:

=COUNT(IF(ISERROR(FIND("LWCHRG",D1160)),0)) and pressing CTRL SHIFT and ENTER

attaching file for reference.

thank you, and most of all for your patience today...really rough day and I know we all have them.

NMP

2. Re: counting certain cells correctly (Excel 2000)

Try this one:

=COUNT(IF(NOT(ISERROR(FIND("LWCHRG",D1160))),0))

as an array formula (Ctrl+Shift+Enter). You could also have used your original formula:

=COUNTA(D1160)-COUNT(IF(ISERROR(FIND("LWCHRG",D1160)),0))

also as an array formula.

3. Re: counting certain cells correctly (Excel 2000)

John,

Bless you. That worked fine. But I must know...why the ISNUMBER, LWCHRG is not formatted as a number...

NMP

4. Re: counting certain cells correctly (Excel 2000)

Try

=COUNT(IF(ISNUMBER(FIND("LWCHRG",D1160)),0)) with Ctrl-Shift-Enter

=SUM(IF(ISNUMBER(FIND("LWCHRG",D1160)),1,0)) with Ctrl-Shift-Enter also works

5. Re: counting certain cells correctly (Excel 2000)

Hans,

Thank you so much. Both formulas work as well as Johns. I had the NOT in the wrong place when I was trying it out...I'll get this after a while, you just wait and see. [img]/forums/images/smilies/smile.gif[/img] Because of all you good folks help.

NMP

6. Re: counting certain cells correctly (Excel 2000)

John,

Thank you for the good explanation. I'm learning all kinds of good stuff today.

NMP

7. Re: counting certain cells correctly (Excel 2000)

Because FIND returns the position number of the found string within the target string if it finds it, and returns #VALUE! if it doesn't find it. ISNUMBER handles the numbers by returning them as 'TRUE', and treats everything else, including #VALUE! , by returning them as 'FALSE'.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•