How can i countif in one cell for two different columns and two different
criteria
i.e.
A B
1 x 1
2 y -0
3 x 2
4 y 9
5 y -1
How can i know how many x are less than 0. and also in case x and y is text
in a specific cell

Hi,

the first part

=SUMPRODUCT((A1:A5="x")*(B1:B5<0))

and also in case x and y is text
in a specific cell

I don't understand this bit. What answer do you expect to get from the
posted data?

Mike


"Lala" wrote:

How can i countif in one cell for two different columns and two different
criteria
i.e.
A B
1 x 1
2 y -0
3 x 2
4 y 9
5 y -1
How can i know how many x are less than 0. and also in case x and y is text
in a specific cell

=SUMPRODUCT(--(A2:A20=letter_cell),--(B2:B20<0))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Lala" wrote in message
...
How can i countif in one cell for two different columns and two different
criteria
i.e.
A B
1 x 1
2 y -0
3 x 2
4 y 9
5 y -1
How can i know how many x are less than 0. and also in case x and y is
text
in a specific cell

it isnt working :-(
I mean i have certain brand written in cells for example KFC, Pizza Hut, etc,
Then i have the branch of these brand (i.e. KFC washington, KFC Chicago,
Pizza Hut Los Angeles, etc.) lisetd all together in one column say A1:A500,
then i have the corresponding returns of these branches listed in the column
B1:B500 (i.e. 500, -300, 600, -200, etc.)
How can i know how many KFC branches are losing and how much is the loss.
I used sumif and countif and the wildcharacter * to determine the total
returns of each brand and teh number of branches for each brand but cant go
any further

"Mike H" wrote:

Hi,

the first part

=SUMPRODUCT((A1:A5="x")*(B1:B5<0))

and also in case x and y is text
in a specific cell

I don't understand this bit. What answer do you expect to get from the
posted data?

Mike


"Lala" wrote:

How can i countif in one cell for two different columns and two different
criteria
i.e.
A B
1 x 1
2 y -0
3 x 2
4 y 9
5 y -1
How can i know how many x are less than 0. and also in case x and y is text
in a specific cell

Can anyone please help me with this, i really cant figure it out as i explained

"Bob Phillips" wrote:

=SUMPRODUCT(--(A2:A20=letter_cell),--(B2:B20<0))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Lala" wrote in message
...
How can i countif in one cell for two different columns and two different
criteria
i.e.
A B
1 x 1
2 y -0
3 x 2
4 y 9
5 y -1
How can i know how many x are less than 0. and also in case x and y is
text
in a specific cell

Hi,

Don't despair, someone will help but I for one can't visualise you data
layout.
Upload copy of your worksheet to the site below and post the link.

http://www.savefile.com/

Mike

"Lala" wrote:

Can anyone please help me with this, i really cant figure it out as i explained

"Bob Phillips" wrote:

=SUMPRODUCT(--(A2:A20=letter_cell),--(B2:B20<0))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Lala" wrote in message
...
How can i countif in one cell for two different columns and two different
criteria
i.e.
A B
1 x 1
2 y -0
3 x 2
4 y 9
5 y -1
How can i know how many x are less than 0. and also in case x and y is
text
in a specific cell

I posted a sample file on this link. If u can help i shall be gratefull
http://www.savefile.com/files/1822437


"Mike H" wrote:

Hi,

Don't despair, someone will help but I for one can't visualise you data
layout.
Upload copy of your worksheet to the site below and post the link.

http://www.savefile.com/

Mike

"Lala" wrote:

Can anyone please help me with this, i really cant figure it out as i explained

"Bob Phillips" wrote:

=SUMPRODUCT(--(A2:A20=letter_cell),--(B2:B20<0))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Lala" wrote in message
...
How can i countif in one cell for two different columns and two different
criteria
i.e.
A B
1 x 1
2 y -0
3 x 2
4 y 9
5 y -1
How can i know how many x are less than 0. and also in case x and y is
text
in a specific cell

hI,

will this do?

http://www.savefile.com/files/1822488

Mike

"Lala" wrote:

I posted a sample file on this link. If u can help i shall be gratefull
http://www.savefile.com/files/1822437


"Mike H" wrote:

Hi,

Don't despair, someone will help but I for one can't visualise you data
layout.
Upload copy of your worksheet to the site below and post the link.

http://www.savefile.com/

Mike

"Lala" wrote:

Can anyone please help me with this, i really cant figure it out as i explained

"Bob Phillips" wrote:

=SUMPRODUCT(--(A2:A20=letter_cell),--(B2:B20<0))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Lala" wrote in message
...
How can i countif in one cell for two different columns and two different
criteria
i.e.
A B
1 x 1
2 y -0
3 x 2
4 y 9
5 y -1
How can i know how many x are less than 0. and also in case x and y is
text
in a specific cell

Actually it worked :-)
Thank u very much, i really needed it.
Thanks again

"Mike H" wrote:

hI,

will this do?

http://www.savefile.com/files/1822488

Mike

"Lala" wrote:

I posted a sample file on this link. If u can help i shall be gratefull
http://www.savefile.com/files/1822437


"Mike H" wrote:

Hi,

Don't despair, someone will help but I for one can't visualise you data
layout.
Upload copy of your worksheet to the site below and post the link.

http://www.savefile.com/

Mike

"Lala" wrote:

Can anyone please help me with this, i really cant figure it out as i explained

"Bob Phillips" wrote:

=SUMPRODUCT(--(A2:A20=letter_cell),--(B2:B20<0))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Lala" wrote in message
...
How can i countif in one cell for two different columns and two different
criteria
i.e.
A B
1 x 1
2 y -0
3 x 2
4 y 9
5 y -1
How can i know how many x are less than 0. and also in case x and y is
text
in a specific cell