Stuck on COUNT (2003)

Topic

New Reply

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

Reply | Quote

Replies

Maybe Excel thinks the “numbers” are text?

Reply | Quote

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:H1000,2)=”80″))

Reply | Quote

Sorry, still having problems with both..

Please see attached. Thanks

Sorry, the answers should be 74 & 176.

Reply | Quote

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

Reply | Quote

(Edited by sdckapr on 21-May-08 07:25. Added PS)

Try this array (confirm with ctrl-shift-enter!).
=SUM(1*(LEFT(HFX!J2:J65536,2)=”11″))+SUM(1*(LEFT(HFX!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.

Reply | Quote

Thanks very much to you all.

Reply | Quote

I’m having a nightmare today!!

I can’t get the attached sums to populate, please help [manicplee]

Reply | Quote

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?

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)

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Works perfect.

The numeric was an error, would always be text.

Many Thanks

Reply | Quote

I think you meant:
=SUM(1*(LEFT(H1:H1000,2)=”11″))+SUM(1*(LEFT(H1:H1000, 2)=”80″))

Steve

Reply | Quote

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

Reply | Quote