Summing row values conditionally by column headers

[ajhess]

I would appreciate some help with this project. Using VBA: for each row in the below table, I would like to create a variable of the sum of certain columns based on the value in row 3. For example: activity code HC9200006, if the column header in row 3 is a '2', i want to sum those values together (so the result for this code would be 35). Then I would like to do the same thing for when row 3 is a '3'.

The end goal is to compare those sum values against the 'CLASS' field for each activity code, and then highlight the row if certain conditions are not met. Thanks in advance...

_ 'row 3     	1	2	2	3	3
ActivityCode	CLASS	VL200	VL300	LAB	MEDSRG
HC2702842	0	0	0	0	0
HC2702820	0	0	0	0	0
HC9200006	1	30	5	0	0
HC4800266	1	0	0	0	0
HC2600005	1	10	30	0	0
HC2701584	2	0	0	1	16
HC2702780	2	0	0	0	139
HC2702782	2	0	0	0	118
HC2701766	2	0	0	1	24
HC2702818	2	0	0	0	29
HC2700933	2	0	0	1	21
HC2702781	2	0	0	0	291
HC7600084	3	7	15	0	1

_

 

[Andrzejek]

Your post is really confusing (at least to me), so this is my guess: you have data in Excel that looks like this and you have column headers in rows 1, 2, 3, and 4:

[pre]
A B C D E F
1
2
3 'row 3 1 2 2 3 3
4 ActivityCode CLASS VL200 VL300 LAB MEDSRG
5 HC2702842 0 0 0 0 0
6 HC2702820 0 0 0 0 0
7 HC9200006 1 30 5 0 0
8 HC4800266 1 0 0 0 0
9 HC2600005 1 10 30 0 0
10 HC2701584 2 0 0 1 16
11 HC2702780 2 0 0 0 139
12 HC2702782 2 0 0 0 118
13 HC2701766 2 0 0 1 24
14 HC2702818 2 0 0 0 29
15 HC2700933 2 0 0 1 21
16 HC2702781 2 0 0 0 291
17 HC7600084 3 7 15 0 1
[/pre]
"For example: activity code HC9200006, if the column header in row 3 is a '2', i want to sum those values together [which values?] (so the result for this code would be 35)."
What values/calculations give you 35? And where do you want to display this 35?


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[ajhess]

Hi Andy, correct that is what my Excel data looks like.
For each row, i want to sum the values that have a row 3 column header = 2. So in my example of activity code HC9200006, I would like to sum the 30 from column C and the 5 from column D. Ideally, i would like to hold that 35 in a variable for use later on in my code.

Hopefully this helps clear things up? thank you
Andrew

 

[SkipVought]

So why is the user entering 3 in A3? Does the user know that 3 is significant or is row 3 where HC9200006 is the Activity Code? It would seem to me that the latter is the most significant. What about if the Activity code were in row 37? Would the user need to COUNT the rows?

 

[SkipVought]

Also...

"Summing row values conditionally by column headers"

Plural, column HEADERS.

So you addressed the value in column 1. What about the other values in row 3????

You're holding back information AND not doing a very good job explaining this entire thing!

 

[ajhess]

The user does not enter row 3, it's a database grouping field for the items in row 4.
The other values in row 3 aren't relevant, I only care when the value is 2 or 3.
If the activity code was in row 37, i would want the loop to continue as it did with each other activity code

Maybe I should try re-stating my problem. For each Activity Code, I would like to sum the row values together... but only for like items (signified by the values in rows 3 and 4). The desired end-result would be to create 2 variables that hold the summed row values for each activity code in my dataset:
1 that holds the summed row values when the row 3 column header = 2 (in my example, columns C & D)
Another that holds the summed row values when the row 3 column header = 3 (in my example, columns E & F). Please see my updated data table below...

 ___ A           B       C       D       E       F
1
2
3        	1	2	2	3	3         Variable 1     Variable 2
4  ActivityCode	CLASS	VL200	VL300	LAB	MEDSRG
5  HC2702842	0	0	0	0	0         0              0 
6  HC2702820	0	0	0	0	0         0              0
7  HC9200006	1	30	5	0	0         35             0
8  HC4800266	1	0	0	0	0         0              0
9  HC2600005	1	10	30	0	0         40             0
10 HC2701584	2	0	0	1	16        0              17
11 HC2702780	2	0	0	0	139       0              139
12 HC2702782	2	0	0	0	118       0              118
13 HC2701766	2	0	0	1	24        0              25  
14 HC2702818	2	0	0	0	29        0              29
15 HC2700933	2	0	0	1	21        0              22
16 HC2702781	2	0	0	0	291       0              291  
17 HC7600084	3	7	15	0	1         22             1

 

[SkipVought]

The user does not enter row 3, it's a database grouping field for the items in row 4."

That makes no sense. So how does this data get into row 3? And what does row 4 have to do with anything?

"The other values in row 3 aren't relevant, I only care when the value is 2 or 3."

Then why does your thread title state, "Summing row values conditionally by column [highlight #FCE94F]headers[/highlight]"

"If the activity code was in row 37, i would want the loop to continue as it did with each other activity code"

What loop?
"As it did". Where did you explain this?

"I would like to sum the row values together... but only for like items (signified by the values in rows 3 and 4)"

Please explain exactly what this means. What values in row 3 and what values in row 4 effect the sum?


I deduce from your last example that you might simply want a formula in columns G & H to sum the desired columns in that row.

 

[ajhess]

SkipVought, we can't seem to get on the same page here. I don't see how I could explain this any clearer than I already have; so rather than waste any more of our time I'm going to keep having a go at this on my own. Thanks for looking,

Andrew

 

[SkipVought]

What about the last paragraph?