I have a question regarding countif formula. I have two columns have value a
b c d in first and in second column i have 1 2 3 4 5 now i want a formula
that could tell me how many a are there that have 1
=countif(A1:A5, "=1") and (b1:b5,"=a") the formula would be something like
this can anybody help

On Fri, 30 Sep 2005 07:11:02 -0700, Fahad Farid Ansari <Fahad Farid
wrote:

I have a question regarding countif formula. I have two columns have value a
b c d in first and in second column i have 1 2 3 4 5 now i want a formula
that could tell me how many a are there that have 1
=countif(A1:A5, "=1") and (b1:b5,"=a") the formula would be something like
this can anybody help

One solution is an array formula.

={SUM((A1:A5="a")*(B1:B5=1))}

Don't forget you need to enter the formula with CTRL SHIFT and Enter
in order to get the squiggly brackets {}.

HTH






__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Also, SUMPRODUCT instead of SUM - no need for array formula

=SUMPRODUCT(--(A1:A5="a"),--(B1:B5=1))


"Richard Buttrey" wrote:

On Fri, 30 Sep 2005 07:11:02 -0700, Fahad Farid Ansari <Fahad Farid
wrote:

I have a question regarding countif formula. I have two columns have value a
b c d in first and in second column i have 1 2 3 4 5 now i want a formula
that could tell me how many a are there that have 1
=countif(A1:A5, "=1") and (b1:b5,"=a") the formula would be something like
this can anybody help

One solution is an array formula.

={SUM((A1:A5="a")*(B1:B5=1))}

Don't forget you need to enter the formula with CTRL SHIFT and Enter
in order to get the squiggly brackets {}.

HTH






__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

No man what i am looking for is aformula that would give me the total that 1
has 20 a's something like that

"Richard Buttrey" wrote:

On Fri, 30 Sep 2005 07:11:02 -0700, Fahad Farid Ansari <Fahad Farid
wrote:

I have a question regarding countif formula. I have two columns have value a
b c d in first and in second column i have 1 2 3 4 5 now i want a formula
that could tell me how many a are there that have 1
=countif(A1:A5, "=1") and (b1:b5,"=a") the formula would be something like
this can anybody help

One solution is an array formula.

={SUM((A1:A5="a")*(B1:B5=1))}

Don't forget you need to enter the formula with CTRL SHIFT and Enter
in order to get the squiggly brackets {}.

HTH






__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Evidently you have two criteria: value in column A is A and value in column B
is 1. You can't do that with COUNTIF -- it allows for only one criterion.

You can use this formula =SUMPRODUCT(--(A1:A5=1),--(B1:B5="a"))

Or you could use a Pivot Table to get all of the results at once.


On Fri, 30 Sep 2005 18:10:02 -0700, Fahad Farid Ansari
wrote:

No man what i am looking for is aformula that would give me the total that 1
has 20 a's something like that

"Richard Buttrey" wrote:

On Fri, 30 Sep 2005 07:11:02 -0700, Fahad Farid Ansari <Fahad Farid
wrote:

I have a question regarding countif formula. I have two columns have value
a
b c d in first and in second column i have 1 2 3 4 5 now i want a formula
that could tell me how many a are there that have 1
=countif(A1:A5, "=1") and (b1:b5,"=a") the formula would be something like
this can anybody help

One solution is an array formula.

={SUM((A1:A5="a")*(B1:B5=1))}

Don't forget you need to enter the formula with CTRL SHIFT and Enter
in order to get the squiggly brackets {}.

HTH






__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Both the formula's do not work i need to know how many 1's have a's

"Fahad Farid Ansari" wrote:

I have a question regarding countif formula. I have two columns have value a
b c d in first and in second column i have 1 2 3 4 5 now i want a formula
that could tell me how many a are there that have 1
=countif(A1:A5, "=1") and (b1:b5,"=a") the formula would be something like
this can anybody help

On Fri, 30 Sep 2005 18:17:01 -0700, Fahad Farid Ansari
wrote:

Both the formula's do not work i need to know how many 1's have a's

What makes you think that the original formual doesn't give you that?
Given the data below starting in A1 the formula returns the value 2.
i.e of the 4 1's two have 'a'


a 1
a 1
b 1
b 1
a 2


"Fahad Farid Ansari" wrote:

I have a question regarding countif formula. I have two columns have value a
b c d in first and in second column i have 1 2 3 4 5 now i want a formula
that could tell me how many a are there that have 1
=countif(A1:A5, "=1") and (b1:b5,"=a") the formula would be something like
this can anybody help

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________