how to Use COUNTIFS funtion with the criteria_range1 to be a column.

[freeSky]

Hi all,


I cant think of anyway to do this. So plz give a hand!

This is usual way to use COUNTIFS

=COUNTIFS(B2:B5,"=Yes",C2:C5,"=Yes")

But what if I want my criteria_range1 to be Column B from another Spreadsheet in the same workbook.



Thanks a lot
Sky

[benishiryo]

hi freeSky. actually you dont have to put "=". to refer to another worksheet, make sure the range is the same if you need the same row to have "yes". like from 2:5. so if it's in Sheet2, it'll be like:
=COUNTIFS(A2:A5,"Yes",Sheet2!B2:B5,"Yes")

if your aim is to add the "Yes" in Column A plus the "Yes" in Sheet2 Column B, use COUNTIF & add
=COUNTIF(A2:A5,"Yes")+COUNTIF(Sheet2!B2:B5,"Yes")

[freeSky]

Hey, Thanks for the quick reply!

Actually I want to count "YES" in the whole column B in another sheet. not just B2-B5, iTS the whole column. Can this be achieved?

hi freeSky. actually you dont have to put "=". to refer to another worksheet, make sure the range is the same if you need the same row to have "yes". like from 2:5. so if it's in Sheet2, it'll be like:
=COUNTIFS(A2:A5,"Yes",Sheet2!B2:B5,"Yes")

if your aim is to add the "Yes" in Column A plus the "Yes" in Sheet2 Column B, use COUNTIF & add
=COUNTIF(A2:A5,"Yes")+COUNTIF(Sheet2!B2:B5,"Yes")

[freeSky]

And one more prob,

I can use Sheet1 in a tryout excel book. But I cannt apply it to my working book, So what should i enter in if my sheet1's name is "Partner List"?
Thanks,
Sky

hi freeSky. actually you dont have to put "=". to refer to another worksheet, make sure the range is the same if you need the same row to have "yes". like from 2:5. so if it's in Sheet2, it'll be like:
=COUNTIFS(A2:A5,"Yes",Sheet2!B2:B5,"Yes")

if your aim is to add the "Yes" in Column A plus the "Yes" in Sheet2 Column B, use COUNTIF & add
=COUNTIF(A2:A5,"Yes")+COUNTIF(Sheet2!B2:B5,"Yes")