SUMPRODUCT: How to get number of rows which match criteria

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

--
Regards
Roger Govier
"kiranmani" <[email protected]> wrote in message
news:[email protected]...
> 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 ..

Hi Bob & Aladin

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

--
Regards
Roger Govier
"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> --
> HTH
>
> Bob Phillips
>
> "Roger Govier" <[email protected]> wrote in message
> news:%[email protected]...
>> 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.
>>
>> --
>> Regards
>> Roger Govier
>> "Bob Phillips" <[email protected]> wrote in message
>> news:[email protected]...
>> > typo
>> >
>> > =SUMPRODUCT(--(A2:A100="Closed"),--(B2:B100="Settings"))
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > "Roger Govier" <[email protected]> wrote in message
>> > news:%[email protected]...
>> >> One way would be
>> >> =SUMPRODUCT(--(A2:A100="Closed"),(B2:B100="Settings"))
>> >> Change ranges to suit
>> >>
>> >> --
>> >> Regards
>> >> Roger Govier
>> >> "kiranmani" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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 ..
>> >>
>> >>
>> >
>> >
>>
>>
>
>

Might be of interest:

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

Roger Govier wrote:
> 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.

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

--
HTH

Bob Phillips

"Roger Govier" <[email protected]> wrote in message
news:%[email protected]...
> 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.
>
> --
> Regards
> Roger Govier
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > typo
> >
> > =SUMPRODUCT(--(A2:A100="Closed"),--(B2:B100="Settings"))
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Roger Govier" <[email protected]> wrote in message
> > news:%[email protected]...
> >> One way would be
> >> =SUMPRODUCT(--(A2:A100="Closed"),(B2:B100="Settings"))
> >> Change ranges to suit
> >>
> >> --
> >> Regards
> >> Roger Govier
> >> "kiranmani" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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 ..
> >>
> >>
> >
> >
>
>

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.

--
Regards
Roger Govier
"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> typo
>
> =SUMPRODUCT(--(A2:A100="Closed"),--(B2:B100="Settings"))
>
> --
> HTH
>
> Bob Phillips
>
> "Roger Govier" <[email protected]> wrote in message
> news:%[email protected]...
>> One way would be
>> =SUMPRODUCT(--(A2:A100="Closed"),(B2:B100="Settings"))
>> Change ranges to suit
>>
>> --
>> Regards
>> Roger Govier
>> "kiranmani" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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 ..
>>
>>
>
>

typo

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

--
HTH

Bob Phillips

"Roger Govier" <[email protected]> wrote in message
news:%[email protected]...
> One way would be
> =SUMPRODUCT(--(A2:A100="Closed"),(B2:B100="Settings"))
> Change ranges to suit
>
> --
> Regards
> Roger Govier
> "kiranmani" <[email protected]> wrote in message
> news:[email protected]...
> > 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 ..
>
>

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

--
Regards
Roger Govier
"kiranmani" <[email protected]> wrote in message
news:[email protected]...
> 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 ..

typo

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

--
HTH

Bob Phillips

"Roger Govier" <[email protected]> wrote in message
news:%[email protected]...
> One way would be
> =SUMPRODUCT(--(A2:A100="Closed"),(B2:B100="Settings"))
> Change ranges to suit
>
> --
> Regards
> Roger Govier
> "kiranmani" <[email protected]> wrote in message
> news:[email protected]...
> > 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 ..
>
>

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.

--
Regards
Roger Govier
"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> typo
>
> =SUMPRODUCT(--(A2:A100="Closed"),--(B2:B100="Settings"))
>
> --
> HTH
>
> Bob Phillips
>
> "Roger Govier" <[email protected]> wrote in message
> news:%[email protected]...
>> One way would be
>> =SUMPRODUCT(--(A2:A100="Closed"),(B2:B100="Settings"))
>> Change ranges to suit
>>
>> --
>> Regards
>> Roger Govier
>> "kiranmani" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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 ..
>>
>>
>
>

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

--
HTH

Bob Phillips

