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

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
Categories
Check out the Formula Handbook template!