Excel - SUM Daily results based on given date range

Question

thank you for taking a minute to help me!

I have 3 sheets, 3 of them with daily data of users, vehicles, and population which is steady, all of them have the same structure:

D column is location and the rest are daily results. Results change every 14 days, that's why for each location you are seeing the same data.

And there's a 4th, where I want to concentrate results from the previous sheets based on data and location - location is D column:

I want to set both start and end date and based on that, sum daily results by location. For example, suppose that Inicio - Start date is 01/03/2020 and Termino - End Date is 05/03/2020, in the second image, where I highlight SUM HERE it should appear 227,340 for 001-L1 Dr. Galvez N.

I already tried SUMIF, SUMIFS, SUMPRODUCT but it doesn´t seem to work.

Any ideas?

Answer 1

I have developed this formula for you (001). Please take a look.

[E5] =SUMIFS(INDEX(Counts1,MATCH($D5,Stops1,0),0),Dates1,">=" & $E$2,Dates1,"<=" & $F$2)

The formula is designed for cell E5 of your fourth sheet. $D5, $E$2 and $F$2 are all on this sheet.

Counts1 is a named range on Sheet1, comprising all the passenger counts, starting from column E, up to the last day of the month and, vertically, from row 4 to as many rows as there are stations.

Stops1 is another named range on Sheet1 D4:[end of column].

Dates1 is a third named range on Sheet1, starting from E2:[end of row]. I would suggest you set up these named ranges to adjust dynamically to the actually used areas of the sheet.

Now, INDEX(Counts,MATCH($D5,Stops,0),0) defines all cells in the row of Counts where the Stop is equal to D5. Of course, this reference changes as you copy the formula down.

SUMIFS takes that range, extracts and sums up the numbers you are interested in.

Now, if you need to extract the same numbers by the same system from 3 different sheets you simply repeat the formula 3 times, concatenating the results with plus signs. That leaves you with the task to set up 9 ranges instead of only 3, each group of 3 on one of your source sheets. By the system I have implied above you would name them Count2, Count3 etc.

That leaves the question of your dates. They must be true dates. Text strings that look like dates won't do. (I guess you know that :-). In the test sheet I set up I entered =DATE(2020,3,1) in E2, [F2]=E2+1 and copied from F2 to the right.

Answer 2

What didn't work with SUMPRODUCT? Try this - the result is correct:

=SUMPRODUCT((Sheet1!$E$3:$M$3>=$E$2)*(Sheet1!$E$3:$M$3<=$F$2)*(Sheet1!$D$2:$D$14=D5)*Sheet1!$E$4:$M$14)

I tried to adjust the ranges to your example, but change them if necessary.