summing in same column based on another cell value

[Simon Clark]

Hi guys.

I would like to create a sum in a column, but the sum has to be based
on another cell value.

So... if i have values in column A (already calculations based on
something else) i want to make a sum of those values where the
adjacent cell equals a certain value. This occurance can happen for
more than 100 rows, so i am guessing i need some VBA here.

E.g.
----
A B
11 Yes
12 no
12 no1
23 no
34 no2
53 Yes
83 no
1 no

''Where B equals "Yes" sum in column A until the next Occurance of
"Yes".
''Where there's a value in column A with Yes..ignore value
''Therefore in Cell A1,
''i should have a total of... 81
''(A2+A3+A4+A5)
''and in cell A6 i should have 84 (A7+A8)

So the formula will continue for each time there's a Yes in column
"B".

Can this be done in VBA?

Thanks...

Sim

 

[Frank Kabel]

Hi Simon
maybe a helper column with only formulas will do for you. enter the
following formula in C1:
=IF(B1<>"Yes","",SUM(OFFSET(A1,0,0,MATCH("Yes",B2:B$100,0)))-A1)
and copy this down

 

[Simon Clark]

Hi Simon
maybe a helper column with only formulas will do for you. enter the
following formula in C1:
=IF(B1<>"Yes","",SUM(OFFSET(A1,0,0,MATCH("Yes",B2:B$100,0)))-A1)
and copy this down

That's some pretty good stuff. It works exactly how i want it.

The problem i have, is that i already have some formulas in cells, so
when using the IF statement, i receive some VALUE errors. Therefore,
is this possible in VBA? Also, the number of rows can change from
week to week.


Any ideas?

Cheers...

Sim