Count entries when two criteria are met
 

I need to count the number of entries in a spreadsheet where a chosen string
appears anywhere in column K and a (different) chosen string ALSO appears in
column D. I have tried to call this by putting a formula in the cell where
the result should appear.

For example - count all the rows where "Smith" appears in column K and "Not
present" appears in column D and put the result in cell A1.

As far as I can discover, you can't use COUNTIF with two parameters and I
believe SUMPRODUCT can be used, although I think I've read that you can't use
SUMPRODUCT on entire columns.

I've read all the posts I can find around this and - sorry all - I can't
really understand what I should do. Would any kind person be able to tell me
in VERY SIMPLE words if this can be done and how?

Thanks in advance - I'm new to this and currently beaten!!

Sue

Count entries when two criteria are met
 

use sumproduct on ranges such as a2:a200. Suggest using a defined name range
that is self adjusting and use that name in the formula.

--
Don Guillett
SalesAid Software

"SueJB" wrote in message
...
I need to count the number of entries in a spreadsheet where a chosen
string
appears anywhere in column K and a (different) chosen string ALSO appears
in
column D. I have tried to call this by putting a formula in the cell
where
the result should appear.

For example - count all the rows where "Smith" appears in column K and
"Not
present" appears in column D and put the result in cell A1.

As far as I can discover, you can't use COUNTIF with two parameters and I
believe SUMPRODUCT can be used, although I think I've read that you can't
use
SUMPRODUCT on entire columns.

I've read all the posts I can find around this and - sorry all - I can't
really understand what I should do. Would any kind person be able to tell
me
in VERY SIMPLE words if this can be done and how?

Thanks in advance - I'm new to this and currently beaten!!

Sue

Count entries when two criteria are met
 

You're right...you can't use SUMPRODUCT when evaluating
entire columns. But the question is, do you really need
to reference entire columns? Do you have 65,536 rows of
data? Try using a formula like:

=SUMPRODUCT((K1:K10000="string1")*(D1:D10000="stri ng2"))

HTH
Jason
Atlanta, GA

-----Original Message-----
I need to count the number of entries in a spreadsheet
where a chosen string
appears anywhere in column K and a (different) chosen
string ALSO appears in
column D. I have tried to call this by putting a
formula in the cell where
the result should appear.

For example - count all the rows where "Smith" appears
in column K and "Not
present" appears in column D and put the result in cell
A1.

As far as I can discover, you can't use COUNTIF with two
parameters and I
believe SUMPRODUCT can be used, although I think I've
read that you can't use
SUMPRODUCT on entire columns.

I've read all the posts I can find around this and -
sorry all - I can't
really understand what I should do. Would any kind
person be able to tell me
in VERY SIMPLE words if this can be done and how?

Thanks in advance - I'm new to this and currently
beaten!!

Sue
.

Count entries when two criteria are met
 

thank you Don, much appreciated

Sue

"Don Guillett" wrote:

use sumproduct on ranges such as a2:a200. Suggest using a defined name range
that is self adjusting and use that name in the formula.

--
Don Guillett
SalesAid Software

"SueJB" wrote in message
...
I need to count the number of entries in a spreadsheet where a chosen
string
appears anywhere in column K and a (different) chosen string ALSO appears
in
column D. I have tried to call this by putting a formula in the cell
where
the result should appear.

For example - count all the rows where "Smith" appears in column K and
"Not
present" appears in column D and put the result in cell A1.

As far as I can discover, you can't use COUNTIF with two parameters and I
believe SUMPRODUCT can be used, although I think I've read that you can't
use
SUMPRODUCT on entire columns.

I've read all the posts I can find around this and - sorry all - I can't
really understand what I should do. Would any kind person be able to tell
me
in VERY SIMPLE words if this can be done and how?

Thanks in advance - I'm new to this and currently beaten!!

Sue

Count entries when two criteria are met
 

Many thanks Jason, you're absolutely right and it works! So simple once
somebody explains it.

Thanks again
S

"Jason Morin" wrote:

You're right...you can't use SUMPRODUCT when evaluating
entire columns. But the question is, do you really need
to reference entire columns? Do you have 65,536 rows of
data? Try using a formula like:

=SUMPRODUCT((K1:K10000="string1")*(D1:D10000="stri ng2"))

HTH
Jason
Atlanta, GA

-----Original Message-----
I need to count the number of entries in a spreadsheet
where a chosen string
appears anywhere in column K and a (different) chosen
string ALSO appears in
column D. I have tried to call this by putting a
formula in the cell where
the result should appear.

For example - count all the rows where "Smith" appears
in column K and "Not
present" appears in column D and put the result in cell
A1.

As far as I can discover, you can't use COUNTIF with two
parameters and I
believe SUMPRODUCT can be used, although I think I've
read that you can't use
SUMPRODUCT on entire columns.

I've read all the posts I can find around this and -
sorry all - I can't
really understand what I should do. Would any kind
person be able to tell me
in VERY SIMPLE words if this can be done and how?

Thanks in advance - I'm new to this and currently
beaten!!

Sue
.