Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 0 down vote favorite

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?!!!

share|improve this question

2 Answers

up vote 2 down vote accepted

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.

share|improve this answer
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 at 7:03
Thanks!!! That works really well – user193896 Jan 31 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 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 at 23:04
up vote 0 down vote

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.