Countif criteria with IF/OR logic across multiple sheets and columns

Question

I've been struggling between the SUMPRODUCT and COUNTIFS formulas as there are a lot of specific dependencies in my data. Wondering if anyone can shed a bit more light on this issue.

Have tried SUMPRODUCT and COUNTIFS which give me calculations based on 1 set, but I need to include additional if/or statements.

I have the following:

| ID | Size    | Dead/Alive | Duration  | Days | Pass/Fil | Reason   |
|----|---------|------------|-----------|------|----------|----------|
| 1  | Full    | Dead       | Permanent | 125  | Pass     | Comments |
| 2  | Partial | Alive      | Permanent | 500  | Pass     |          |
| 3  | Other   | Dead       | Temporary | 180  | Fail     | Comments |
| 4  | No      | Dead       | Temporary | 225  | Fail     | Comments |
| 5  | Yes     | Alive      | Permanent | 200  | Pass     |          |

with the following rules:

Only Count the ID/ROW if:

1) Values in column A = Full, Partial or Other

OR...

2) Values in column A = No AND values in column B = Dead

OR...

3) If values in column C = Permanent AND values in column D = >=100 or <=200

OR

4) If values in column C = Temporary AND values in column E = Pass, Fail AND column F=not blank

By my calculations, the total should be 5, but this is just a small sampling of my total data. Just not sure how to get that in Excel with either Sumproduct, Countifs or even someone suggested a Lookup function, although Ive never used that one.

Your best approach is to create a helper column that tell you if each row needs to be counted and then count the helper column. Otherwise you may end up with a really long formula to avoid counting the same row twice — Kaiser
Please share some simple mock-up sample date in a markdown template and let us know what formula you tried and what the desired outcome is. — JvdV
Thanks. I already have 1 column where I've determined the difference between 2 values for the 100-200 range, although I can likely create a 2nd helper to determine if its true or false based on my criteria, but the area that I'm stuck in is how to combine an OR and AND statement in one function for multiple sets of criteria. Not sure that I can, or know how to add additional helper columns for those values as well. — SLM
Just to clarify as well, I need to come up with only 1 total calculation here, not 2 based on the OR statement. — SLM
Like I asked, you need to edit your question and provide some simple mock-up data in markdown format. Use a simple tool like this. Just reading your data makes no sense. Include what your expected outcome with sample data would be. — JvdV

Terry W Terry W · Accepted Answer · 2019-08-02T03:24:16

Given that you have so many different conditions, I have to break it down one by one and create a few helper columns to account for each condition.

In my solution I created 10 helper columns as shown below, and I have added some sample data (ID 6 to 29) to test the solution.

I also named 7 conditions in my solution:

Cond_1 Values in column A = Full, Partial or Other

Cond_2 Values in column A = No AND values in column B = Dead

Cond_3A Values in column C = Permanent

Cond_3B Values in column D >=100

Cond_3C Values in column D <=200

Cond_3A, Cond_3B and Cond_3C must be TRUE at the same time

Cond_4 Values in column C = Temporary AND values in column E = Pass

Cond_5A Values in column C = Temporary AND values in column E = Fail

Cond_5B Column F is not blank (I did not give a name to this condition)

Cond_5A and Cond_5B must be TRUE at the same time

Please note my Cond_4, Cond_5A and Cond_5B are all related to your original condition 4), which reads a bit odd, and I am not 100% sure if my interpretation of the condition is correct. If not please re-state your last condition and I can amend my answer accordingly.

As shown in my screen-shot, the formulas in I2 to Q2 are listed in Column U. I only used MAX, AND, SUM, =, &, and/or <> to interpret each condition. Please note some of the formulas are Array Formula so you need to press Ctrl+Shift+Enter to make it work.

The To Count column is simply asking whether the SUM of the previous 9 columns is greater than 1, which means at least one of the conditions is met. If so returns 1 otherwise 0.

Then you just need to work out the total of To Count column. In my example it is 22. I have highlighted the entries that did not meet any of the given condition.

You can use only one helper column to capture all conditions in one formula, but I would not recommend it as it would be too long to be easily understood and modified in future.

{=--(SUM(MAX(--(A2=Cond_1)),MAX(--(A2&B2=Cond_2)),--(SUM(--(C2=Cond_3A),--(AND(D2>=Cond_3B,D2<=Cond_3C)))=2),MAX(--((C2&E2)=Cond_4)),--(SUM(MAX(--((C2&E2)=Cond_5)),--(F2<>""))=2))>0)}

Ps. I would also wonder if there is a formula-based solution without using any helper column...? :)

Countif criteria with IF/OR logic across multiple sheets and columns

1 Answers