In every report, I need to sum/calculate values and also the time, I mean the hours between for instance today and yesterday. For some, they are quite easy and for others, more difficult but there is always a way to get the final result.

When I use the formula ?

Every time to get the total result of something.

How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

How are the formulas ?

=SUM()

=SUMPRODUCT()

The basic way to sum all values are using this formula:

=SUM(C2:C4)

Of course, you can put “=C2+C3+C4” and you will get the same result.

This one will calculate only if it finds “chocolate” and “dark”, so it will tell you how much:

=SUMPRODUCT((A2:A4="chocolate")*(B2:B4="dark"))

To calculate the time between today and other day:

=NOW()-B2

And if you want to calculate the time between “end” and “start”, the formula is simpler:

=B2-A2

But as you can see in the cell D3, the time showing is more than 24h. If you need to display like the example, you have to format the cell by using the “custom” option by putting:

[hh]:mm

And if you want the seconds, just add “:ss”.

For more option, read Sum and count sales with a formula in an excel report.