Conditional Total in a Cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to get a sum of a range depending on the response in another cell.
I have many ranges on a spreadsheet I need totals for.

Example:
A1 X A2 6 A3 RN/LVN
B1 X B2 6 B3 CP
C1 X C2 6 C3 RN/LVN

D2: Sum of the range for A2:C2 if A3:C3 'contains' 'RN'
E2: Next Sum: Sum of the range A2:C2 if A3:C3 equals 'CP'
D1: Sum of A2:C2 if A1:B1 = 'X' and A3:C3 'contains' 'RN'
E1: Sum of A2:C2 if A1:B1 = 'X' and A3:C3 equals 'CP'
 
=SUMPRODUCT(--(LEFT(A3:C3,2)="RN"),A2:C2)
=SUMPRODUCT(--(LEFT(A3:C3,2)="CP"),A2:C2)
=SUMPRODUCT(--(A1:C1="X"),--(LEFT(A3:C3,2)="RN"),A2:C2)
=SUMPRODUCT(--(A1:C1="X"),--(LEFT(A3:C3,2)="CP"),A2:C2)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Back
Top