CODEWITHSUNDEEP
excelrangespreadsheetcriteriacountif
Jacob B
Jacob B

Reputation: 11

Countifs with array criteria and multiple ranges

I have three columns of Data. Image

I want to find a permutation where I can count the hits of data that satisfy 3 conditions

1) In column C the data belongs to the group defined as Europe (I9:I12)

2) In column D the data belongs to the group defined as Asia (J9:J12)

3) In column B the data is greater than year 2013 (L9)

Basically trying to find the permutation of European countries that cross with Asian countries from 2013 to 2018.

This is my formula as in the image, with Ctrl+Shift+Enter:

{=SUM(COUNTIFS($C:$C,I12:I15,$D:$D,J12:J15,$B:$B,">="&$L$12))}

But gives me 0 at the moment when should find and count 2 results

For interest, my next step would be finding the other way round, cross between Asian and Europe.

I appreciate very much the help

Upvotes: 1

Views: 1220

Answers (1)

XOR LX
XOR LX

Reputation: 7762

=SUM(COUNTIFS($C:$C,I12:I15,$D:$D,TRANSPOSE(J12:J15),$B:$B,">="&$L$12))

One of the two arrays needs to be transposed such that it is orthogonal to the other. See here for an explanation if you like:

https://excelxor.com/2014/09/28/countifs-multiple-or-criteria-for-one-or-two-criteria_ranges/

Regards

Upvotes: 2

Related Questions