"Roger Govier" <[email protected]> wrote in message
news:%[email protected]...
> 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.
>
> --
> Regards
> Roger Govier
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > typo
> >
> > =SUMPRODUCT(--(A2:A100="Closed"),--(B2:B100="Settings"))
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Roger Govier" <[email protected]> wrote in message
> > news:%[email protected]...
> >> One way would be
> >> =SUMPRODUCT(--(A2:A100="Closed"),(B2:B100="Settings"))
> >> Change ranges to suit
> >>
> >> --
> >> Regards
> >> Roger Govier
> >> "kiranmani" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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 ..
> >>
> >>
> >
> >
>
>

Might be of interest:

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

Roger Govier wrote:
> 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.

Hi Bob & Aladin

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

--
Regards
Roger Govier
"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> --
> HTH
>
> Bob Phillips
>
> "Roger Govier" <[email protected]> wrote in message
> news:%[email protected]...
>> 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.
>>
>> --
>> Regards
>> Roger Govier
>> "Bob Phillips" <[email protected]> wrote in message
>> news:[email protected]...
>> > typo
>> >
>> > =SUMPRODUCT(--(A2:A100="Closed"),--(B2:B100="Settings"))
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > "Roger Govier" <[email protected]> wrote in message
>> > news:%[email protected]...
>> >> One way would be
>> >> =SUMPRODUCT(--(A2:A100="Closed"),(B2:B100="Settings"))
>> >> Change ranges to suit
>> >>
>> >> --
>> >> Regards
>> >> Roger Govier
>> >> "kiranmani" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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 ..
>> >>
>> >>
>> >
>> >
>>
>>
>
>

typo

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

--
HTH

Bob Phillips

"Roger Govier" <[email protected]> wrote in message
news:%[email protected]...
> One way would be
> =SUMPRODUCT(--(A2:A100="Closed"),(B2:B100="Settings"))
> Change ranges to suit
>
> --
> Regards
> Roger Govier
> "kiranmani" <[email protected]> wrote in message
> news:[email protected]...
> > 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 ..
>
>

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.

--
Regards
Roger Govier
"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> typo
>
> =SUMPRODUCT(--(A2:A100="Closed"),--(B2:B100="Settings"))
>
> --
> HTH
>
> Bob Phillips
>
> "Roger Govier" <[email protected]> wrote in message
> news:%[email protected]...
>> One way would be
>> =SUMPRODUCT(--(A2:A100="Closed"),(B2:B100="Settings"))
>> Change ranges to suit
>>
>> --
>> Regards
>> Roger Govier
>> "kiranmani" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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 ..
>>
>>
>
>

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

--
HTH

Bob Phillips

"Roger Govier" <[email protected]> wrote in message
news:%[email protected]...
> 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.
>
> --
> Regards
> Roger Govier
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > typo
> >
> > =SUMPRODUCT(--(A2:A100="Closed"),--(B2:B100="Settings"))
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Roger Govier" <[email protected]> wrote in message
> > news:%[email protected]...
> >> One way would be
> >> =SUMPRODUCT(--(A2:A100="Closed"),(B2:B100="Settings"))
> >> Change ranges to suit
> >>
> >> --
> >> Regards
> >> Roger Govier
> >> "kiranmani" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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 ..
> >>
> >>
> >
> >
>
>

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

--
Regards
Roger Govier
"kiranmani" <[email protected]> wrote in message
news:[email protected]...
> 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 ..

Might be of interest:

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

Roger Govier wrote:
> 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.

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 ..

Hi Bob & Aladin

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

--
Regards
Roger Govier
"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> --
> HTH
>
> Bob Phillips
>
> "Roger Govier" <[email protected]> wrote in message
> news:%[email protected]...
>> 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.
>>
>> --
>> Regards
>> Roger Govier
>> "Bob Phillips" <[email protected]> wrote in message
>> news:[email protected]...
>> > typo
>> >
>> > =SUMPRODUCT(--(A2:A100="Closed"),--(B2:B100="Settings"))
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > "Roger Govier" <[email protected]> wrote in message
>> > news:%[email protected]...
>> >> One way would be
>> >> =SUMPRODUCT(--(A2:A100="Closed"),(B2:B100="Settings"))
>> >> Change ranges to suit
>> >>
>> >> --
>> >> Regards
>> >> Roger Govier
>> >> "kiranmani" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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 ..
>> >>
>> >>
>> >
>> >
>>
>>
>
>