Forum Discussion

SophieGon's avatar
SophieGon
Copper Contributor
Nov 03, 2022

Automatize sum of different ranges linked to weekday

Hi guys! 

I was wondering if you have any suggestions on how to approach this: 

 

I receive the hourly consumption of gas in a month, so I have a matrix with the dates in a column and the hours in the top row so, gas per day per hour

 

I want to obtain the total amount of monthly gas but divided in three categories

1: Mon-Fri from 0:00-6:00am, Sat from 0:00-8:00, Sun from 0:00-18:00

2:Mon-Fri from 6:00-18:00am, Sat from 8:00-19:00, Sun from 18:00-24:00

3:Mon-Fri from 18:00-22:00am, Sat from 19:00-21:00

 

The thing is that every month, weekdays will change so how can I make it automatically either with excel formulas or VBA code? 

 

Thanks for your help!!

 

 

 

excel

1 Reply

Sort By
  • T_Nielsen_'s avatar
    T_Nielsen_
    Copper Contributor
    Nov 16, 2022
    Sounds like Filterfunction using the weekday function could help you filter through the Days and then do something similar similar with the timeslots but i expect timeslots dont change so that filter would probably be easier

    Here is a command that filters out monday - friday
    =LET(Range_;A2:A22;FILTER(Range_;((WEEKDAY(Range_)=2)*1+(WEEKDAY(Range_)=3)*1+(WEEKDAY(Range_)=4)*1+(WEEKDAY(Range_)=5)*1+(WEEKDAY(Range_)=6)*1)))

Resources