0

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!

rohannair
  • 31
  • 7
  • 1
    Why does 0.5 count as 1 but 3 counts as 3? What would a 2 count as? Or a 4? – Gravitate Apr 11 '23 at 15:16
  • 2
    `=SUMIFS(B:B,A:A,"SNLS-UBT",B:B,">=1")+COUNTIFS(A:A,"SNLS-UBT",B:B,"<1")` – Scott Craner Apr 11 '23 at 15:21
  • I agree with the logic sighted by @Gravitate, however I assume might be trying to round those deimals if so then could you try this once because this works as per your criteria ==> `=SUM(ROUND($B$1:$B$8,0)*(D1=$A$1:$A$8))` – Mayukh Bhattacharya Apr 11 '23 at 15:21
  • @Gravitate it is meant to be like people allocated to that code, therefore 0.5 should be rounded off to 1. – rohannair Apr 11 '23 at 15:44
  • @MayukhBhattacharya why is there D1 in the formula? Im trying to add that in to my excel worksheet so i would need to know which cell to choose instead of D1 – rohannair Apr 11 '23 at 15:45
  • @rohannair actually D1 refers to the cells where i have entered unique values of column A. Do you want me to post it as an answer, will that be okay for you – Mayukh Bhattacharya Apr 11 '23 at 15:46
  • @MayukhBhattacharya `=SUM(ROUND($R$13:$R$61,0)*(T14=$H$13:$H$61))` This comes off as an error. Its basically the same thing you sent but I had to put in the accurate cell numbers in my worksheet. Shows Error in Value – rohannair Apr 11 '23 at 15:52
  • @rohannair refer in answers I have posted there. You need to suit the ranges with your data accordingly.. – Mayukh Bhattacharya Apr 11 '23 at 15:54

1 Answers1

1

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.

Mayukh Bhattacharya
  • 12,541
  • 5
  • 21
  • 32