Formula to Filter 3 columns & enter data from 3rd

Having Excel's =MINIFS() would make this a lot easier!

But are you looking for results like this?

If so, here is how I did it.

First, I added a column to the data table showing the time period for each entry.

That could be done manually, or automated with a lookup table something like this (I had to kludge a little for midnight):

Then I added two columns to get the first row number for that date and the last row number for that date. That is used later in a special form of the OFFSET() function.

First row for that date:

Last row for that date:

Then I added more columns with labels like this:

The formula in G2, filled right to J2, then down, is:

=IF($D2=G$1,$C2,"")

The formula in K2, filled right to N2, then down, is:

=MIN(OFFSET(G$1,$E2−1,0,$F2−$E2+1,1))

The formula in O2, filled down is:

=COUNTIF(A$1:A2,A2)

Then I hid columns E:J and filtered on column O:

SG

Here is a way that may do what you want:

Create one table to set the time periods (since they are not regular):

the first row should be formatted as a header row.

enter the value in column A and C as shown.

select cell B2 and type (or copy and paste from here) the formula:

=TIMEVALUE(A2)

shorthand for this is:

B2=TIMEVALUE(A2)

to fill down , select cell B2, copy

now select cells C2 thru the end of column C, paste

Now add some columns to the table "Data":

D2=A2&"-"&VLOOKUP(TIMEVALUE(B2), Time Periods::B:C, 2,TRUE)

E2=VLOOKUP(TIMEVALUE(B2), Time Periods::B:C, 2,TRUE)

F2=IF(E2<E$1, 0, F1+1)

G2=IF(OR(F3<F2, AND(F2=0, F3=0)),MIN(OFFSET(C2, −F2, 0, 1+F2, 1)), "")

fill these down by

select cells D2 thru G2, copy

select cells D2 thru the end of column G, paste

Now complete the summary table:

set up the first two rows as show (columns B thru E)

B3=SUMIFS(Data::$G, Data::$A, $A3, Data::$E, B1)

select cell B3, copy

select cells B3 thru the end of column E, paste

Alan,

I looking at your document, the main problem is that the dates entered in the table "Data" were formatted as:

mm/dd/yy

where as the dates in the table "Summary" were enter, for some reason, as:

dd/mm/yy

In addition I seem to have a problem relaying my correct formulas.

The formula for F2 should be:

=IF(E2≠E1, 0, F1+1)

I know how it go messed up too!

notice that the formula has the not equal operator as an equal sign with a slash. I tried to correct this by pasting directly from Numbers to this forum, then I tried, unsuccessfully, to change the

≠ to <>

Final formula is:

=IF(E2<>E1, 0, F1+1)

Also... in the table "Summary", the formula for B3 should be:

=SUMIFS(Data::$G, Data::$A, $A3, Data::$E, B$1)

copy and paste this formula to the range:

B3 thru the end of column E

the format of the dates in column A of table Data and column A of the summary table MUST be the same. I corrected this. I do not know why they were different formats.

I will email the corrected document back to you.

Alan, in order to obtain this result:

you can start from here: your Data table expanded with columns D —> M and proposing 2 header rows for the time periods: starting time and end time for each period.

This screenshot shows the formulas used, converted to text:

(the rows 4 —> 25 and the columns K, L, and M are not shown in the latest screenshot above).

using the following formulas:

calculating the 1st row for a given date in column D: MATCH($A3;A;−1)

calculating the last row for a given date in column E: MATCH($A3;A;1)

getting the data in the right time period in columns F, G, H and I: IF(AND($B3≥F$1;$B3≤F$2);$C3;"")

calculating the smallest value in each time period in columns J, K, L and M for each date: MIN(OFFSET(F$2;$D3−2;0;$E3−$D3+1;1))

Now you can get the data calculated in the columns J, K, L and M into your Summary table:

using the following formula:

INDEX(Data::J;MATCH($A3;Data::$A;0))

filling down for each given date and filling right for each time period.

The final result will be this Summary table:

Custom format "do not display zeroes" applied to the cells B3:E10 (Summary table)

Hope this helps.

Paul.

P.S. do not forget to hide columns D —> M (Data table) when everything works fine.

alanavey wrote:

Not sure what I am doing wrong but when I fill down the column all answers are 0.

Have you had time to try the solution I suggested above? I tested it before posting. No zeros. Friendly-looking output (not numbers representing time periods but actual time period labels).

SG

It's not a great idea to put your email in a post here (I think the hosts have edited out).

Also, since this is a public forum it would be great if you could share your file from Dropbox or iCloud so we can all see it.

Hope you try out my solution above.🙂

SG

maybe try using the average of the entries?

=sumifs(Data::C, Data::A, A3, Data::B, ">10:30", Data::B, "<=15:30")/countifs(A3, Data::B, ">10:30", Data::B, "<=15:30")

OK. To make working the example easier, please, copy the data table and paste here.

screenshots and formulas always help.

Post the formula in the cell now, and a screenshot of the the cell with the formula selected

I am sorry. I had a typo:

the formula:

F2=IF(E2<E$1, 0, F1+1)

should have been:

F2=IF(E2<E1, 0, F1+1)