COUNTIF with two variables

Laurie,

What is the date supposed to match? The example below assumes the date to match to is in A2, and the data is in rows 4 thru 14. This formula is an Array formula, which means if must be entered using Control+Shift+Enter, rather than just Enter. (You should end up with curly braces around the formula after it is entered, but don't enter them yourself, Excel does it).

=COUNT(IF(A4:A14=A2,IF(T4:T14="Passed",A4:A14)))

Let me know if you need further clarification.

Chris

Assume data in A3:T100.
In U3 enter =CONCATENATE(A3,T3)and fill down through U100.
In V101 enter word Passed. In U102 enter =CONCATENATE(U101,V101).
In U103 enter =COUNTIF(U3:U100,U102).
Now enter the date of interest in U101 and the result is
displayed in U103.

I am actually doing the calculation from another page so I tried;
=COUNT(IF('All Deployments'!A:A="1-Oct-99",IF('All Deployments'!T:T="Passed",A:A)))
I hoped that would include all the possible records in the columns of A and T on the page called All Deployments. (When I tried to use the formula wizard that is the format it used for the whole column.)
I tried what you suggested with the changes I thought would make it work, but, no luck.
Thank you for your help!
Laurie

Laurie, it doesn't work because the range you specified
=A:A will conatain "Null" data and your formula
is looking for a string, I think your dates will
actually be stored as integer values so; try this;
{=COUNT(IF('All Deployments'!A1:A17=VALUE("1 Oct 99")*('All Deployments'!T1:T17="Passed"),('All Deployments'!A1:A17)))}
Note: as Chris said it is a FormulaArray so it should
be entered via CTRL SHFT ENTER key combination,
you will see the curly braces if it is entered
correctly. For more info look at Mrexcels Tips
on CSE formulas.
The only criteria for this formula is,
1) You have to set the range eg A1:A17
2) There can be no blanks in this range (COLUMN"A")
3) You set the Date to compare it to.
other wise it should work ??
TEST it.

Regards

Ivan

Hi Laurie, don't give up so easely. Earlier I tried to give you a brute force method but the FormulaArray from Chris that you tried will work.
Assume your data is on sheet All Deployments and you want the results of the count to appear on a sheet named Result in cell B3 whenever you enter a date in cell A3 on sheet Result. Assume the dates in the data is in A3:A150 and the Passed or Failed (or whatever) is in T3:T150. The FormulaArray you enter on sheet Result in cell B3 is:

{=COUNT(IF('All Deployments'!A3:A150=A3,IF('All Deployments'!T3:T150="Passed",'All Deployments'!A3:A150)))}

You will get a new count each time you enter a different date in A3. Also, blanks didn't cause a problem it the few tests I ran.

Alternatively, you could go to sheet All Deployments and highlight the data in A3:A150 and name it Cat, highlight the data in T3:T150 and name it Dog (if you really do want all possible entries in column A then click the top to highlight the entire column and name it Cat and do the same for the T column and name it Dog), and the FormulaArray in cell B3 on sheet Result is entered as:
{=COUNT(IF(Cat=A3,IF(Dog="Passed",Cat)))}

I know its becoming redundent, but remember the FormulaArray is entered via the CTRL SHFT ENTER key combination.

Mark