How to sum column with multiple "OR" and "AND" criteria?

[VancityPlanner]

Background:

I have a event activity dataset with columns for "Attendance", "Start/End Date", "Start/End Time", "Day of the Week", and miscellaneous criteria. I am trying to create a timetable that sums attendance depending on the date and hour of the day. I have developed a formula using SUMPRODUCT and ISNUMBER & MATCH. However, I have several questions in order to improve the elegance and functionality of the formula.

Questions:

1. How do I incorporate "Day of the Week" (J3:P3) into the formula using an 'OR' condition? I'm currently having to duplicate the formula and swap out the reference for the day of the week and use a simple "+" to add up the results. I don't know how to correctly specify "Day of the Week" in either the "SUMPRODUCT" or the "ISNUMBER(MATCH" section.
2. I had to create a new column for my "Start/End Time" using an integer value, e.g., 9:00 am = '9', 10:00 am = '10' (H3:I4). It would be nice if I can directly use the "Start/End Time" column (F3:G3).
3. I only show the formula example for 'Sunday', 'Monday', and 'Tuesday' for simplicity in the example. Because I'm referencing a column that specifies the text value of the day (B14:B18), I had to replace the original dummy values ('Y'/'N') in the data (J3:L3) to match the exact string value ('Sunday'/etc.). It would be nice to retain the original dummy values (see "Wednesday" to "Saturday" columns; M2:P2).

Requirements:

1. Satisfy values for the criteria columns Column A, B, and C), e.g., Criteria 1 (Cell B7), Criteria 2 (Cell B8), Criteria 3 (Cell B9). This is an 'AND' condition. There may be multiple values for each criteria, so this needs to be a range.
2. Satisfy values that are between the date range (Column D and E), e.g., '2020-09-07' in Cell A14 when Cell D3 is '2020-09-06' and Cell DE is '2020-09-12'?
3. Satisfy values that are between the time range (Column F and G), e.g., '10am' in Cell E11 when Cell F3 is '9am' and Cell G3 is '12pm'?

Attachments:
Refer to .XLS file in attachment.

[Ochimus]

Vancity,

Try the attached.

Days and Dates are in Cols F1 - L2.

F3 - L4 show whether people attended a session or not..

Hours are clock hours in row 12.

Formula in B13, copied across to I13, is:

=SUMIFS($M$3:$M$4,$F$3:$F$4,"Y",$D$3:$D$4,"<="&B$12,$E$3:$E$4,">="&B$12+TIME(0,59,0))

(For each "hour" bloc, add the total in Col M where Col F shows people attended a session on the date in Col A, and the Start Time is before or on the hour in Row 12, and the End Time is more than or equal to the hour plus fifty nine minutes)

For rows 14 - 19 you change the "date element" ($F$3:$F$4) for $G$3:$G$4, etc.

You can test it by changing the Start and End times and delegate numbers.

Hope this helps?

Ochimus

[VancityPlanner]

Vancity,

Try the attached.

Days and Dates are in Cols F1 - L2.

F3 - L4 show whether people attended a session or not..

Hours are clock hours in row 12.

Formula in B13, copied across to I13, is:

=SUMIFS($M$3:$M$4,$F$3:$F$4,"Y",$D$3:$D$4,"<="&B$12,$E$3:$E$4,">="&B$12+TIME(0,59,0))

(For each "hour" bloc, add the total in Col M where Col F shows people attended a session on the date in Col A, and the Start Time is before or on the hour in Row 12, and the End Time is more than or equal to the hour plus fifty nine minutes)

For rows 14 - 19 you change the "date element" ($F$3:$F$4) for $G$3:$G$4, etc.

You can test it by changing the Start and End times and delegate numbers.

Hope this helps?

Ochimus

Unfortunately my dataset is several thousand records that spans a year. For that reason, I cannot assign a unique column (as you have modified) for each date. It's equally important to know attendance on dates that are within the records and outside the records, hence why I have a "2020-09-14" line in the summary section whereas the data is only from 2020-09-06 to 2020-09-12.

[Ochimus]

Vancity,

Thank you for the clarification. Attached formula does what I understand you asked for originally:

