I am trying to generate returns from data in more than one column. E.g.
country and sector, so I want to count all the countries that are UAE as well
as Construction. Or Bahrain and Power, Qatar and Industry. Any ideas, guys?

Try:

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

HTH

"petess" wrote:

I am trying to generate returns from data in more than one column. E.g.
country and sector, so I want to count all the countries that are UAE as well
as Construction. Or Bahrain and Power, Qatar and Industry. Any ideas, guys?

Try SUMPRODUCT.

Assuming your range is A1:B10.

=SUMPRODUCT((A1:A10="UAE")*(B1:B10="Construction") )

You could also type your variables in a cell and use the cell
references so UAE in C1 and Construction in C2

=SUMPRODUCT((A1:A10=C1)*(B1:B10=C2))


HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=529186

My post assumed you are trying to count the number of occurences. If
you are trying to "return" data from a different column if the
conditions are met, that would be different.

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=529186

Not much

=SUMPRODUCT(--(A1:A10="UAE"),--(B1:B10="Construction"),C1:C10)


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"SteveG" wrote in
message ...

My post assumed you are trying to count the number of occurences. If
you are trying to "return" data from a different column if the
conditions are met, that would be different.

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile:
http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=529186

Bob,

You're right. Not much different.


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=529186

Guys, Many thanks to you all! You just saved/made my day!! Petess.

"Toppers" wrote:

Try:

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

HTH

"petess" wrote:

I am trying to generate returns from data in more than one column. E.g.
country and sector, so I want to count all the countries that are UAE as well
as Construction. Or Bahrain and Power, Qatar and Industry. Any ideas, guys?

Toppers,

However, I find that I cannot use this formula to work with data from
ANOTHER worksheet within the same Excel file... :o(

Petess

"Toppers" wrote:

Try:

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

HTH

"petess" wrote:

I am trying to generate returns from data in more than one column. E.g.
country and sector, so I want to count all the countries that are UAE as well
as Construction. Or Bahrain and Power, Qatar and Industry. Any ideas, guys?