Forum Discussion

Priscilla Kerkhof's avatar
Priscilla Kerkhof
Copper Contributor
Jan 04, 2018

Adding up a specific value above a certain value

Hi all,

I'm looking for a special formula that so far I've been unable to find. This is a general outline of what I want the formula to do (I hope it's clear enough!):

Add up the numbers between cells A1:a5. When this is over a certain value (38 hours in my case), I want the formula to tell me how many hours above the 38 it is. Eg if the total comes to 40 hours I want the cell to display 2. If it's 42 hours I want the cell to display 4. Bonus would be if it could also display how many hours under 38 it is. Eg when the total is 35 I want the cell to display -3.

Is there a way to get a formula to do this? Any help is appreciated, many thanks!
Excel Desktop
Formulas & Functions

5 Replies

  • Priscilla Kerkhof's avatar
    Priscilla Kerkhof
    Copper Contributor
    Jan 04, 2018
    Oh, and I forgot to add this in my OP. The formula should exclude all letters. So it should only count numbers >0.
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jan 04, 2018

      Hi Priscilla,

       

      Would you like to say in your cells not only numbers, letters (text) as well? And how it looks like, combined with numbers or one cell number, another text?

       

      If you give typical sample with these 5 cells it'll be bit easier to understand.

       

      • Priscilla Kerkhof's avatar
        Priscilla Kerkhof
        Copper Contributor
        Jan 04, 2018
        Hi Sergei, thanks for your response.

        An example would be something like this:
        A1 8
        a2 8
        A3 6
        A4 9
        A5 f

        It's never a combination of letters and numbers in the same cells. It's always either a number or a letter. I use the spreadsheet to keep track of my working hours, my days off and also the bank holidays (hence the combination of numbers for the hours and the letters to keep track of different days).

        I hope this makes it a bit clearer!

Resources