Reputation: 3

SUM IF when a text string meets a specific numeric value

Okay it might sound weird but I need to calculate points for each employee where: LT(Tardy) = 0.50 pts A(Absent) = 1 pt

The table looks like this:

12/1/22   12/2/22   12/3/22   12/4/22       Total Points
  A         LT       LT        LT                ?

Can I calculate total points in a range where LT is 0.50 pts and A = 1 pt??

Thanks!!

I can not figure out SUMIF to calculate total where a text string meets a specific numeric value

Upvotes: 0

Answers (4)

Reputation: 6749

A short one for this specific case (two possible values):

=SUM(IF(A2:D2="A",1,0.5))

Upvotes: -1

Reputation: 27233

I know, I am late, as the solutions posted by Scott Craner Sir, & Rory Sir are very useful, however sharing one more alternative way.

• Formula used in cell E2

=SUM((A2:D2={"A";"LT"})*{1;0.5})

Let me show you what it creates, before wrapping within SUM()

It creates an array of 2 rows x 4 columns, next we are multiplying by {1;0.5} matrix calculation,

Last but not least to get the counts we are summing the matrix which returns 2.5.

Upvotes: 1

Reputation: 34035

FWIW, you can also use arrays of criteria and values like this:

=SUM(COUNTIF(A2:D2,{"A","LT"})*{1,0.5})

Upvotes: 4

Reputation: 152450

Use COUNTIFS:

=COUNTIFS(A2:D2,"A")+COUNTIFS(A2:D2,"LT")/2

Upvotes: 2