=IF(WEEKDAY($A12)=1,SUMPRODUCT(--($F$3:$F$4<=B$11),--($G$3:$G$4>=B$11),--($D$3:$D$4<=$A12),--($E$3:$E$4>=$A12),--($H$3:$H$4="Y"),--ISNUMBER(MATCH($A$3:$A$4,$B$7,0))*--ISNUMBER(MATCH($B$3:$B$4,$B$8,0))*--ISNUMBER(MATCH($C$3:$C$4,$B$9,0)),$O$3:$O$4),IF(WEEKDAY($A12)=2,SUMPRODUCT(--($F$3:$F$4<=B$11),--($G$3:$G$4>=B$11),--($D$3:$D$4<=$A12),--($E$3:$E$4>=$A12),--($I$3:$I$4="Y"),--ISNUMBER(MATCH($A$3:$A$4,$B$7,0))*--ISNUMBER(MATCH($B$3:$B$4,$B$8,0))*--ISNUMBER(MATCH($C$3:$C$4,$B$9,0)),$O$3:$O$4),IF(WEEKDAY($A12)=3,SUMPRODUCT(--($F$3:$F$4<=B$11),--($G$3:$G$4>=B$11),--($D$3:$D$4<=$A12),--($E$3:$E$4>=$A12),--($J$3:$J$4="Y"),--ISNUMBER(MATCH($A$3:$A$4,$B$7,0))*--ISNUMBER(MATCH($B$3:$B$4,$B$8,0))*--ISNUMBER(MATCH($C$3:$C$4,$B$9,0)),$O$3:$O$4),IF(WEEKDAY($A12)=4,SUMPRODUCT(--($F$3:$F$4<=B$11),--($G$3:$G$4>=B$11),--($D$3:$D$4<=$A12),--($E$3:$E$4>=$A12),--($K$3:$K$4="Y"),--ISNUMBER(MATCH($A$3:$A$4,$B$7,0))*--ISNUMBER(MATCH($B$3:$B$4,$B$8,0))*--ISNUMBER(MATCH($C$3:$C$4,$B$9,0)),$O$3:$O$4),IF(WEEKDAY($A12)=5,SUMPRODUCT(--($F$3:$F$4<=B$11),--($G$3:$G$4>=B$11),--($D$3:$D$4<=$A12),--($E$3:$E$4>=$A12),--($L$3:$L$4="Y"),--ISNUMBER(MATCH($A$3:$A$4,$B$7,0))*--ISNUMBER(MATCH($B$3:$B$4,$B$8,0))*--ISNUMBER(MATCH($C$3:$C$4,$B$9,0)),$O$3:$O$4,IF(WEEKDAY($A12)=6,SUMPRODUCT(--($F$3:$F$4<=B$11),--($G$3:$G$4>=B$11),--($D$3:$D$4<=$A12),--($E$3:$E$4>=$A12),--($M$3:$M$4="Y"),--ISNUMBER(MATCH($A$3:$A$4,$B$7,0))*--ISNUMBER(MATCH($B$3:$B$4,$B$8,0))*--ISNUMBER(MATCH($C$3:$C$4,$B$9,0)),$O$3:$O$4,IF(WEEKDAY($A12)=7,SUMPRODUCT(--($F$3:$F$4<=B$11),--($G$3:$G$4>=B$11),--($D$3:$D$4<=$A12),--($E$3:$E$4>=$A12),--($N$3:$N$4="Y"),--ISNUMBER(MATCH($A$3:$A$4,$B$7,0))*--ISNUMBER(MATCH($B$3:$B$4,$B$8,0))*--ISNUMBER(MATCH($C$3:$C$4,$B$9,0)),$O$3:$O$4,""))))))))))

Formula from B12 across and down works as follows:

Check whether (a) the date in A(whatever) is within the Date Span in F - G, (b) there is a "Y" in whichever column in H - N matches the weekday in A(whatever), (c) the time in whichever Hour column you are in is within the Start and Finish times in Cols D and E, and (d) the Criteria in A - C are met. If so, enter the number(s) from Col O.

Key point is that formula works on ANY date where the weekday is that day. If six rows are that day and within the Start and End dates, it posts the relevant volume(s) in each of them automatically.

So in every row where the weekday is a (whatever day), whatever the date, if it is within the time span and there is one Y in row 3, every row from 12 downwards with that weekday will show 100 delegates from the Start to the End times. If you have the Y in row 4 it will show 50 delegates. If both rows have a "Y" it will show 150.

Formula then repeats check for weekdays 2 - 7.

Ochimus

[VancityPlanner]

Vancity,

Thank you for the clarification. Attached formula does what I understand you asked for originally:

