SUMIF Function for a period between two dates

Cambosity100 

Re: ``Very simple formula checked against a calculate and the answer is significantly incorrect``

You neglect to provide even a single example of the Excel result vs the calculator result.

But we might guess that you are making a very common mistake:  when you enter values into the calculator, you enter them as they are displayed in Excel.

But the displayed values might be different from the actual values; and Excel uses the actual values in calculations.

For example, the formula in G7 is =B7*C7*H7*F3. B7 is 1, C7 is 77.81, H7 is 4, and F3 appears to be 39. And the result in G7 appears to be $12,093.90.

But if we enter 1*77.81*4*39 into a calculator, the result is $12,138.36.  That is indeed very different.

The problem is:  F3 only appears to be 39 because of the cell format.  Its actual value is 38.8571428571429.  And that is the value that Excel uses in the calculation.

To demonstrate, if we enter 1*77.81*4*38.85714 into the calculator (and display only 2 decimal places), the result appears to be $12,093.90 -- the same as G7.

And BTW, G7 only appears to be $12,093.90 because of the cell format. Its actual value is 12093.8971428571.

Only you can decide if and where to explicitly round Excel calculations.  For example, you might write:

F3: =ROUND((E3-D3)/7, 0)

G7: =ROUND(B7*C7*H7*F3, 2)

(Arguably, rounding G7 is unnecessary if we round F3, at least for the example Excel file.  But it is prudent to explicitly round calculations that involve decimal fractions for other reasons.)

Alternatively, you might choose to leave F3 unrounded for other purposes, but round its value when calculating G7.  So, you might write:

F3: =(E3-D3)/7

G7: =ROUND(B7*C7*H7*ROUND(F3, 0), 2)

But in the final analysis, you might choose not to round at all, accepting the fact that the Excel calculation is more "accurate" (for some purposes) and different from the WYSIWYG calculations that you might enter on a calculator.

Aside.... There is no need to use DATEDIF(...,"d"); E3-D3 is simpler.  And there is no need for the outer parentheses around the entire formula expression after the equal sign ("=").

-----

Caveat:  Someone might suggest setting the option "Precision as displayed" to avoid the explicit rounding.  I do not recommend that, for many reasons.  But if you choose to experiment with PAD, be sure to make a copy of the Excel file first.  Merely setting PAD might irreversibly change constants that you purposely display with less precision.  Setting PAD affects all worksheets change in the entire Excel file.  And PAD affects only the final value in a cell that is formatted as intended; for example, =IF(F3=39, TRUE) would still return FALSE(!).