Counting entries in 2+ columns

P

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
 
B

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)
 
T

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)
 
P

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

?
 
B

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)
 
T

Toppers

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