The spreadsheet consists of columns that include:
|start date|end date|power value|new column|
There are over 100,000 rows covering a two year period with ~96 row power values per starting date. My challenge is to sum up each day's total power values and insert that days total into a "new column" in the last row of each date series. I used to think I was pretty good at excel and VB but that was years ago and this relatively simple problem solution has eluded me for several days now.
CodePudding user response：
Assuming you want to find per day, the sum of power value, you'll need two columns :
- One for the date
- One for the sum
As you already have 3 columns (assuming Col A = start date, B = end date and C = power value) I'd suggest a blank column (D) and col E being date, col F being power value sum.
Then put the following formulas:
E2 : =MIN(A:A) --> this will find the first start date
E3 : =IF(E2 1>MAX(B:B),"",E2 1) --> will increment the day by 1 until it reach the maximum end day. From that point it will put a blank value
Deploy the formula until you got some empty cells
F2 : =if(E2="","",SUMIFS(C:C,A:A,"<="&E2,B:B,">="&E2)) --> will sum the power values as long as the range start date - end date includes the date in column E, and force the value at blank when there are no more dates
Deploy the formula