DCOUNT With Function in Criteria

Posted on

QUESTION :

I have a table of data in Excel with each row possessing multiple columns. For the sake of simplicity, let’s call them A and B. I want to count the rows wherein which the sum of A and B is less than integer X. Normally, I would set up two rows for this criteria: the top would have the label for the column, and the bottom would have the criteria itself.

----
|A |
----
|<X|
----

That works just fine, but if I do something like this, the DCOUNT function just returns 0, which is an incorrect answer:

-------
|A + B|
-------
|<X   |
-------

Obviously there is something incorrect with the A + B syntax, but I can’t figure it out. None of the examples for DCOUNT I have found demonstrate the use of functions in criteria. I also saw this, but it doesn’t seem to be entirely relevant.

I am also aware that it would be possible to make an additional column with the sum of A and B and use that for the criteria, but that seems unnecessarily redundant.

ANSWER :

With data in columns A and B, put your criteria in, say, D1 and D2. D1 must contain a header that is not present in your headers, and D2 is your formula. D2 contains this formula:

=A2+B2<X

and your DCOUNT() looks like this:

=DCOUNT(A:B,1,D1:D2)

Leave a Reply

Your email address will not be published. Required fields are marked *