Nested COUNTIFS

Michael Aaron

I'm trying to count the matching items in two cells to a reference in another sheet. Individually they work with COUNTIF and produce the associated result but then I want to test both it comes back with "0".

=COUNTIF({DNAC Adoption Range 1}, Partner106) = counts the correct partner name

=COUNTIF({DNAC Adoption Range 1}, Solution106) - counts the correct # solution name

=COUNTIFS({DNAC Adoption Range 1}, Operation106, {DNAC Adoption Range 1}, Partner106) - returns "0" but should return "1"

What am I missing?

Michael Aaron

The 1st 2 examples both return the correct number of 1 instance for both partner106 and solution106 resepctively. The 3rd example tests for both comes back as 0 when it too should be 1 as there are valid records where both test match.

Paul Newcome

You would use something like this...

=COUNTIFS(Partner:Partner, "IBM", Operation:Operation, "South")

SoS | Dan Palenchar

Difficult to say exactly without seeing your Sheets in question, but it looks like your COUNTIFS (last formula) will only be 0 if the values in the Operation and Partner columns are the same, is this how you intend it to work? This is because your formula is going to count only rows in the DNAC Adoption Range 1 that have a value equal to Partner106 and Solution106. Individually (the first two formulas) will count rows where the specified column has the correct value irrespective of the other column.

Michael Aaron

The 1st 2 examples both return the correct number of 1 instance for both partner106 and solution106 resepctively. The 3rd example tests for both comes back as 0 when it too should be 1 as there are valid records where both test match.

Michael Aaron

I've also tried this with COUNTIF(AND... but it won't parse.

=COUNTIF(AND{DNAC Adoption Range 1}, Partner106, {DNAC Adoption Range 1}, Operation106)

Paul Newcome

Are you able to provide a screenshot of the data that should be matching and what it should be matching to?

Michael Aaron

I think I've found the issue but not sure how to make it work. There are multiple instances of both Partner106 & Operation 106. It's matching the 1st instance and returning 0 because it doesn't match.

In excel I used to count the number of instances then have it loop through the list to test the 2nd variable. Can I do that in SS?

Partner.  Operation

IBM South

IBM Central

IBM. East

IBM West

IBM South

How would I get it to match Partner(IBM) and Operation(South) and return a result of 2?

Thanks

Paul Newcome

You would use something like this...

=COUNTIFS(Partner:Partner, "IBM", Operation:Operation, "South")

Michael Aaron

Hi Paul, That works when all the data is in the same sheet but not for referenced sheets. It did lead me down another path though. When referencing another sheet we can't do Partner1:Partner300 in the formula so I created a separate reference range for each of the tests and only selected the row with the corresponding data.

Effectively the 1st test only selected the partners who match the partner test then the operations test only matched / counted what it matched which is the correct answer.

Here's what the formula finally looked like when referencing another sheet:

=COUNTIFS({DNAC Adoption Range 3}, Partner1, {DNAC Adoption Range 4}, Operation1)

Thanks for everyone's guidance!

Paul Newcome

Yes. Referencing another sheet is definitely going to look different, but using the example I provided made it easier to show which columns to select along with the syntax.

As for selecting a specific range of cells... You CAN do that in cross sheet references. You just highlight that particular range instead of clicking on the column header to select the entire column.