r/excel•
MartelFirst

Formula to simply add the numbers situated two cells underneath each recurring named (or colored) cells

unsolved

Hi there. I can't find the correct formula to add the "HOURS" of each employee. As you can see in the mock example I made, I'd like a formula to add all of MAT's hours, which would entail adding all the numbers situated two cells underneath all the "MAT" cells. The reason I'd need a formula is that the actual Excel page I'm using is much larger (includes months of work days - in this example I just put 4 days to demonstrate), and I switch employees from post to post in order to give them good time tables and reach the ideal pre-set number of TOTAL HOURS. I waste a lot of time having to manually add every cell to see if it fits the required number of hours.

Thanks a bunch for your help.

Mock example of my problem

Posted Aug 8, 2019, 5:28 PM UTC

82
SmashLanding9•
=HLOOKUP("MAT",D5:F7,3,FALSE)+HLOOKUP("MAT",H5:J7,3,FALSE)+HLOOKUP("MAT",D12:F14,3,FALSE)+HLOOKUP("MAT",H12:J14,3,FALSE)

Then just do the same for each name.

For the record, this would be a much shorter and easier formula if you were to use a table Format.

1
MartelFirstOP•

Thanks a bunch for your uber-fast answer! Unfortunately, I'm an Excel newbie and I don't actually have Excel at home. I saved a screenshot from work to post from home. I hoped that with my free Excel-equivalent (LibreOffice) I could try out any answer, but it doesn't seem to work. It says "Err :501" for invalid character.

But as soon as I get back to work tomorrow morning (I'm in France) I'll test your formula on actual Excel.

Thanks again for your time.

2