Excel Count Formula
 

I Have a spread sheet if 10,000 records. In column A, I have a salespersons
initials. In another column(ex. R) the cell is either blank or has a 1. 1
states that the product was sold.

Can someone help me out with a formula which will add up the number if both
fields are True. I have tried many formulas but was unseccessful. How would i
be able to write a formula which would look like the following.

Count=(A:A=AD and R:R=1) i need both columns for that record to match in
order for it to add.

PLEASE HELP!!!

Thank You

=SUMPRODUCT(--(A2:A1000="AD"),--(R2:R1000=1))

If the 1 is a text 1 enclose it in apostrophes "1"


Regards,

Peo Sjoblom

"Ronil Patel" wrote:

I Have a spread sheet if 10,000 records. In column A, I have a salespersons
initials. In another column(ex. R) the cell is either blank or has a 1. 1
states that the product was sold.

Can someone help me out with a formula which will add up the number if both
fields are True. I have tried many formulas but was unseccessful. How would i
be able to write a formula which would look like the following.

Count=(A:A=AD and R:R=1) i need both columns for that record to match in
order for it to add.

PLEASE HELP!!!

Thank You

=sumproduct((a1:a10000="AD")*(--r1:r10000=1))



"Ronil Patel" <Ronil wrote in message
...
I Have a spread sheet if 10,000 records. In column A, I have a
salespersons
initials. In another column(ex. R) the cell is either blank or has a 1. 1
states that the product was sold.

Can someone help me out with a formula which will add up the number if
both
fields are True. I have tried many formulas but was unseccessful. How
would i
be able to write a formula which would look like the following.

Count=(A:A=AD and R:R=1) i need both columns for that record to match in
order for it to add.

PLEASE HELP!!!

Thank You

Hi
use SUMPRODUCT:
=SUMPRODUCT(--(A1:A100="AD"),--(R1:R100=1))

Also see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany

"Ronil Patel" <Ronil schrieb im
Newsbeitrag ...
I Have a spread sheet if 10,000 records. In column A, I have a
salespersons
initials. In another column(ex. R) the cell is either blank or has a
1. 1
states that the product was sold.

Can someone help me out with a formula which will add up the number
if both
fields are True. I have tried many formulas but was unseccessful. How
would i
be able to write a formula which would look like the following.

Count=(A:A=AD and R:R=1) i need both columns for that record to
match in
order for it to add.

PLEASE HELP!!!

Thank You