Results 1 to 14 of 14
Thread: Stuck on COUNT (2003)

20080521, 12:25 #1
 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

20080521, 13:40 #2
 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.jkpads.com
Professional Office Developers Association

20080521, 14:42 #3
 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.

20080521, 15:15 #4
 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 nonblank 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"))

20080521, 15:15 #5
 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

20080521, 15:16 #6
 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!

20080521, 15:17 #7
 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.

20080521, 15:25 #8
 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 21May08 07:25. Added PS)</P>Try this array (confirm with ctrlshiftenter!).
=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.

20080521, 15:39 #9
 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.

20080521, 16:54 #10
 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]

20080521, 17:11 #11
 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)

20080521, 17:24 #12
 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

20080521, 17:30 #13
 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

20080521, 17:34 #14
 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.