How to use =COUNTIF() on every second cell that contains a letter?

user_9423028 0
Viewed 1560

  1. I want to keep track of employees working hours for the month which will include leave hours and overtime.

  2. I'm using two cells for each day of the month. Cell 1 = how many hours worked/employee was on leave. Cell 2 = how many hours overtime

  3. The working hours cell for the day will either contain a number(for how many hours they worked that day) or a letter(a "V", which will indicate they were on leave for the day).

  4. Each "V" will count as 8 hours of leave.

  5. I need to count every second cell in the range, starting at the 1st cell moving to the 3rd cell and so on. The 1st cell will be used for hours worked/leave and the 2nd cell will be used for overtime hours. So I need a formula to skip over the overtime cells.

  6. I want to use a =COUNTIF() formula on the range to count how many times the letter appeared. The letter will then add 8 hours to the total leave cell.

At the moment I'm just selecting each cell and adding an IF formula to it. But I have to do it 35 times. I pasted the formula I currently had to type to achieve the results I need. But I have to repeat the formula for other cells in my sheet(which will use other letters) and if I want to change the letter "V" to anything else I'll have to go and change each "V". 

=IF(B5="V",8)+IF(D5="V",8)+IF(F5="V",8)+IF(H5="V",8)+IF(J5="V",8)+IF(L5="V",8)+IF(N5="V",8)+IF(P5="V",8)+IF(R5="V",8)+IF(T5="V",8)+IF(V5="V",8)+IF(X5="V",8)+IF(Z5="V",8)+IF(AB5="V",8)+IF(AD5="V",8)+IF(AF5="V",8)+IF(AH5="V",8)+IF(AJ5="V",8)+IF(AL5="V",8)+IF(AN5="V",8)+IF(AP5="V",8)+IF(AR5="V",8)+IF(AT5="V",8)+IF(AV5="V",8)+IF(AX5="V",8)+IF(AZ5="V",8)+IF(BB5="V",8)+IF(BD5="V",8)+IF(BF5="V",8)+IF(BH5="V",8)+IF(BJ5="V",8)+IF(BL5="V",8)+IF(BN5="V",8)+IF(BP5="V",8)+IF(BR5="V",8)
excel-formula excel
3 Answers

Not sure if I understood your question correctly, but here's an example of a range B5:N5 with four Vs. The formula is checking every second column (MOD(COLUMN(B5:N5),2)=0) and returns 8 if the value equals V. Note that this is an array formula so it needs to be entered using Ctrl+Shift+Enter.

As there are four Vs, the result is 4*8=32. You can easily adjust/extend the range B5:N5 as required.

=SUMPRODUCT(IF(MOD(COLUMN(B5:N5),2)=0,IF(B5:N5="V",8),0))

enter image description here

user_106719260

Use SUMPRODUCT and Check if the COLUMN is even:

=SUMPRODUCT(ISEVEN(COLUMN(B5:N5))*(B5:N5="V"))*8

This is entered normally.

user_48515900

It look like your range B5:BR5 will contain either a number or a letter "V"

Then,

for count the total number of "V" then multiply by 8,

the formula is :

=COUNTIF(B5:BR5,"V")*8
user_121251810
Related