I have a question regarding countif formula.

F

Fahad Farid Ansari

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
 
R

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
Click to expand...

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
__________________________
 
D

Duke Carey

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

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

Fahad Farid Ansari

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

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.
 
R

Richard Buttrey

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

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
__________________________
 
Top