excelwsp

Is there an excel formula to use in a count if function for counting more than one if indicated

enter image description here

Is there a formula to use the count if function for each code seen on the left side of the table but should count more than one if its indicated on the right. If it is 0.5 it should count as 1.

For example, SNLS-UBT if used the count if function returns the value 5 but it should return as 7 as one of them has 3 in it.

Let me know if you want more information. Thanks a ton!

Solution

  • Solution was assumption based, however it sounded like OP's comments from above that they were trying to rounded off 0.5 to 1.

    enter image description here

    Solution:

    enter image description here

    • Formula used in cell D1 --> Applicable to MS365

    =LET(x,UNIQUE(A1:A8),
HSTACK(x,BYROW(x,LAMBDA(r,SUM((r=A1:A8)*(ROUND(B1:B8,0)))))))

    • Formula used in cell G1 & H1

    =UNIQUE(A1:A8)

    =SUMPRODUCT((G1=$A$1:$A$8)*ROUND($B$1:$B$8,0))

    Note: You need to suite the ranges accordingly as per your data.