SUMIF cells are not formulas

Question

I'm trying to create an excel spreadsheet that contains a row for each employee of a company, where each column references the hours of work they do each week. Initially, the cells are filled with expected hours/week filled from another sheet, but then they are replaced manually with the amount of hours people actually work every week. I want to be able to sum the amount of hours people have actually worked i.e. sum the cells of each row which are formulas.

In the past I've just been manually summing the cells each week.

The formula I think should work is:

=SUMIF(B5:M5, "!ISFORMULA(B5:M5)")

Where columns B:M contain the hours worked each week.

At the moment, this formula returns 0, no matter how many cells with formulas there are in the row.

---

EDIT See link for sample data: Sample Data

Answer 1

To sum the cells that has formula use:

=SUMPRODUCT((ISFORMULA(B5:M5)=TRUE)*B5:M5)

And to sum the cells that doesn't have formula use:

=SUMPRODUCT((ISFORMULA(B5:M5)=FALSE)*B5:M5)

I think this is the solution you are looking for.

Answer 2

looks like a job for sumproduct

=SUMPRODUCT((B5:M5)*(NOT(ISFORMULA(B5:M5))))

This gives 169 as result which is the same as if i only sum the uncolored cells.