PDA

View Full Version : [SOLVED] How to sum cells based on ?? (array? constants?)



ValerieT
10-31-2013, 03:59 AM
A

B

C



1

CEO

0.0

0.0



2

G

60.0

60.0



3

COO

0.0

0.0



4

O

5.0

5.0



5

E

13107.0

13107.0



6

PROD

45.0

45.0



7

OE

3.0

3.0



8

D

1182.2

1182.2



9

DS

1372.3

1372.3



10

DC

513.0

513.0



11

P

379.0

379.0




In this example, I want red cells B3,C3 to sum the cells in row yellow BUT it should comes from a constant or an array, so that user has just one line of code to change when it evolves (he can go into code, but simple one)

I've thought about having a string constant, that would become my array, but i'm getting totally lost, it's too hard for me... Do you have a better idea?

Public Const ACOO As String = "O,E,PROD,OE,OT,P,Q,I,J,PROD,S,BD,BM"

For i = 1 To 4
T_COOO = Array(ACOO)
My_ID = T_COOO(i)

For MyRow = 2 To 56
If Cells(My_Row, CSIG) = My_ID Then
My_Total = My_Total + Cells(My_Row, 2)
End If
Next MyRow

Cells(RSN4, 2) = My_Total
Next i

p45cal
10-31-2013, 07:14 AM
T_COOO = Split(ACOO, ",")
For i = 0 To 3
For MyRow = 2 To 56
If Cells(My_Row, CSIG) = T_COOO(i) Then My_Total = My_Total + Cells(My_Row, 2)
Next MyRow
Cells(RSN4, 2) = My_Total
Next i
?

ValerieT
10-31-2013, 08:24 AM
I'll try that on Monday. I have a doubt: As the lengh of string ACOO is unknown, what will happen if "i" is too big? can I solve it by a simple "On error resume next"?

p45cal
10-31-2013, 08:44 AM
I'll try that on Monday. I have a doubt: As the lengh of string ACOO is unknown, what will happen if "i" is too big? can I solve it by a simple "On error resume next"?
For i = 0 To UBound(T_COOO)

snb
10-31-2013, 01:14 PM
to sum the data in column B, according to the value conditions in column A:

Sub M_snb()
y = [sum(if(iserror(match(A2:A56,{"E","I","J","O","P","Q","S","BD","BM","OE","OT","PROD"},0)),0,B2:B56))]
End Sub

snb
10-31-2013, 01:15 PM
to sum the data in column B, according to the value conditions in column A:


Sub M_snb()
y = [sum(if(iserror(match(A2:A56,{"E","I","J","O","P","Q","S","BD","BM","OE","OT","PROD"},0)),0,B2:B56))]
End Sub

ValerieT
11-07-2013, 07:12 AM
I finally has time to test it. After some errors, it works fine. I thank you very much