Countif w/ multiple conditions

[mjack003]

I need some help creating a countif function that will count rows based upon three conditions. I have an index that I need to one: count records that fall under a specific release, two: eliminating duplicate job #s, and three: contain a completion date. An example of my index is:

A B C
1 Release Job # Completion Date
2 03 0045 3/27/04
3 03 0045 3/27/04
4 03 0048
5 03 0050 03/28/04
6 04 0025 05/04/04
7 04 0025 05/04/04
8 05 0001
9 05 1027 04/03/04
10 05 1027 04/03/04

So let's say I need to count all "03" release cards with a unique job number that have been completed. In my example the result should be 2. Any suggestions would be appreciated.

Best Regards,
Mjack

[ajhubble]

I'll have a crack at this. I believe the accepted method of counting up the ocurrence of values in one column depending on the values of multiple other columns involves using the SUMPRODUCT function.

e.g. in your example, to count up the number of completed records for release "03" (which gets automatically converted to the number 3), the formula would be

=SUMPRODUCT((A2:A10=3)*(ISNUMBER(C2:C10)))

A2:A10 is the range holding the release numbers. And C2:C10 is the range holding the completion dates. If the date is blank or not a number, then that row doesn't go towards the count. Of those that are dates, only those rows with "3" in column A are counted.

The result of the above is "3".

The only way I could figure out to weed out all the rows with duplicated job numbers was to insert an extra column (D) next to the table with the following formula:

=AND(ISNUMBER(C2),MATCH(B2,B$1:B$10,0)=ROW())

This will return TRUE if the date cell is a number (i.e. a date) and the job number is the first occurence in the table (so any further rows with the same job number return FALSE).

The formula to obtain all completed jobs for a particular release while weeding out duplicate jobs is then:

=SUMPRODUCT((A1:A10=3)*(D1:D10=TRUE))

We wouldn't need the extra column if Excel could create an array of a specified length on the fly with a particular formula (=AND(ISNUMBER(C2),MATCH(B2,B$1:B$10,0)=ROW()) ) in each cell (would be cool if we could).

Hope that helps.

[mjack003]

Hi Aj,

Haven't had a chance to test out your suggestion but should work like a charm. Was hopin there was a way to go about it w/out using col D since I'm running a slow system at work but should save me some time. Only question, still pretty new to excel, is there a function instead of "isnumber" but more like "nonblank" so any cell that is not blank w/ an "03" in column A will be counted? There are other placeholders in the index that need to be counted in this group. Appreciate the help.

Best Regards,
Mjack

[ajhubble]

Only question, still pretty new to excel, is there a function instead of "isnumber" but more like "nonblank" so any cell that is not blank w/ an "03" in column A will be counted?

That was an assumption on my part that only dates should go towards the count and anything else means the job wasn't completed.

The function you're looking for would be "ISBLANK(reference)". If you want this to return true if the reference is not blank, you can write the following:

=NOT(ISBLANK(reference)). So the formula will then be:

=AND(NOT(ISBLANK(C2)),MATCH(B2,B$1:B$10,0)=ROW())