CODEWITHSUNDEEP
excelexcel-formula
Karma
Karma

Reputation: 269

Countifs multiple columns

I want to count the number of times that all 3 criteria are matched, as shown below:

     A    B    C    D
1  12%  Yes  2.0  1.9
2   6%  Yes  0.6  2.2
3  -2%  Yes  4.8  4.0
4  11%   No  6.0  4.2
5   5%   No  3.5  3.9

3 criteria include: Column A is greater than 10%, Column B is "Yes", and Column C > Column D

Here's my formula: 

=COUNTIFS(A:A, ">=0.1", B:B, "Yes", C:C, ">="&D:D)

As you can see, only Row 1 fits all the criteria, and it supposedly would return 1. However, it returns 0 no matter how I amend my formula.

EDIT: I'm pretty sure the C:C, ">="&D:D is causing the error, but I don't know how to fix it.

Upvotes: 0

Views: 71

Answers (3)

user11174618
user11174618

Reputation:

An olde style array formula produces the correct results.

=SUM(IF(A1:A5>0.1, IF(B1:B5="yes", IF(C1:C5>D1:D5, 1))))

Finish this with Ctrl+Shift+Enter, not just Enter.

Upvotes: 1

ashleedawg
ashleedawg

Reputation: 21639

The problem is that ">=" & D:D is basically a standalone text function ("&" is a shortcut for the CONCATENATE function) and therefore doesn't know which row you're referring to (so you're saying "greater than or equal to all of column D").

One solution would be to add "helper column". For example if E1 had a formula:

=C1>=D1

...which was then copied down to the rest of the column, then you could get your desired result with formula:

=COUNTIFS(A:A, ">=0.1", B:B, "Yes", E:E, TRUE)

(There's another solution using an array formula but it eludes me at the moment.)

Edit: heh, the array formula solution eluded me because you already had the correct formula to use as an array formula --

....so, use your existing formula, but instead of hitting Enter to enter it, use Ctrl+Shift+Enter.

More Information:

Upvotes: 3

Baris Tasdelen
Baris Tasdelen

Reputation: 356

You can use sumproduct

=SUMPRODUCT(--(A:A>=0.1), --( B:B = "Yes"), --(C:C >= D:D))

or =SUMPRODUCT(--(A1:A5>=0.1), --( B1:B5 = "Yes"), --(C1:C5 >= D1:D5))

Upvotes: 1

Related Questions