Counting entries in 2+ columns

[petess]

Toppers,

Further to my earlier post, however, I find that I cannot use the formula
you suggest to work with data from ANOTHER worksheet within the same Excel
file... I can still get the answers I need in the same worksheet, but this
would be more elegant.

Petess

[Bob Phillips]

You should be able to quite happily

=SUMPRODUCT(--('Other sheet name'!A1:A10="UAE"),--('Other sheet
name'!B1:B10="Construction"))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"petess" <[email protected]> wrote in message
news:[email protected]...
> Toppers,
>
> Further to my earlier post, however, I find that I cannot use the formula
> you suggest to work with data from ANOTHER worksheet within the same Excel
> file... I can still get the answers I need in the same worksheet, but
this
> would be more elegant.
>
> Petess
>

[Toppers]

This works placed in Sheet2 wth data in Sheet1:

=SUMPRODUCT(--(Sheet1!A1:A10="UAE"),--(Sheet1!B1:B10="Construction"))

(Perhaps you should state your requirements more clearly)

"petess" wrote:

> Toppers,
>
> Further to my earlier post, however, I find that I cannot use the formula
> you suggest to work with data from ANOTHER worksheet within the same Excel
> file... I can still get the answers I need in the same worksheet, but this
> would be more elegant.
>
> Petess
>

[petess]

Toppers and Bob,

That's great. But how about selecting the entire column:

=SUMPRODUCT((ALL!B:B="Bahrain")*(ALL!D:D="Power"))

i.e. B:B rather than B2:B457

?

"Bob Phillips" wrote:

> You should be able to quite happily
>
> =SUMPRODUCT(--('Other sheet name'!A1:A10="UAE"),--('Other sheet
> name'!B1:B10="Construction"))
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "petess" <[email protected]> wrote in message
> news:[email protected]...
> > Toppers,
> >
> > Further to my earlier post, however, I find that I cannot use the formula
> > you suggest to work with data from ANOTHER worksheet within the same Excel
> > file... I can still get the answers I need in the same worksheet, but
> this
> > would be more elegant.
> >
> > Petess
> >
>
>
>

[Bob Phillips]

No you can't select the entire column. SP works on arrays, and as such is
limited to a defined range. It can be upto 65535 rows, but not the entire
column.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"petess" <[email protected]> wrote in message
news:[email protected]...
> Toppers and Bob,
>
> That's great. But how about selecting the entire column:
>
> =SUMPRODUCT((ALL!B:B="Bahrain")*(ALL!D:D="Power"))
>
> i.e. B:B rather than B2:B457
>
> ?
>
> "Bob Phillips" wrote:
>
> > You should be able to quite happily
> >
> > =SUMPRODUCT(--('Other sheet name'!A1:A10="UAE"),--('Other sheet
> > name'!B1:B10="Construction"))
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "petess" <[email protected]> wrote in message
> > news:[email protected]...
> > > Toppers,
> > >
> > > Further to my earlier post, however, I find that I cannot use the
formula
> > > you suggest to work with data from ANOTHER worksheet within the same
Excel
> > > file... I can still get the answers I need in the same worksheet, but
> > this
> > > would be more elegant.
> > >
> > > Petess
> > >
> >
> >
> >

[Toppers]

SUMPRODUCT doesn't allow selection by columns. If you want to do that then
use B1:B65535.

"petess" wrote:

> Toppers and Bob,
>
> That's great. But how about selecting the entire column:
>
> =SUMPRODUCT((ALL!B:B="Bahrain")*(ALL!D:D="Power"))
>
> i.e. B:B rather than B2:B457
>
> ?
>
> "Bob Phillips" wrote:
>
> > You should be able to quite happily
> >
> > =SUMPRODUCT(--('Other sheet name'!A1:A10="UAE"),--('Other sheet
> > name'!B1:B10="Construction"))
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "petess" <[email protected]> wrote in message
> > news:[email protected]...
> > > Toppers,
> > >
> > > Further to my earlier post, however, I find that I cannot use the formula
> > > you suggest to work with data from ANOTHER worksheet within the same Excel
> > > file... I can still get the answers I need in the same worksheet, but
> > this
> > > would be more elegant.
> > >
> > > Petess
> > >
> >
> >
> >

[petess]

Thanks for your help, guys. Appreciated. Am very impressed!

"Bob Phillips" wrote:

> You should be able to quite happily
>
> =SUMPRODUCT(--('Other sheet name'!A1:A10="UAE"),--('Other sheet
> name'!B1:B10="Construction"))
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "petess" <[email protected]> wrote in message
> news:[email protected]...
> > Toppers,
> >
> > Further to my earlier post, however, I find that I cannot use the formula
> > you suggest to work with data from ANOTHER worksheet within the same Excel
> > file... I can still get the answers I need in the same worksheet, but
> this
> > would be more elegant.
> >
> > Petess
> >
>
>
>