Hello,
I want a row counted only if the row contains the letters "AA" in one cell
and does not contain the letters "NOT REQ'D" in another cell of the same row.

The COUNTIF statement works fine. =COUNTIF(A4:A1000,"AA")

However, I tried adding the AND statement but can't get it to work.

Would someone be able to help please?
Merci,
KA

Try this:

=SUMPRODUCT(--(A4:A1000="AA"),--(B4:B1000<>"NOT REQ'D"))

--
Biff
Microsoft Excel MVP


"KA" <(E-Mail Removed)> wrote in message
news:9B4E0C5E-0399-417F-B032-(E-Mail Removed)...
> Hello,
> I want a row counted only if the row contains the letters "AA" in one cell
> and does not contain the letters "NOT REQ'D" in another cell of the same
> row.
>
> The COUNTIF statement works fine. =COUNTIF(A4:A1000,"AA")
>
> However, I tried adding the AND statement but can't get it to work.
>
> Would someone be able to help please?
> Merci,
> KA
>
>

Biff,

When I entered your formula it doesn't generate an error message, but it
still only counts the total number of "AA" values.

Since the values I'm using are on another worksheet, and the results on
another, the formula I entered was:
=SUMPRODUCT(--('WIs - All Modules'!A4:A1000="AA"),--(F4:F1000<>"NOT REQ'D"))

Would you have any other ideas?
Thanks,
KA

"T. Valko" wrote:
> Try this:
> =SUMPRODUCT(--(A4:A1000="AA"),--(B4:B1000<>"NOT REQ'D"))
> Biff
> Microsoft Excel MVP
>
> > "KA" <(E-Mail Removed)> wrote in message
> news:9B4E0C5E-0399-417F-B032-(E-Mail Removed)...
> > Hello,
> > I want a row counted only if the row contains the letters "AA" in one cell
> > and does not contain the letters "NOT REQ'D" in another cell of the same
> > row.
> >
> > The COUNTIF statement works fine. =COUNTIF(A4:A1000,"AA")
> >
> > However, I tried adding the AND statement but can't get it to work.
> >
> > Would someone be able to help please?
> > Merci,
> > KA
> >
> >
>
>
>

Are you saying that it counts rows where A= "AA" and F= "NOT REQ'D" ?

If that's the case then there's a "problem" with the data in column F. Is
"NOT REQ'D" the only entry in the cell or is it part of what can be in a
cell? In other words, is "NOT REQ'D" a substring of a larger string?

Common problems with data include unseen leading/trailing spaces. Like this:

<space>NOT REQ'D
NOT REQ'D<space>
<space>NOT REQ'D<space>

--
Biff
Microsoft Excel MVP


