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

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
[email protected]
"SueJB" <[email protected]> wrote in message
news:[email protected]...
> 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

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="string2"))

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
>.
>

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
> [email protected]
> "SueJB" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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="string2"))
>
> 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
> >.
> >
>