use =countif to generate returns from more than one column
 

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?

use =countif to generate returns from more than one column
 

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?

use =countif to generate returns from more than one column
 

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

use =countif to generate returns from more than one column
 

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

use =countif to generate returns from more than one column
 

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

use =countif to generate returns from more than one column
 

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

use =countif to generate returns from more than one column
 

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?

use =countif to generate returns from more than one column
 

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?