I'm lookink for an Excel function

  • Thread starter Thread starter iulia
  • Start date Start date
I

iulia

Hello!

I want to tell me an Excel function that counts the number
of cells within a range that meet TWO given conditions.

Thanks.
 
Hi

=SUMPRODUCT((FirstRange=FirstCondition)*(SecondRange=SecondCondition)*...)

I think the number of various conditions is limited by max length for a
formula.
All ranges must be of same dimension, but they can differ or be same, like:
=SUMPRODUCT((A2:A100="a")*(B2:B100="b"))
or
=SUMPRODUCT((A2:A100="a")*(A2:A100="b"))
but not
=SUMPRODUCT((A2:A100="a")*(B2:B200="b"))


Arvi Laanemets
 
Try using the function COUNTIF. It has 2 parameers, first
being the range you want evaluated, and the second being
the condition in which case the cells within the range
must be counted.

so, for example; if the range you want to count is A1:A100
and the conditions that you want to evaluate are all cells
matching "ABCD" or all cells matching "PQRS", then you use
the function as follows:


=COUNTIF(A1:A100, "ABCD") + COUNTIF(A1:A100, "PQRS")

If you are looking for an "AND" condition instead of an OR
condition, then you need to use SUMPRODUCT.

In that case lets say in range A1:A100 you are looking
for "ABCD" and for those records you want to count how
many records in range B1:B100 contain "PQRS". The
formula to use will be:

=SUMPRODUCT((A1:A100="ABCD")*(B1:B100="PQRS"))

HTH
 
Harlan said:
If one's trying to minimize function calls (somethimes a necessity, especially
with regard to *nested* function calls),

=SUMPRODUCT(--(A1:A100={"ABCD","PQRS"}))

is even tighter.
Click to expand...

At a cost in execution speed?

Alan Beban
...
Akshay said:
[snip]>
so, for example; if the range you want to count is A1:A100
and the conditions that you want to evaluate are all cells
matching "ABCD" or all cells matching "PQRS", then you use
the function as follows:


=COUNTIF(A1:A100, "ABCD") + COUNTIF(A1:A100, "PQRS")
Click to expand...

Or simply =SUM(COUNTIF(A1:A100,{"ABCD","PQRS"})
Click to expand...
Click to expand...
 
Harlan Grove wrote: ...

At a cost in execution speed?
Click to expand...

Dunno. Have you tested it? If SUM(COUNTIF(...)) is faster, it's a dead cinch
that COUNTIF(..)+COUNTIF(..) would be faster still since no function call
overhead (and only one level of function calls, though 2 function calls in
total), so what's your criterion for 'better'?
 
Depends on what the goals are. If, as you hypothethized, the goal is to
minimize function calls, then the one that minimizes function calls is
clearly "better". If that's not an issue, and the goal is the fastest,
then obviously the fastest one is "better". And there could be more
idiosyncratic goals relating to ease of coding or ease of understanding
for particular user(s), etc.

I asked only because I had picked up the impression, perhaps
erroneously, that fewer function calls was inherently better; but you
hadn't mentioned speed in this case as a reason for the "tighter"
version, so I began to wonder. If my impression *is* erroneous, then I
will view differently in the future alternatives whose only claim to
fame is fewer function calls.

Alan Beban
 
Back
Top