sum difference if

[Viorel]

Hello,
Here i am again, and of course i need your help again......
I have a number of 30 cells in a row that can contain numbers, text or blank. If one or many of these cells contain a number bigger than 8, i need to sum the difference between those number and 8. In the example i attached, i need the result 11...(B1-8)+(C1-8)+(O1-8)+(AB1-8).
If none of the cells contain number bigger than 8 the result of the formula must be 0.
I need that for a time sheet where the blank cells are sundays and saturdays, CM are sick leave and CO are vacation....

 

[Somendra Misra]

Hi,

Try below array formula:

=SUM((A1:AD1>8)*IF(ISNUMBER(A1:AD1),A1:AD1-8,0))

Enter with Ctrl+Shift+ENter.

Regards,

 

[Viorel]

Hi Somendra, you save me again.... i don't now how can i thank you enough.... You are a true excel ninja....

 

[Somendra Misra]

@Viorel

Thanks for your kind words, I am still learning. Welcome back anytime.

Regards,