Wildcards '*/' in Count IF Statements

[Fiona]

Is there any way to count values for fields which
contain "/2" in the text within an existing Count If
statement with other variables? Ie: how do you incorporate
the wildcard feature to allow all values to be returned
across a series of parameters, if "x" is in the month of
July and in the code of "y" and then if "z" has "/2" count
me the number of "/2"'s. The following statement works if
the last section where the wildcard is included is
removed. How can I get this to work.

=COUNT(IF(DATA!B3:B3000="T301",IF(DATA!
AF2:AF30000="VOID",IF(DATA!S2:S30000="JUL",IF(DATA!
F2:F30000,"*/2",DATA!F2:F30000)))))

 

[AH]

Hi,

A simplified example; column A has names of the months, column B has text
string and some of them include the substring "/2". To count all the rows
that match A="JUL" and B includes "/2", use the following array formula
(this example only includes the lines 2-11):

=SUM((A2:A11="JUL")*NOT(ISERROR(FIND("/2",B2:B11))))

Remember to input array formula by pressing Ctrl + Shift + Enter, not just
Enter.

The "*"-opertation represent a logical AND operation, so if you want more
conditions, just add one more logical comparison. The line
(C2:C11="VOID)*(A2:A11="JUL")*NOT(ISERROR(FIND("/2",B2:B11))) has 3
conditions:

C2:C11="VOID

AND

A2:A11="JUL"

AND

NOT(ISERROR(FIND("/2",B2:B11)))

When all of these return a "TRUE" value, then the SUM-function counts it
as "1". That's because in excel TRUE = 1 and FALSE = 0.

One more thing to remember in array-formulas is, that all the ranges that
are used has to have exactly as many rows and columns (like A2:A11, C2:C11,
B2:B11) other wise an error is created.

Hope this helps. More info about array-functions can be found in
http://www.emailoffice.com/excel/arrays-bobumlas.html

- Asser