=IF(WEEKDAY($A12)=1,SUMPRODUCT(--($F$3:$F$4<=B$11),--($G$3:$G$4>=B$11),--($D$3:$D$4<=$A12),--($E$3:$E$4>=$A12),--($H$3:$H$4="Y"),--ISNUMBER(MATCH($A$3:$A$4,$B$7,0))*--ISNUMBER(MATCH($B$3:$B$4,$B$8,0))*--ISNUMBER(MATCH($C$3:$C$4,$B$9,0)),$O$3:$O$4),IF(WEEKDAY($A12)=2,SUMPRODUCT(--($F$3:$F$4<=B$11),--($G$3:$G$4>=B$11),--($D$3:$D$4<=$A12),--($E$3:$E$4>=$A12),--($I$3:$I$4="Y"),--ISNUMBER(MATCH($A$3:$A$4,$B$7,0))*--ISNUMBER(MATCH($B$3:$B$4,$B$8,0))*--ISNUMBER(MATCH($C$3:$C$4,$B$9,0)),$O$3:$O$4),IF(WEEKDAY($A12)=3,SUMPRODUCT(--($F$3:$F$4<=B$11),--($G$3:$G$4>=B$11),--($D$3:$D$4<=$A12),--($E$3:$E$4>=$A12),--($J$3:$J$4="Y"),--ISNUMBER(MATCH($A$3:$A$4,$B$7,0))*--ISNUMBER(MATCH($B$3:$B$4,$B$8,0))*--ISNUMBER(MATCH($C$3:$C$4,$B$9,0)),$O$3:$O$4),IF(WEEKDAY($A12)=4,SUMPRODUCT(--($F$3:$F$4<=B$11),--($G$3:$G$4>=B$11),--($D$3:$D$4<=$A12),--($E$3:$E$4>=$A12),--($K$3:$K$4="Y"),--ISNUMBER(MATCH($A$3:$A$4,$B$7,0))*--ISNUMBER(MATCH($B$3:$B$4,$B$8,0))*--ISNUMBER(MATCH($C$3:$C$4,$B$9,0)),$O$3:$O$4),IF(WEEKDAY($A12)=5,SUMPRODUCT(--($F$3:$F$4<=B$11),--($G$3:$G$4>=B$11),--($D$3:$D$4<=$A12),--($E$3:$E$4>=$A12),--($L$3:$L$4="Y"),--ISNUMBER(MATCH($A$3:$A$4,$B$7,0))*--ISNUMBER(MATCH($B$3:$B$4,$B$8,0))*--ISNUMBER(MATCH($C$3:$C$4,$B$9,0)),$O$3:$O$4,IF(WEEKDAY($A12)=6,SUMPRODUCT(--($F$3:$F$4<=B$11),--($G$3:$G$4>=B$11),--($D$3:$D$4<=$A12),--($E$3:$E$4>=$A12),--($M$3:$M$4="Y"),--ISNUMBER(MATCH($A$3:$A$4,$B$7,0))*--ISNUMBER(MATCH($B$3:$B$4,$B$8,0))*--ISNUMBER(MATCH($C$3:$C$4,$B$9,0)),$O$3:$O$4,IF(WEEKDAY($A12)=7,SUMPRODUCT(--($F$3:$F$4<=B$11),--($G$3:$G$4>=B$11),--($D$3:$D$4<=$A12),--($E$3:$E$4>=$A12),--($N$3:$N$4="Y"),--ISNUMBER(MATCH($A$3:$A$4,$B$7,0))*--ISNUMBER(MATCH($B$3:$B$4,$B$8,0))*--ISNUMBER(MATCH($C$3:$C$4,$B$9,0)),$O$3:$O$4,""))))))))))

Formula from B12 across and down works as follows:

Check whether (a) the date in A(whatever) is within the Date Span in F - G, (b) there is a "Y" in whichever column in H - N matches the weekday in A(whatever), (c) the time in whichever Hour column you are in is within the Start and Finish times in Cols D and E, and (d) the Criteria in A - C are met. If so, enter the number(s) from Col O.

Key point is that formula works on ANY date where the weekday is that day. If six rows are that day and within the Start and End dates, it posts the relevant volume(s) in each of them automatically.

So in every row where the weekday is a (whatever day), whatever the date, if it is within the time span and there is one Y in row 3, every row from 12 downwards with that weekday will show 100 delegates from the Start to the End times. If you have the Y in row 4 it will show 50 delegates. If both rows have a "Y" it will show 150.

Formula then repeats check for weekdays 2 - 7.

Ochimus

Excellent! This solution works.

[Ochimus]

Vancity,

Glad it gave you the solution. Formula ought to be a contender for the Guinness Book of Records!

As a matter of interest, why is the form limited to only two rows of events?

Would it not be better to move both the Criteria markers and the output bloc from rows 8 downward either to columns Q onwards, or put them on a separate sheet?

That way you can have a "rolling" database of as many events as you want (and I would also set the columns as Named Ranges so they expanded automatically as you added new ones).

Ochimus