1

I have a spreadsheet that has columns for dates and the values can be either "1v, .5v, 1p, .5p, 1s, .5s" I have 3 columns in each row one for each letter "v, p and s". I want to be able to add the total of all cells in the range grouped by letter and then display the sum for each letter in it's respective column (v, p or c).

Here is an example of the sheet:

Name     Vacation   Personal   Sick   1/5/15  1/6/15  1/7/15  1/8/15
Billy       1.5        1         0       .5v    1v              1p

It is the formula that goes in the vacation/personal/sick cell that I just can't figure out.

dotnetgeek
  • 275
  • 1
  • 4
  • 14

3 Answers3

2

Assuming the range you posted starts at A1, use

=SUMPRODUCT((RIGHT($E2:$G2,1)="v")*LEFT($E2:$G2,LEN($E2:$G2)-1))

in B2. Change "v" and the range to use suitably.

Pro:

Con:

  • I could not make it work with blank cells.
Community
  • 1
  • 1
sancho.s ReinstateMonicaCellio
  • 14,708
  • 20
  • 93
  • 185
  • I was having an issue and getting a #VALUE!. After some lever pulling I realized that it is because some of the values may be blank. Once I put in values for all the range then it worked. I've updated the range to add in a H2 and removed the value in H2 to better represent the issues. Is there a way to tell the formula to ignore no/null celled values – dotnetgeek Jan 05 '15 at 17:26
2

I went down the array formula route and came up with essentially the same formula as @Sancho.s :-

=SUM(LEFT($E2:$H2,LEN($E2:$H2)-1)*(RIGHT($E2:$H2)="v"))

You could modify it to take account of blanks:-

=SUM(IF($E2:$H2<>"",LEFT($E2:$H2,LEN($E2:$H2)-1)*(RIGHT($E2:$H2)="v")))

Perhaps this would be better, to ignore any mis-formatted cells:-

=SUM(IFERROR(LEFT($E2:$H2,LEN($E2:$H2)-1)*(RIGHT($E2:$H2)="v"),0))

These all have to be put in with Ctrl-Shift-Enter.

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
  • I was able to get your 2nd formula working. I could not get the if statement to work with @sancho.s' SUMProduct. Thanks! – dotnetgeek Jan 05 '15 at 18:01
1

This "array entered" version will also allow blanks

=SUM(IF(RIGHT(E2:G2)="v",SUBSTITUTE(E2:G2,"v","")+0))

confirmed with CTRL+SHIFT+ENTER

barry houdini
  • 45,615
  • 8
  • 63
  • 81