Hi, This is URGENT.

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

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

Try something like: =SUMPRODUCT((A1:A100="Z")*(B1:B100=3))

Adjust range to suit
(but both ranges must be identical).

--
hth
Max
-----------------------------------------
Please reply in newsgroup

Use xdemechanik
<at>yahoo<dot>com
for email
-------------------------------------------
"preety" <(E-Mail Removed)> wrote in message
news(E-Mail Removed)...
>
> 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.
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>

Use the CONCATENATE function to create a third column.
CONCATENATE is used to join the contents of 2 or more cells into one cell

eg
if cell A2 contains 'A' and cell B2 contains '3'

in cell C2 enter =CONCATENATE(A2,B2)
the result of the CONCATENATE function will be 'A3'
copy the formula down the column and perform your COUNTIF function on this
column for A3


"preety" <(E-Mail Removed)> wrote in message
news(E-Mail Removed)...
>
> 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.
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>

try this one in column c

IF(AND(COUNTIF(A1,"a")=1,COUNTIF(B1,"3")=1)=TRUE,1,"")

it will return a value=1

if you want to how many cells with A and 3 then just get the sum at th
bottom....
thnks;

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com