COUNTIFs: Multiple ranges, single criterion with OR logic

I have the following sample data:

Book1.png

What I want to do:
-Count the number of events that were organised on EITHER Agenda 1 OR Agenda 2 between March 09, 2017 and June 15, 2017 by each individual.
Example: Number of events organised by A is 2 (if event has yes in both agenda 1 and agenda 2, it needs to be counted as ONE)

What I have been able to do:
I have been using COUNTIFs to do this. Have no trouble whatsoever with using criteria for name, date, type. Following is an example of what my formula looks like:

=COUNTIFS(Activity_Details!B:B, VLOOKUP(A4,Activity_Details!B:B,1,FALSE), Activity_Details!C:C, "Event", Activity_Details!D:D,">="&DATE(2017,3,9),Activity_Details!D:D,"<="&DATE(2017,6,15), Activity_Details!H:H, "Yes")

*Where:
Sheet name: Activity_Details
Col C = Type
Col H = Agenda 1
Col I = Agenda 2
Col D = Date
Col B = Name

What I need help with:
Within my COUNTIFs, I want to nest a single criterion (="Yes") for two ranges (Agenda 1, Agenda 2) using OR logic, whereby, if both columns have "Yes", it is counted as one.

Anyone to help would be a savior.

Hi,

Try:

=SUM(COUNTIFS(Activity_Details!B:B,VLOOKUP(A4,Activity_Details!B:B,1,FALSE),Activity_Details!C:C,"Event",Activity_Details!D:D,">="&DATE(2017,3,9),Activity_Details!D:D,"<="&DATE(2017,6,15),Activity_Details!H:H,{"=","=","<>"}&"Yes",Activity_Details!I:I,{"=","<>","="}&"Yes"))

although I have to confess that I don't understand the part:

VLOOKUP(A4,Activity_Details!B:B,1,FALSE)

which you are using, since this will always return a result which is precisely the same as simply:

A4

Regards

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

If in reality the number of columns to consider for the OR criterion is quite large, then it's best to switch to an array formula, since otherwise the required COUNTIFS constructions become both lengthy and impractical.

For example, assuming this new Agenda 3 is in column J, you would require:

=SUM(COUNTIFS(Activity_Details!B:B,VLOOKUP(A4,Activity_Details!B:B,1,FALSE),Activity_Details!C:C,"Event",Activity_Details!D:D,">="&DATE(2017,3,9),Activity_Details!D:D,"<="&DATE(2017,6,15),Activity_Details!H:H,{"=","=","=","=","<>","<>","<>"}&"Yes",Activity_Details!I:I,{"=","=","<>","<>","=","=","<>"}&"Yes",Activity_Details!J:J,{"=","<>","=","<>","=","<>","="}&"Yes"))

The point is to create all permutations from the three columns where at least one of the entries is "Yes". As you can see, however, although the above is perfectly valid, it's neither readily understandable nor easily extendible.

I would suggest the following array formula**:

=SUM(IF(Activity_Details!B1:B100=VLOOKUP(A4,Activity_Details!B:B,1,FALSE),IF(Activity_Details!C1:C100="Event",IF(Activity_Details!D1:D100>=DATE(2017,3,9),IF(Activity_Details!D1:D100<=DATE(2017,6,15),IF(MMULT(0+(Activity_Details!H1:J100="Yes"),TRANSPOSE(COLUMN(Activity_Details!H1:J100)^0))>0,1))))))

again, assuming that the new Agenda 3 column is column J (if not, let me know).

The only slight downside to this approach is that you would now be strongly advised to not use entire column references (else the above will take an age to calculate). Hence my choice of an upper row reference of 100 here, which obviously you can change (though being sure not to make it too large).

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Then use:

=SUM(IF(Activity_Details!B1:B100=VLOOKUP(A4,Activity_Details!B:B,1,FALSE),IF(Activity_Details!C1:C100="Event",IF(Activity_Details!D1:D100>=DATE(2017,3,9),IF(Activity_Details!D1:D100<=DATE(2017,6,15),IF((Activity_Details!E1:E100="Yes")+(Activity_Details!H1:H100="Yes")+(Activity_Details!I1:I100="Yes"),1))))))

Regards

So just replace the 1 at the end of my last formula with the appropriate sum range, i.e.:

=SUM(IF(Activity_Details!B1:B100=VLOOKUP(A4,Activity_Details!B:B,1,FALSE),IF(Activity_Details!C1:C100="Event",IF(Activity_Details!D1:D100>=DATE(2017,3,9),IF(Activity_Details!D1:D100<=DATE(2017,6,15),IF((Activity_Details!E1:E100="Yes")+(Activity_Details!H1:H100="Yes")+(Activity_Details!I1:I100="Yes"),Activity_Details!Y1:Y100))))))

Regards