ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   I have a question regarding countif formula. (https://www.excelbanter.com/excel-worksheet-functions/48013-i-have-question-regarding-countif-formula.html)

Fahad Farid Ansari

I have a question regarding countif formula.
 
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
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
__________________________

Duke Carey

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
__________________________


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

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


Fahad Farid Ansari

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


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.


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
__________________________


Richard Buttrey

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
__________________________


All times are GMT +1. The time now is 01:52 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com