COUNTIF Function

[preety]

Hi, This is URGENT.

Problem: In MS Excel, Column A contains values from A to Z and Column B
contains value from 1 to 5. Now, I need to count the records that have
"A" has value in Column A and "3" in Column B. I had tried with COUNTIF
function but it works only for one criteria, i.e. it can be used if I
want to count the records with value "A" in Column A or the records
with "5 in Column B. Is there any function that can be used for this
purpose.
Pls treat this very urgent.

 

[Andy B]

Hi

Try using SUMPRODUCT:

=SUMPRODUCT((A2:A200="A")*(B2:B200="3"))

Andy

 

[Paul]

Hi, This is URGENT.

Problem: In MS Excel, Column A contains values from A to Z and Column B
contains value from 1 to 5. Now, I need to count the records that have
"A" has value in Column A and "3" in Column B. I had tried with COUNTIF
function but it works only for one criteria, i.e. it can be used if I
want to count the records with value "A" in Column A or the records
with "5 in Column B. Is there any function that can be used for this
purpose.
Pls treat this very urgent.

=SUMPRODUCT(--(A1:A100="A"),--(B1:B100=3))
Adjust the ranges to suit your data. You cannot use whole column references,
but the range can be longer than you need (i.e. blank rows are fine). Note
that you can have more than two criteria if necessary.

 

[Mark]

Can you not combine the two columns and then count the
cells containing A3?

 

[preety]

Hi Paul, Thanks a lot. It really worked.
Apart from this I found the other way doing the same is PIVOT TABLES
But now the next problem is plotting the chart with that PIVOT TABLE
If the no. of rows or the columns in the PIVOT TABLE changes, it doe
not automatically reflects in the corresponding char.
Note that any change in the data gets reflected but change in number o
Rows and Columns do not get updated in the chart.
Any Info In This Reard.

 

[preety]

Hi Paul, Thanks a lot. It really worked.
Apart from this I found the other way doing the same is PIVOT TABLES
But now the next problem is plotting the chart with that PIVOT TABLE
If the no. of rows or the columns in the PIVOT TABLE changes, it doe
not automatically reflects in the corresponding char.
Note that any change in the data gets reflected but change in number o
Rows and Columns do not get updated in the chart.
Any Info In This Reard