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

Dianak123

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?

Genevieve P.

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