Google-sheets – Copying data with two criteria in sheets

google sheets

I am trying to copy data from one worksheet to another if the data meets two criteria. First it has to match the date, then it has to fall within a four hour time frame. Once both are met the data will copy to a certain cell in one worksheet from the master worksheet. I have tried the IF function but I do not know how sheets interprets time. Also i have researched how to do this but everything i find about multiple criteria does not include time or date. Any help would be greatly appreciated. The following link is to some dummy data so you can see kind of what im looking for.

https://docs.google.com/spreadsheets/d/1U3336UidAEtsQF45tHF3391net8AOpXPcJhgVveCJ-A/edit?usp=sharing

Best Answer

I have inserted two worksheets on your sample, one named Master and the other Results.

In Master, I have simply copied the original data.

In Results, I copied the results table and added a helper row and a helper column. The helper row contains the hours in number format (0400 to mean 00:00-04:00, 0800 to mean 04:00-08:00, etc) and the helper column contains the value from column D for the same row. I will mention here that it would be simpler to make the two worksheets have matching row data.

Then in cell C4 of Results, I used the formula:

=if(sum($B4:B4)=$B4*2,"",if(Master!$B4<C$3,$B4,""))

If I have to break it into smaller parts:

  • if(Master!$B4<C$3,$B4,"") is responsible for ensuring this cell is the appropriate cell (with respect to the time) in which the value has to be inserted, and

  • if(sum($B4:B4)=$B4*2 verifies if we already inserted a value in a previous cell on the same row. I highlighted some cells in cream; those would also have values if it wasn't for this part.

Related Topic