SUMIF cells are not formulas

2
votes

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

2
excelexcel-formula
You need the criteria of the sumif to evaluate to true or false, your isformula statement doesn't evaluate cleanly like that. Could try something like =Sumif(B5,not(isformula(B5))+Sumif(C5,not(isformula(C5))+....+Sumif(M5,not(isformula(M5)) - that should fill down easily so you only need to type it once. Don't have Excel on hand to check or come up with a more elegant solution but if it's B through M shouldn't be too bad. – mock_blatt
Alright sure but that's going to get very ugly for 10+ columns (which I will have). Is there a more dynamic/compact solution? – Will Maclean
Can you share some sample data? – Imtiaz Ahmed
See link in the original post for some sample data – Will Maclean
@WillMaclean As per your question description you want to sum the cells which has formulas, but as per the title of the question you want to sum the cells which doesn't have any formulas. Kindly correct it so that in the future if any other person who faces the same problem can search and get the proper answer. – Imtiaz Ahmed

2 Answers

2
votes

I think this is the solution you are looking for -

sum the cells that has formula

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

sum the cells that doesn't have formula

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

0
votes

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.