# countifs working on extended ranges and different sheets not working

1. ## countifs working on extended ranges and different sheets not working

i have a countifs function working on a different sheet

so the formula is
=COUNTIFS(Sheet2!\$B:\$B,\$A3,Sheet2!\$AK:\$AK,B\$2)
which works

where i have a grid with the criteria

this works OK
and
=COUNTIFS(Sheet2!\$B1:\$B1000,\$A3,Sheet2!\$AK1:\$AK1000,B\$2)
also works

BUT
if i change the range of the first criteria range to more then one column it fails

=COUNTIFS(Sheet2!\$B1:\$G1000,\$A3,Sheet2!\$AK1:\$AK1000,B\$2)

it retunes value
i have text in A3
Face to face
and i have tried
=COUNTIFS(Sheet2!\$B:\$B,"=Face to face",Sheet2!\$AK:\$AK,B\$2)

so no idea why it fails if i extend the columns

any ideas ?

thanks

2. ## Re: countifs working on extended ranges and different sheets not working

Hi,

From office.microsoft.com: "Each additional range must have the same number of rows and columns as the criteria_range1 argument."

So

=COUNTIFS(Sheet2!\$B1:\$G1000,\$A3,Sheet2!\$AK1:\$AP1000,B\$2)

would fix this issue, though obviously may have undesirable consequences, depending what's in columns AL:AP.

You can do it with SUMPRODUCT:

=SUMPRODUCT((Sheet2!\$B1:\$G1000=\$A3)*(Sheet2!\$AK1:\$AK1000=B\$2))

Regards

3. ## Re: countifs working on extended ranges and different sheets not working

brilliant - thanks for that - both work perfectly

4. ## Re: countifs working on extended ranges and different sheets not working

You're welcome.

##### Users Browsing this Thread

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