| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining COUNTIF and AND functions
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combining COUNTIF and AND functions
Try this:
=SUMPRODUCT(--(A4:A1000="AA"),--(B4:B1000<"NOT REQ'D")) -- Biff Microsoft Excel MVP "KA" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combining COUNTIF and AND functions
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" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combining COUNTIF and AND functions
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: <spaceNOT REQ'D NOT REQ'D<space <spaceNOT REQ'D<space -- Biff Microsoft Excel MVP "KA" wrote in message ... 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" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combining COUNTIF and AND functions
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: <spaceNOT REQ'D NOT REQ'D<space <spaceNOT REQ'D<space -- Biff Microsoft Excel MVP "KA" wrote in message ... 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" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combining COUNTIF and AND functions
=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" wrote in message ... 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: <spaceNOT REQ'D NOT REQ'D<space <spaceNOT REQ'D<space -- Biff Microsoft Excel MVP "KA" wrote in message ... 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" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combining COUNTIF and AND functions
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" wrote in message ... 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: <spaceNOT REQ'D NOT REQ'D<space <spaceNOT REQ'D<space -- Biff Microsoft Excel MVP "KA" wrote in message ... 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" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combining COUNTIF and AND functions
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" wrote in message ... 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" wrote in message ... 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: <spaceNOT REQ'D NOT REQ'D<space <spaceNOT REQ'D<space -- Biff Microsoft Excel MVP "KA" wrote in message ... 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" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combining COUNTIF and AND functions
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" wrote in message ... 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" wrote in message ... 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: <spaceNOT REQ'D NOT REQ'D<space <spaceNOT REQ'D<space -- Biff Microsoft Excel MVP "KA" wrote in message ... 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" wrote in message ... 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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Combining COUNTIF and AND functions
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "KA" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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: <spaceNOT REQ'D NOT REQ'D<space <spaceNOT REQ'D<space -- Biff Microsoft Excel MVP "KA" wrote in message ... 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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| combining IF, AND, and OR Functions | Excel Worksheet Functions | |||
| Combining Functions | Excel Discussion (Misc queries) | |||
| Combining IF & AND functions | Excel Discussion (Misc queries) | |||
| Combining LOOKUP and COUNTIF functions | Excel Worksheet Functions | |||
| Combining COUNTIF and AND functions | Excel Worksheet Functions | |||
Linear Mode
