Excel Countif Multiple Criteria based on ID

Question

I am trying to create a formula that will count an ID in one column if it means several criteria in another column. Other formulas I've seen are close to what I want but none consider ID.

My data look like this:

The formula you see in the formula box is the closest thing I could get but it sums up the number of times it gets all the criteria. My wish list simplified is:

1. to get a 1 in Column G based on ClientEnrollmentID if Column C has "Initial" OR "Annual".
2. same as above except "Initial" AND "Annual".

For example, 1074328692 (the first ClientEnrollmentID--H2) should be 1 for the first wish and 0 for the second wish. 1074331324 (second row--H3-H5) should be 1 because it satisfies the two wishes (it would be okay for 1 to appear in multiple rows for the ClientEnrollmentID).

My third wish is that someone can help me with this. Thanks!

Answer 1

Just math. Maybe this can help:

1. =MIN(COUNTIFS(H:H, H:H, C:C, "Initial") + COUNTIFS(H:H, H:H, C:C,"Annual"), 1)
2. =COUNTIFS(H:H, H:H, C:C, "Initial") * COUNTIFS(H:H, H:H, C:C, "Annual")

Answer 2

A COUNTIFS function can be given OR criteria in the form of an array of constants if you wrap it in a SUM function.

        

The formula in G2 is,

    

The second wish can be derived as either a 1 or 0 with an AND function.

    

If they have to be in one cell, you could concatenate them together with something like a division symbol separating them like this.

        

Of course, that renders them completely ineffective for any totaling or mathematical comparison without parsing the string.