# Excel. Double the number in cell A7, IF all cells from A1 through A6 contain a value higher than 0

Excel. Double the number in cell A7, IF all cells from A1 through A6 contain a value higher than 0.

I am trying to make an excel timesheet where Employees who work on Sunday get paid at double their hourly rate. HOWEVER, in order for them to get this double rate they MUST have worked all of the previous 6 days. (i.e Monday to Saturday) Otherwise they just get the usual hourly rate.

Got myself rather confused!! Any pointers please?!!!

-

The following formula will do the trick:

``````=IF(COUNTIF(A1:A6,">0")=6,2*A7,A7)
``````

The COUNTIF function calculates how many of the cells in A1:A6 are greater than zero. If the count is six, then the formula doubles A7; if not, then just A7 is returned.

-
No need for countif. Count() can be used like =IF(COUNT(A1:A6)=6,2*A7,A7) -- Count only counts numbers, not text. – teylyn Jan 30 '13 at 7:03
Thanks!!! That works really well – user193896 Jan 31 '13 at 0:41
@teylyn: “`COUNT` only counts numbers, not text.” True. // “No need for `COUNTIF` –– `COUNT()` can be used, as in `=IF(COUNT(A1:A6)=6, …)`. False. Zero is a number. If `A1:A6` contains { 8, 8, 8, 8, 8, 0 }, `COUNT(A1:A6)` is 6, and timesheets often contain zeroes. – Scott Feb 1 '13 at 22:19
Just for completeness(?), `=IF(PRODUCT(A1:A6)>0, …)` would work if we were guaranteed that all six cells have numbers in them –– but `PRODUCT(A1:A6)` gives the multiplication product (A1×A2×A3×A4×A5×A6) of the numbers in the range `A1:A6`, ignoring blanks and even text –– so, for example, if `A1:A6` contains { 8, 8, 8, 8, 8, "off" }, `PRODUCT(A1:A6)` is 32768 (8×8×8×8×8). So I guess `=IF(AND(COUNT(A1:A6)=6, PRODUCT(A1:A6)>0), …)` would work. But chuff’s `COUNTIF` answer is clearly superior. – Scott Feb 1 '13 at 23:04

It seems like you don't have a blank cell when no hours have been worked. If this was the case, you could simply use the IsBlank() function to check those cells.

Another way is to use a hidden column that uses an if formula to interrogate the hours worked for that day and inserts 1 if hours have been worked, and 0 if they haven't.

Then, in your formula that checks to see if the rate is double time, check that the sum of the hidden cells equals 6, which indicates that all days have been worked.

Edit: d'oh. forgot countif(). This works, but go with the other answer

-