use =countif to generate returns from more than one column

[petess]

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?

[SteveG]

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

[Toppers]

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?

[SteveG]

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

[Bob Phillips]

Not much

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


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"SteveG" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[SteveG]

Bob,

You're right. Not much different.

[petess]

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?

[petess]

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?