How to get number of rows which match criteria

[Guest]

Coloum A Column B
Closed Settings
Open Settings
Closed Prametes
Open UI

I want to calculate Number of rows which have in Column A Closed and in
Column B Settings . Please help ..

 

[Roger Govier]

One way would be
=SUMPRODUCT(--(A2:A100="Closed"),(B2:B100="Settings"))
Change ranges to suit

 

[Bob Phillips]

typo

=SUMPRODUCT(--(A2:A100="Closed"),--(B2:B100="Settings"))

 

[Roger Govier]

Hi Bob

Thanks for that.
However, I am confused (nothing new there!!).
Normally I use the "*" in the SUMPRODUCT formula and would normally have
submitted
=SUMPRODUCT(--(A2:A100="Closed")*(B2:B100="Settings"))
which works fine.

However, following a post from J.E. the other day where he suggested the use
of the "," as a separator was marginally more efficient, I substituted it in
this formula (without testing) and you quite rightly pointed out it doesn't
work.

Perhaps I need more explanation on the difference between the two methods.

 

[Bob Phillips]

Hi Roger,

I cannot believe that * versus -- would make any noticeable difference on
any spreadsheet.. It might be more efficient as the * will do the coercion
in the same step as the product, but that might also make it less so. And
with some complex formulae, it will be better to coerce to numeric before
the product. But as I said I cannot believe it would be noticeable.

BTW, if you do use the * operator, you don't need to coerce the first part

=SUMPRODUCT((A2:A100="Closed")*(B2:B100="Settings"))


Perhaps this can help you http://xldynamic.com/source/xld.SUMPRODUCT.html

 

[Aladin Akyurek]

Might be of interest:

http://www.mrexcel.com/board2/viewtopic.php?t=73205

Hi Bob

Thanks for that.
However, I am confused (nothing new there!!).
Normally I use the "*" in the SUMPRODUCT formula and would normally have
submitted
=SUMPRODUCT(--(A2:A100="Closed")*(B2:B100="Settings"))
which works fine.

However, following a post from J.E. the other day where he suggested the use
of the "," as a separator was marginally more efficient, I substituted it in
this formula (without testing) and you quite rightly pointed out it doesn't
work.

Perhaps I need more explanation on the difference between the two methods.

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

 

[Roger Govier]

Hi Bob & Aladin

Thank you for the references. They were both very useful and all is now
clear.