How do I repeat function over several row.
Posted
by ChrisBD
on Stack Overflow
See other posts from Stack Overflow
or by ChrisBD
Published on 2010-04-01T10:17:38Z
Indexed on
2010/04/01
11:13 UTC
Read the original article
Hit count: 174
I'll admit that I'm not an Excel guru so maybe someone here can help me.
On my worksheet I have several blocks of data.
I calculate the sum of all items within column D of that block. Within each block I am checking the value of the cell in column C and if it contains the letter "y" and the value in column D of that row is equal to zero I must exclude the total sum of column D.
Currently I am doing this by multiplying the sum value by either 1 or 0 which is produced by running a test over the cell contents.
Below is an example of what I am using to test rows 23 to row 25 inclusively for data in Column D. I am also performing the same on Column E and G, but the "y" character is always in column C, hence the absolut column reference.
=IF(AND($C23="y",D23=0),0,1)*IF(AND($C24="y",D24=0),0,1)*IF(AND($C25="y",D25=0),0,1)
There must be a more efficient way to do this.
Ideally I would like to write a function that I can paste into a cell and then select the rows or cells over which I run the test.
Can anyone point me in the right direction?
© Stack Overflow or respective owner