I have a question regarding countif formula.

[Guest]

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]

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

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
__________________________

 

[Guest]

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

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

 

[Guest]

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

 

[Guest]

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

 

[Myrna Larson]

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.

 

[Richard Buttrey]

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


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________