Function mySum(s As String, rSubs As Range) As Long
Dim Subs As Variant
Dim i As Long
Subs = rSubs.Value
s = "-" & Replace(s, "-", "--") & "-"
For i = 1 To UBound(Subs)
s = Replace(s, "-" & Subs(i, 1) & "-", "-" & Subs(i, 2) & "-")
Next i
mySum = Evaluate(Replace(s, "-", "+0"))
End Function
Excel 2016 | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | A | 7 | A | 7 | |||
2 | B | 6 | A-A | 14 | |||
3 | C | 5 | A-B-C | 18 | |||
4 | D | 4 | A-B | 13 | |||
5 | E | 3 | |||||
6 | F | 2 | |||||
7 | G | 1 | |||||
8 | |||||||
Sub Values |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E1 | =mySum(D1,A$1:B$7) |
Excel 2013/2016 | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | A | 7 | A | 7 | |||
2 | B | 6 | A-A | 14 | |||
3 | C | 5 | A-B | 13 | |||
4 | D | 4 | A-B-C | 18 | |||
5 | E | 3 | |||||
6 | F | 2 | |||||
7 | G | 1 | |||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E1 | =SUMPRODUCT(SUMIFS(B:B,A:A,TRIM(MID(SUBSTITUTE(D1,"-",REPT(" ",99)),{1,2,3,4,5,6,7,8,9,10}*99-98,99)))) |
Mon 22/07/2019 | A | A | A | A | A | D | A | 46 |
Fri 26/07/2019 | B | B | A | B | A | F | A | 41 |
Sat 27/07/2019 | B | A | A | B | A | F | A | 42 |
Sun 28/07/2019 | B | C | A | A | A | E | A | 42 |
Hi, here is one option..:
Excel 2013/2016
A B C D E F G H I 1 Mon 22/07/2019 A A A A A D A 46 2 Fri 26/07/2019 B B A B A F A 41 3 Sat 27/07/2019 B A A B A F A 42 4 Sun 28/07/2019 B C A A A E A 42 Sheet1
Cell Formulas Range Formula I1 =SUMPRODUCT(COUNTIF(B1:H1,{"A","B","C","D","E","F","G"}),{7,6,5,4,3,2,1})