need help on count entires with conditions

[sajid]

Hi

I have an excel sheet with few columns. one column contain factory names.
the factory name repeats. another column contains the status which can be
either "Approved" or "Rejected". I need to count how many times does a
factory got approved status.


please help on which formula can capture these simultaneous entries.

thanks

Sajid

 

[RonaldoOneNil]

Create a pivot table with the factory name as the row heading, Approved and
Rejected as the column headings and the count of Approved or Rejected as the
value.

 

[sajid]

Hi Ronaldo

thanks for your reply. is there anyway that i could do this from formulars?

thanks

sajid

 

[Mike H]

Hi,

Try this

=SUMPRODUCT((A1:A30=C1)*(B1:B30="Approved"))

Where
Column A = factory names
Column B= Status
C1 = Factory you are trying to count for.

Mike

 

[sajid]

Hi

It gives a NUM# error. may be because both columns have text entries to
compare

any other solution??

thanks

sajid

 

[Mike H]

Hi,

Try as I might I can't make this formula produce a NUM# error and it works
perfectly comparing text in 2 columns. Did you use the formula I gave you or
did you modify it. What precisly do you have in these 2 ranges? post some
sample data.

Mike

 

[sajid]

I tried with the below formula

=SUMPRODUCT(('Clothing TL&T Sample record'!D="CKT)*('Clothing TL&T Sample
record'!J:O=""App"))

and it gave me the NUM error

 

[Mike H]

Hi,

You can't use full columns with sumproduct. Is 50000 rows enough? I've also
corrected the Syntax

=SUMPRODUCT(('Clothing TL&T Sample record'!D150000="CKT")*('Clothing TL&T
Sample record'!J1:O50000="App"))

Mike

 

[sajid]

Hi Mike

thank for your continuous reply. In fact i tried
=SUMPRODUCT(('Clothing TL&T Sample record'!D5000="CKT")*('Clothing TL&T
Sample record'!J:O5000="App"))


but now it gives a name# error


what can be the issue?

please help

thanks


sajid