Results 1 to 14 of 14
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stuck on COUNT (2003)

    Maybe Excel thinks the "numbers" are text?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stuck on COUNT (2003)

    Sorry, still having problems with both..

    Please see attached. Thanks

    Sorry, the answers should be 74 & 176.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Stuck on COUNT (2003)

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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Stuck on COUNT (2003)

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

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stuck on COUNT (2003)

    Thanks very much to you all.

  10. #10
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stuck on COUNT (2003)

    I'm having a nightmare today!!

    I can't get the attached sums to populate, please help [manicplee]
    Attached Files Attached Files

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #13
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stuck on COUNT (2003)

    Works perfect.

    The numeric was an error, would always be text.

    Many Thanks

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •