0

I have many large excel files that I need an easy way to combine daily totals for each person (a pivot table is not going to work for me on this one). My example is one of the smaller files, and it has 22,176 rows of data. The hours are listed in column H, and a person could have anywhere from 1 to 5 (or more) rows of hours for a day. I have already done conditional formatting to visually differentiate between the days (that's where the number in column I comes from). So now I need a total for each date, preferably on the last line of each date.

So in my example:

for 12/20/20, I need the sum of H2:H4 in J4

for 12/23/20, I need the sum of H5:H6 in J6

for 12/24/20, I need the sum of H7:H8 in J8

for 12/25/20, since there is only one entry for that day, I need the number in H9 to be in J9, etc

It doesn't matter to me if the formula is based on the color, the date, or the number in column I. I also have Kutools installed if that is easier. I have used Excel for most of my life and can usually figure these things out. As I typed this it seems so simple, but I just can't get there on this one. I have been looking for a solution and have not been able to find one.

Please let me know if additional information is needed. Thank you in advance for any assistance!! Example

Improve this question
2
  • Can you live with having cumulative values of the hours for each day, up to the last entry which has the total? Your pivot could then use the Max() function on the value
    – Daniel
    Commented Jan 27, 2022 at 8:08
  • Hi Daniel, Not really...I am preparing the files for other people to enter and then they have to be submitted to another agency so I need it as clear as possible. I would also need all of the information for each line to be intact.
    – Sheila
    Commented Jan 27, 2022 at 14:26

1 Answer 1

Reset to default
1 
J2: =IF(C2=C3,"", SUMIFS($H$2:$H$9,$A$2:$A$9,A2,$C$2:$C$9,C2))

and fill down.

You could also do this Power Query

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .