# Thread: Stuck on COUNT (2003)

1. ## Stuck on COUNT (2003)

Hi,

=COUNT(ACCOUNTS!L2:L5000) - Why is this formula returning 0 when the expected result is 294?

Also, how do I do a count based on 2 conditions. for example - Count the total items in worksheet ACCOUNTS, column H if the 6 digit number (in H) starts with 11 or starts with 80?

Thanks

2. ## Re: Stuck on COUNT (2003)

Maybe Excel thinks the "numbers" are text?

3. ## Re: Stuck on COUNT (2003)

Sorry, still having problems with both..

Sorry, the answers should be 74 & 176.

4. ## Re: Stuck on COUNT (2003)

Edited by HansV to correct typos

COUNT returns the number of cells with a numeric value. If you want to return the number of non-blank cells, use COUNTA.

For the other question, you can add the two counts, for example the following array formula (confirm with Ctrl+Shift+Enter):

=SUM(1*(LEFT(H1:H1000,2)="11"))+SUM(1*(LEFT(H1:H10 00,2)="80"))

5. ## Re: Stuck on COUNT (2003)

<img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> I think you meant:
=SUM(1*(LEFT(H1:H1000,<font color=red>2</font color=red>)="11"))+SUM(1*(LEFT(H1:H1000<font color=red>, 2</font color=red>)="80"))

Steve

6. ## Re: Stuck on COUNT (2003)

Yes, stupid errors (I was distracted by other things while composing my reply). Corrected now, thanks!

7. ## Re: Stuck on COUNT (2003)

I apologize, I had two errors in my reply. Jan Karel and Steve have already posted the solution.

8. ## Re: Stuck on COUNT (2003)

<P ID="edit" class=small>(Edited by sdckapr on 21-May-08 07:25. Added PS)</P>Try this array (confirm with ctrl-shift-enter!).
=SUM(1*(LEFT(HFX!J2:J65536,2)="11"))+SUM(1*(LEFT(H FX!J2:J65536,2)="80"))
You can't use the entire column as arrays don't allow this.
If you are checking for a 2 digit number you must use the left 2 digits not the leftmost 1 digit

As indicated before the 2nd should be:
=COUNTA(HFX!L2:L5000)

Steve
PS Your "numbers" in col L are formatted as "TEXT" (and MUST BE) since they are longer than 15 digits. If you don't treat them as text you will lose the last digit (XL will round the 15 most significant digits). To "count" text you must use COUNTA, COUNT only counts numbers.

9. ## Re: Stuck on COUNT (2003)

Thanks very much to you all.

10. ## Re: Stuck on COUNT (2003)

I'm having a nightmare today!!

11. ## Re: Stuck on COUNT (2003)

In F5: =SUMPRODUCT(('Rejections 2'!\$L\$2:\$L\$45<>"")*('Rejections 2'!\$C\$2:\$C\$45="10")*'Rejections 2'!\$I\$2:\$I\$45)+SUMPRODUCT(('Rejections 2'!\$L\$2:\$L\$45<>"")*('Rejections 2'!\$C\$2:\$C\$45=40)*'Rejections 2'!\$I\$2:\$I\$45)

Why, in heaven's name, is C32 numeric, while all other values in that column are text? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

In F6: =SUMPRODUCT(('Rejections 2'!\$L\$2:\$L\$45<>"")*('Rejections 2'!\$C\$2:\$C\$45="20")*'Rejections 2'!\$I\$2:\$I\$45)

In F7: =SUMPRODUCT(('Rejections 2'!\$L\$2:\$L\$45<>"")*('Rejections 2'!\$C\$2:\$C\$45="30")*'Rejections 2'!\$I\$2:\$I\$45)

12. ## Re: Stuck on COUNT (2003)

A shorter version of your first formula would be to combine the "OR"s together into one SUMPRODUCT:
=SUMPRODUCT(('Rejections 2'!\$L\$2:\$L\$45<>"")*(('Rejections 2'!\$C\$2:\$C\$45="10")+('Rejections 2'!\$C\$2:\$C\$45=40))*'Rejections 2'!\$I\$2:\$I\$45)

Steve

13. ## Re: Stuck on COUNT (2003)

Works perfect.

The numeric was an error, would always be text.

Many Thanks

14. ## Re: Stuck on COUNT (2003)

Yep, that works since the conditions are mutually exclusive. Thanks.

#### Posting Permissions

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