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

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" wrote:

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

Hi Ronaldo

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

thanks

sajid

"RonaldoOneNil" wrote:

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" wrote:

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

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" wrote:

Hi Ronaldo

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

thanks

sajid

"RonaldoOneNil" wrote:

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" wrote:

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

Hi

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

any other solution??

thanks

sajid

"Mike H" wrote:

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" wrote:

Hi Ronaldo

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

thanks

sajid

"RonaldoOneNil" wrote:

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" wrote:

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

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" wrote:


Hi

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

any other solution??

thanks

sajid

"Mike H" wrote:

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" wrote:

Hi Ronaldo

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

thanks

sajid

"RonaldoOneNil" wrote:

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" wrote:

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

I tried with the below formula

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

and it gave me the NUM error


"Mike H" wrote:

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" wrote:


Hi

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

any other solution??

thanks

sajid

"Mike H" wrote:

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" wrote:

Hi Ronaldo

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

thanks

sajid

"RonaldoOneNil" wrote:

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" wrote:

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

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'!D1:D50000="CKT")*('Clothing TL&T
Sample record'!J1:O50000="App"))

Mike

"sajid" wrote:


I tried with the below formula

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

and it gave me the NUM error


"Mike H" wrote:

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" wrote:


Hi

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

any other solution??

thanks

sajid

"Mike H" wrote:

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" wrote:

Hi Ronaldo

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

thanks

sajid

"RonaldoOneNil" wrote:

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" wrote:

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

Hi Mike

thank for your continuous reply. In fact i tried
=SUMPRODUCT(('Clothing TL&T Sample record'!D: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


"Mike H" wrote:

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'!D1:D50000="CKT")*('Clothing TL&T
Sample record'!J1:O50000="App"))

Mike

"sajid" wrote:


I tried with the below formula

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

and it gave me the NUM error


"Mike H" wrote:

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" wrote:


Hi

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

any other solution??

thanks

sajid

"Mike H" wrote:

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" wrote:

Hi Ronaldo

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

thanks

sajid

"RonaldoOneNil" wrote:

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" wrote:

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