I want to calculate the number of "x" and "h"

I want to calculate the number of "x" and "h" in a range of cells:

  • the calculation is doing for the right person
  • it is in a range of cells
  • count "x" as 1 and "h" as 0,5
  • if it is "x" and "h" in the same range sum both

I used this: =COUNTIFS([Column2]4:[Column6]4, >27, [Column2]5:[Column6]11, <>"x", [Column2]5:[Column6]11, <>"h"

and also: =SUMIFS([Column2]4:[Column286]4, ">="&[Column2]15,[Column2]4:[Column286]4,"<"&[Column3]15,[Column2]5:[Column286]5,"X")

and COUNTIF([Column2]4:[Column286]4, ">="&[Column2]15,[Column2]4:[Column286]4,"<"&[Column3]15,[Column2]5:[Column286]5,"X")+(0.5*COUNTIFS([Column2]4:[Column286]4, ">="&[Column2]15,[Column2]4:[Column286]4,"<"&[Column3]15,[Column2]5:[Column286]5,"h")

but is nor working any.. what am I doing wrong?

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 10/13/23

    Hey @Dianak123

    If I'm understanding you correctly, on the same row in the current sheet, you're looking to assign a value of "1" to every time a letter "X" appears for a person, and a value of "0.5" for every time the letter "H" appears for a person.

    If so, you'll want to use two separate COUNTIF statements, one for each letter, then multiply the K statement by 0.5

    Try something like this:

    =COUNTIFS([Column2]4:[Column6]4, "x") + (COUNTIFS([Column2]4:[Column6]4, "H") * 0.5)

    This will give you a weighted count of Xs and Hs for Row 4 only. Is that what you wanted?

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!