# Counta array is producing number that is wrong (not sure why)

1. ## Counta array is producing number that is wrong (not sure why)

Greetings (and my apologies in advance if I don't describe this well),

I have a large dataset with multiple sheets. The SUMMARY sheet pulls from a table on the WORKING sheet. I need to summary counts from a column comprised of blanks and numbers here's the formula:

=COUNTA(IF(Working!\$A:\$A=Summary!\$A2,Working!L:L))

Summary counts are based on referenced cells from SUMMARY column A [categorical data] matching WORKING column A, and blanks and numerical cells in WORKING column L.

When I perform the array I receive: 1048576 (this is the count of the entire row list, even though my data stops at row 2042)

However when I manually count (using filters) the column of data for the reference category it should be: 2

So my question is: How can I get a valid count without doing manually manipulation? Thanks!

PS I cannot upload this dataset due to sensitive information.

2. ## Re: Counta array is producing number that is wrong (not sure why)

Are you trying to count how many Summary A2's are in Working A:A or Working L;L, or both?

Try:

=COUNTIF(Working!\$A:\$A,Summary!\$A2)
Or

=COUNTIF(Working!L:L,Summary!\$A2)

bot Both

SUM(COUNTIF(Working!L:L,Summary!\$A2),COUNTIF(Working!\$A:\$A,Summary!\$A2)

3. ## Re: Counta array is producing number that is wrong (not sure why)

Let's say Summary A2 = Boat, Working column A contains all categories for with Boat is one. I want only rows with Boat from Working column A and then the count of data in Working column L that corresponds to that row.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1