"KA" <(E-Mail Removed)> wrote in message
news:C1872D5C-2045-4A39-BCF7-(E-Mail Removed)...
> Biff,
>
> When I entered your formula it doesn't generate an error message, but it
> still only counts the total number of "AA" values.
>
> Since the values I'm using are on another worksheet, and the results on
> another, the formula I entered was:
> =SUMPRODUCT(--('WIs - All Modules'!A4:A1000="AA"),--(F4:F1000<>"NOT
> REQ'D"))
>
> Would you have any other ideas?
> Thanks,
> KA
>
> "T. Valko" wrote:
>> Try this:
>> =SUMPRODUCT(--(A4:A1000="AA"),--(B4:B1000<>"NOT REQ'D"))
>> Biff
>> Microsoft Excel MVP
>>
>> > "KA" <(E-Mail Removed)> wrote in message
>> news:9B4E0C5E-0399-417F-B032-(E-Mail Removed)...
>> > Hello,
>> > I want a row counted only if the row contains the letters "AA" in one
>> > cell
>> > and does not contain the letters "NOT REQ'D" in another cell of the
>> > same
>> > row.
>> >
>> > The COUNTIF statement works fine. =COUNTIF(A4:A1000,"AA")
>> >
>> > However, I tried adding the AND statement but can't get it to work.
>> >
>> > Would someone be able to help please?
>> > Merci,
>> > KA
>> >
>> >
>>
>>
>>

Yes, it counts rows where A= "AA" and F= "NOT REQ'D".
I checked the "NOT REQ'D" value, in the data list, and it does not have
leading or following spaces.

Is there any other way of writing the statement?

KA

"T. Valko" wrote:

> Are you saying that it counts rows where A= "AA" and F= "NOT REQ'D" ?
>
> If that's the case then there's a "problem" with the data in column F. Is
> "NOT REQ'D" the only entry in the cell or is it part of what can be in a
> cell? In other words, is "NOT REQ'D" a substring of a larger string?
>
> Common problems with data include unseen leading/trailing spaces. Like this:
>
> <space>NOT REQ'D
> NOT REQ'D<space>
> <space>NOT REQ'D<space>
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "KA" <(E-Mail Removed)> wrote in message
> news:C1872D5C-2045-4A39-BCF7-(E-Mail Removed)...
> > Biff,
> >
> > When I entered your formula it doesn't generate an error message, but it
> > still only counts the total number of "AA" values.
> >
> > Since the values I'm using are on another worksheet, and the results on
> > another, the formula I entered was:
> > =SUMPRODUCT(--('WIs - All Modules'!A4:A1000="AA"),--(F4:F1000<>"NOT
> > REQ'D"))
> >
> > Would you have any other ideas?
> > Thanks,
> > KA
> >
> > "T. Valko" wrote:
> >> Try this:
> >> =SUMPRODUCT(--(A4:A1000="AA"),--(B4:B1000<>"NOT REQ'D"))
> >> Biff
> >> Microsoft Excel MVP
> >>
> >> > "KA" <(E-Mail Removed)> wrote in message
> >> news:9B4E0C5E-0399-417F-B032-(E-Mail Removed)...
> >> > Hello,
> >> > I want a row counted only if the row contains the letters "AA" in one
> >> > cell
> >> > and does not contain the letters "NOT REQ'D" in another cell of the
> >> > same
> >> > row.
> >> >
> >> > The COUNTIF statement works fine. =COUNTIF(A4:A1000,"AA")
> >> >
> >> > However, I tried adding the AND statement but can't get it to work.
> >> >
> >> > Would someone be able to help please?
> >> > Merci,
> >> > KA
> >> >
> >> >
> >>
> >>
> >>
>
>
>

>=SUMPRODUCT(--('WIs - All Modules'!A4:A1000="AA"),--(F4:F1000<>"NOT
>REQ'D"))
>Yes, it counts rows where A= "AA" and F= "NOT REQ'D".

Hmmm....

Are these ranges on 2 different sheets?

Try this:

=SUMPRODUCT(--('WIs - All Modules'!A4:A1000="AA"),--(ISERROR(SEARCH("NOT
REQ'D",F4:F1000))))

--
Biff
Microsoft Excel MVP


"KA" <(E-Mail Removed)> wrote in message
news:40DFC7EC-A04E-4F44-AA59-(E-Mail Removed)...
> Yes, it counts rows where A= "AA" and F= "NOT REQ'D".
> I checked the "NOT REQ'D" value, in the data list, and it does not have
> leading or following spaces.
>
> Is there any other way of writing the statement?
>
> KA
>
> "T. Valko" wrote:
>
>> Are you saying that it counts rows where A= "AA" and F= "NOT REQ'D" ?
>>
>> If that's the case then there's a "problem" with the data in column F. Is
>> "NOT REQ'D" the only entry in the cell or is it part of what can be in a
>> cell? In other words, is "NOT REQ'D" a substring of a larger string?
>>
>> Common problems with data include unseen leading/trailing spaces. Like
>> this:
>>
>> <space>NOT REQ'D
>> NOT REQ'D<space>
>> <space>NOT REQ'D<space>
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "KA" <(E-Mail Removed)> wrote in message
>> news:C1872D5C-2045-4A39-BCF7-(E-Mail Removed)...
>> > Biff,
>> >
>> > When I entered your formula it doesn't generate an error message, but
>> > it
>> > still only counts the total number of "AA" values.
>> >
>> > Since the values I'm using are on another worksheet, and the results on
>> > another, the formula I entered was:
>> > =SUMPRODUCT(--('WIs - All Modules'!A4:A1000="AA"),--(F4:F1000<>"NOT
>> > REQ'D"))
>> >
>> > Would you have any other ideas?
>> > Thanks,
>> > KA
>> >
>> > "T. Valko" wrote:
>> >> Try this:
>> >> =SUMPRODUCT(--(A4:A1000="AA"),--(B4:B1000<>"NOT REQ'D"))
>> >> Biff
>> >> Microsoft Excel MVP
>> >>
>> >> > "KA" <(E-Mail Removed)> wrote in message
>> >> news:9B4E0C5E-0399-417F-B032-(E-Mail Removed)...
>> >> > Hello,
>> >> > I want a row counted only if the row contains the letters "AA" in
>> >> > one
>> >> > cell
>> >> > and does not contain the letters "NOT REQ'D" in another cell of the
>> >> > same
>> >> > row.
>> >> >
>> >> > The COUNTIF statement works fine. =COUNTIF(A4:A1000,"AA")
>> >> >
>> >> > However, I tried adding the AND statement but can't get it to work.
>> >> >
>> >> > Would someone be able to help please?
>> >> > Merci,
>> >> > KA
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

No. The ranges of values are on the same worksheet. The data lists are on
another worksheet.

I tried the latest formula that you posted and it still counts rows where A=
"AA" and F= "NOT REQ'D".

A bit of a mystery, what?

"T. Valko" wrote:

> >=SUMPRODUCT(--('WIs - All Modules'!A4:A1000="AA"),--(F4:F1000<>"NOT
> >REQ'D"))
> >Yes, it counts rows where A= "AA" and F= "NOT REQ'D".
>
> Hmmm....
> Are these ranges on 2 different sheets?
> > Try this:
> > =SUMPRODUCT(--('WIs - All Modules'!A4:A1000="AA"),--(ISERROR(SEARCH("NOT
> REQ'D",F4:F1000))))
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "KA" <(E-Mail Removed)> wrote in message
> news:40DFC7EC-A04E-4F44-AA59-(E-Mail Removed)...
> > Yes, it counts rows where A= "AA" and F= "NOT REQ'D".
> > I checked the "NOT REQ'D" value, in the data list, and it does not have
> > leading or following spaces.
> >
> > Is there any other way of writing the statement?
> >
> > KA
> >
> > "T. Valko" wrote:
> >
> >> Are you saying that it counts rows where A= "AA" and F= "NOT REQ'D" ?
> >>
> >> If that's the case then there's a "problem" with the data in column F. Is
> >> "NOT REQ'D" the only entry in the cell or is it part of what can be in a
> >> cell? In other words, is "NOT REQ'D" a substring of a larger string?
> >>
> >> Common problems with data include unseen leading/trailing spaces. Like
> >> this:
> >>
> >> <space>NOT REQ'D
> >> NOT REQ'D<space>
> >> <space>NOT REQ'D<space>
> >>
> >> --
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "KA" <(E-Mail Removed)> wrote in message
> >> news:C1872D5C-2045-4A39-BCF7-(E-Mail Removed)...
> >> > Biff,
> >> >
> >> > When I entered your formula it doesn't generate an error message, but
> >> > it
> >> > still only counts the total number of "AA" values.
> >> >
> >> > Since the values I'm using are on another worksheet, and the results on
> >> > another, the formula I entered was:
> >> > =SUMPRODUCT(--('WIs - All Modules'!A4:A1000="AA"),--(F4:F1000<>"NOT
> >> > REQ'D"))
> >> >
> >> > Would you have any other ideas?
> >> > Thanks,
> >> > KA
> >> >
> >> > "T. Valko" wrote:
> >> >> Try this:
> >> >> =SUMPRODUCT(--(A4:A1000="AA"),--(B4:B1000<>"NOT REQ'D"))
> >> >> Biff
> >> >> Microsoft Excel MVP
> >> >>
> >> >> > "KA" <(E-Mail Removed)> wrote in message
> >> >> news:9B4E0C5E-0399-417F-B032-(E-Mail Removed)...
> >> >> > Hello,
> >> >> > I want a row counted only if the row contains the letters "AA" in
> >> >> > one
> >> >> > cell
> >> >> > and does not contain the letters "NOT REQ'D" in another cell of the
> >> >> > same
> >> >> > row.
> >> >> >
> >> >> > The COUNTIF statement works fine. =COUNTIF(A4:A1000,"AA")
> >> >> >
> >> >> > However, I tried adding the AND statement but can't get it to work.
> >> >> >
> >> >> > Would someone be able to help please?
> >> >> > Merci,
> >> >> > KA
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Well, this is your problem:

>The ranges of values are on the same worksheet.
>The data lists are on another worksheet.

The formula you posted is testing 2 different sheets:

'WIs - All Modules'!A4:A1000="AA"

And

F4:F1000<>"NOT REQ'D"

Maybe we need this:

=SUMPRODUCT(--('WIs - All Modules'!A4:A1000="AA"),--('WIs - All
Modules'!F4:F1000<>"NOT REQ'D"))

--
Biff
Microsoft Excel MVP


"KA" <(E-Mail Removed)> wrote in message
news:485D7C7F-2745-45E4-9A5B-(E-Mail Removed)...
> No. The ranges of values are on the same worksheet. The data lists are on
> another worksheet.
>
> I tried the latest formula that you posted and it still counts rows where
> A=
> "AA" and F= "NOT REQ'D".
>
> A bit of a mystery, what?
>
> "T. Valko" wrote:
>
>> >=SUMPRODUCT(--('WIs - All Modules'!A4:A1000="AA"),--(F4:F1000<>"NOT
>> >REQ'D"))
>> >Yes, it counts rows where A= "AA" and F= "NOT REQ'D".
>>
>> Hmmm....
>> Are these ranges on 2 different sheets?
>> > Try this:
>> > =SUMPRODUCT(--('WIs - All
>> > Modules'!A4:A1000="AA"),--(ISERROR(SEARCH("NOT
>> REQ'D",F4:F1000))))
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "KA" <(E-Mail Removed)> wrote in message
>> news:40DFC7EC-A04E-4F44-AA59-(E-Mail Removed)...
>> > Yes, it counts rows where A= "AA" and F= "NOT REQ'D".
>> > I checked the "NOT REQ'D" value, in the data list, and it does not have
>> > leading or following spaces.
>> >
>> > Is there any other way of writing the statement?
>> >
>> > KA
>> >
>> > "T. Valko" wrote:
>> >
>> >> Are you saying that it counts rows where A= "AA" and F= "NOT REQ'D" ?
>> >>
>> >> If that's the case then there's a "problem" with the data in column F.
>> >> Is
>> >> "NOT REQ'D" the only entry in the cell or is it part of what can be in
>> >> a
>> >> cell? In other words, is "NOT REQ'D" a substring of a larger string?
>> >>
>> >> Common problems with data include unseen leading/trailing spaces. Like
>> >> this:
>> >>
>> >> <space>NOT REQ'D
>> >> NOT REQ'D<space>
>> >> <space>NOT REQ'D<space>
>> >>
>> >> --
>> >> Biff
>> >> Microsoft Excel MVP
>> >>
>> >>
>> >> "KA" <(E-Mail Removed)> wrote in message
>> >> news:C1872D5C-2045-4A39-BCF7-(E-Mail Removed)...
>> >> > Biff,
>> >> >
>> >> > When I entered your formula it doesn't generate an error message,
>> >> > but
>> >> > it
>> >> > still only counts the total number of "AA" values.
>> >> >
>> >> > Since the values I'm using are on another worksheet, and the results
>> >> > on
>> >> > another, the formula I entered was:
>> >> > =SUMPRODUCT(--('WIs - All Modules'!A4:A1000="AA"),--(F4:F1000<>"NOT
>> >> > REQ'D"))
>> >> >
>> >> > Would you have any other ideas?
>> >> > Thanks,
>> >> > KA
>> >> >
>> >> > "T. Valko" wrote:
>> >> >> Try this:
>> >> >> =SUMPRODUCT(--(A4:A1000="AA"),--(B4:B1000<>"NOT REQ'D"))
>> >> >> Biff
>> >> >> Microsoft Excel MVP
>> >> >>
>> >> >> > "KA" <(E-Mail Removed)> wrote in message
>> >> >> news:9B4E0C5E-0399-417F-B032-(E-Mail Removed)...
>> >> >> > Hello,
>> >> >> > I want a row counted only if the row contains the letters "AA" in
>> >> >> > one
>> >> >> > cell
>> >> >> > and does not contain the letters "NOT REQ'D" in another cell of
>> >> >> > the
>> >> >> > same
>> >> >> > row.
>> >> >> >
>> >> >> > The COUNTIF statement works fine. =COUNTIF(A4:A1000,"AA")
>> >> >> >
>> >> >> > However, I tried adding the AND statement but can't get it to
>> >> >> > work.
>> >> >> >
>> >> >> > Would someone be able to help please?
>> >> >> > Merci,
>> >> >> > KA
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

It is a beautiful thing. It works!

Thanks Biff : )

KA

"T. Valko" wrote:

> Well, this is your problem:
>
> >The ranges of values are on the same worksheet.
> >The data lists are on another worksheet.
>
> The formula you posted is testing 2 different sheets:
>
> 'WIs - All Modules'!A4:A1000="AA"
>
> And
>
> F4:F1000<>"NOT REQ'D"
>
> Maybe we need this:
>
> =SUMPRODUCT(--('WIs - All Modules'!A4:A1000="AA"),--('WIs - All
> Modules'!F4:F1000<>"NOT REQ'D"))
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "KA" <(E-Mail Removed)> wrote in message
> news:485D7C7F-2745-45E4-9A5B-(E-Mail Removed)...
> > No. The ranges of values are on the same worksheet. The data lists are on
> > another worksheet.
> >
> > I tried the latest formula that you posted and it still counts rows where
> > A=
> > "AA" and F= "NOT REQ'D".
> >
> > A bit of a mystery, what?
> >
> > "T. Valko" wrote:
> >
> >> >=SUMPRODUCT(--('WIs - All Modules'!A4:A1000="AA"),--(F4:F1000<>"NOT
> >> >REQ'D"))
> >> >Yes, it counts rows where A= "AA" and F= "NOT REQ'D".
> >>
> >> Hmmm....
> >> Are these ranges on 2 different sheets?
> >> > Try this:
> >> > =SUMPRODUCT(--('WIs - All
> >> > Modules'!A4:A1000="AA"),--(ISERROR(SEARCH("NOT
> >> REQ'D",F4:F1000))))
> >>
> >> --
> >> Biff
> >> Microsoft Excel MVP
> >>
> >>
> >> "KA" <(E-Mail Removed)> wrote in message
> >> news:40DFC7EC-A04E-4F44-AA59-(E-Mail Removed)...
> >> > Yes, it counts rows where A= "AA" and F= "NOT REQ'D".
> >> > I checked the "NOT REQ'D" value, in the data list, and it does not have
> >> > leading or following spaces.
> >> >
> >> > Is there any other way of writing the statement?
> >> >
> >> > KA
> >> >
> >> > "T. Valko" wrote:
> >> >
> >> >> Are you saying that it counts rows where A= "AA" and F= "NOT REQ'D" ?
> >> >>
> >> >> If that's the case then there's a "problem" with the data in column F.
> >> >> Is
> >> >> "NOT REQ'D" the only entry in the cell or is it part of what can be in
> >> >> a
> >> >> cell? In other words, is "NOT REQ'D" a substring of a larger string?
> >> >>
> >> >> Common problems with data include unseen leading/trailing spaces. Like
> >> >> this:
> >> >>
> >> >> <space>NOT REQ'D
> >> >> NOT REQ'D<space>
> >> >> <space>NOT REQ'D<space>
> >> >>
> >> >> --
> >> >> Biff
> >> >> Microsoft Excel MVP
> >> >>
> >> >>
> >> >> "KA" <(E-Mail Removed)> wrote in message
> >> >> news:C1872D5C-2045-4A39-BCF7-(E-Mail Removed)...
> >> >> > Biff,
> >> >> >
> >> >> > When I entered your formula it doesn't generate an error message,
> >> >> > but
> >> >> > it
> >> >> > still only counts the total number of "AA" values.
> >> >> >
> >> >> > Since the values I'm using are on another worksheet, and the results
> >> >> > on
> >> >> > another, the formula I entered was:
> >> >> > =SUMPRODUCT(--('WIs - All Modules'!A4:A1000="AA"),--(F4:F1000<>"NOT
> >> >> > REQ'D"))
> >> >> >
> >> >> > Would you have any other ideas?
> >> >> > Thanks,
> >> >> > KA
> >> >> >
> >> >> > "T. Valko" wrote:
> >> >> >> Try this:
> >> >> >> =SUMPRODUCT(--(A4:A1000="AA"),--(B4:B1000<>"NOT REQ'D"))
> >> >> >> Biff
> >> >> >> Microsoft Excel MVP
> >> >> >>
> >> >> >> > "KA" <(E-Mail Removed)> wrote in message
> >> >> >> news:9B4E0C5E-0399-417F-B032-(E-Mail Removed)...
> >> >> >> > Hello,
> >> >> >> > I want a row counted only if the row contains the letters "AA" in
> >> >> >> > one
> >> >> >> > cell
> >> >> >> > and does not contain the letters "NOT REQ'D" in another cell of
> >> >> >> > the
> >> >> >> > same
> >> >> >> > row.
> >> >> >> >
> >> >> >> > The COUNTIF statement works fine. =COUNTIF(A4:A1000,"AA")
> >> >> >> >
> >> >> >> > However, I tried adding the AND statement but can't get it to
> >> >> >> > work.
> >> >> >> >
> >> >> >> > Would someone be able to help please?
> >> >> >> > Merci,
> >> >> >> > KA
> >> >> >> >
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"KA" <(E-Mail Removed)> wrote in message
news:0B781EF6-F338-44FE-9272-(E-Mail Removed)...
> It is a beautiful thing. It works!
>
> Thanks Biff : )
>
> KA
>
> "T. Valko" wrote:
>
>> Well, this is your problem:
>>
>> >The ranges of values are on the same worksheet.
>> >The data lists are on another worksheet.
>>
>> The formula you posted is testing 2 different sheets:
>>
>> 'WIs - All Modules'!A4:A1000="AA"
>>
>> And
>>
>> F4:F1000<>"NOT REQ'D"
>>
>> Maybe we need this:
>>
>> =SUMPRODUCT(--('WIs - All Modules'!A4:A1000="AA"),--('WIs - All
>> Modules'!F4:F1000<>"NOT REQ'D"))
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "KA" <(E-Mail Removed)> wrote in message
>> news:485D7C7F-2745-45E4-9A5B-(E-Mail Removed)...
>> > No. The ranges of values are on the same worksheet. The data lists are
>> > on
>> > another worksheet.
>> >
>> > I tried the latest formula that you posted and it still counts rows
>> > where
>> > A=
>> > "AA" and F= "NOT REQ'D".
>> >
>> > A bit of a mystery, what?
>> >
>> > "T. Valko" wrote:
>> >
>> >> >=SUMPRODUCT(--('WIs - All Modules'!A4:A1000="AA"),--(F4:F1000<>"NOT
>> >> >REQ'D"))
>> >> >Yes, it counts rows where A= "AA" and F= "NOT REQ'D".
>> >>
>> >> Hmmm....
>> >> Are these ranges on 2 different sheets?
>> >> > Try this:
>> >> > =SUMPRODUCT(--('WIs - All
>> >> > Modules'!A4:A1000="AA"),--(ISERROR(SEARCH("NOT
>> >> REQ'D",F4:F1000))))
>> >>
>> >> --
>> >> Biff
>> >> Microsoft Excel MVP
>> >>
>> >>
>> >> "KA" <(E-Mail Removed)> wrote in message
>> >> news:40DFC7EC-A04E-4F44-AA59-(E-Mail Removed)...
>> >> > Yes, it counts rows where A= "AA" and F= "NOT REQ'D".
>> >> > I checked the "NOT REQ'D" value, in the data list, and it does not
>> >> > have
>> >> > leading or following spaces.
>> >> >
>> >> > Is there any other way of writing the statement?
>> >> >
>> >> > KA
>> >> >
>> >> > "T. Valko" wrote:
>> >> >
>> >> >> Are you saying that it counts rows where A= "AA" and F= "NOT REQ'D"
>> >> >> ?
>> >> >>
>> >> >> If that's the case then there's a "problem" with the data in column
>> >> >> F.
>> >> >> Is
>> >> >> "NOT REQ'D" the only entry in the cell or is it part of what can be
>> >> >> in
>> >> >> a
>> >> >> cell? In other words, is "NOT REQ'D" a substring of a larger
>> >> >> string?
>> >> >>
>> >> >> Common problems with data include unseen leading/trailing spaces.
>> >> >> Like
>> >> >> this:
>> >> >>
>> >> >> <space>NOT REQ'D
>> >> >> NOT REQ'D<space>
>> >> >> <space>NOT REQ'D<space>
>> >> >>
>> >> >> --
>> >> >> Biff
>> >> >> Microsoft Excel MVP
>> >> >>
>> >> >>
>> >> >> "KA" <(E-Mail Removed)> wrote in message
>> >> >> news:C1872D5C-2045-4A39-BCF7-(E-Mail Removed)...
>> >> >> > Biff,
>> >> >> >
>> >> >> > When I entered your formula it doesn't generate an error message,
>> >> >> > but
>> >> >> > it
>> >> >> > still only counts the total number of "AA" values.
>> >> >> >
>> >> >> > Since the values I'm using are on another worksheet, and the
>> >> >> > results
>> >> >> > on
>> >> >> > another, the formula I entered was:
>> >> >> > =SUMPRODUCT(--('WIs - All
>> >> >> > Modules'!A4:A1000="AA"),--(F4:F1000<>"NOT
>> >> >> > REQ'D"))
>> >> >> >
>> >> >> > Would you have any other ideas?
>> >> >> > Thanks,
>> >> >> > KA
>> >> >> >
>> >> >> > "T. Valko" wrote:
>> >> >> >> Try this:
>> >> >> >> =SUMPRODUCT(--(A4:A1000="AA"),--(B4:B1000<>"NOT REQ'D"))
>> >> >> >> Biff
>> >> >> >> Microsoft Excel MVP
>> >> >> >>
>> >> >> >> > "KA" <(E-Mail Removed)> wrote in message
>> >> >> >> news:9B4E0C5E-0399-417F-B032-(E-Mail Removed)...
>> >> >> >> > Hello,
>> >> >> >> > I want a row counted only if the row contains the letters "AA"
>> >> >> >> > in
>> >> >> >> > one
>> >> >> >> > cell
>> >> >> >> > and does not contain the letters "NOT REQ'D" in another cell
>> >> >> >> > of
>> >> >> >> > the
>> >> >> >> > same
>> >> >> >> > row.
>> >> >> >> >
>> >> >> >> > The COUNTIF statement works fine. =COUNTIF(A4:A1000,"AA")
>> >> >> >> >
>> >> >> >> > However, I tried adding the AND statement but can't get it to
>> >> >> >> > work.
>> >> >> >> >
>> >> >> >> > Would someone be able to help please?
>> >> >> >> > Merci,
>> >> >> >> > KA
>> >> >> >> >
>> >> >> >> >
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>