How do I repeat function over several row.

Question

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?

Answer 1

This Function should work:

Option Explicit

Public Function getMySum(src As Range, sumColumn As Integer)
Dim iRow As Range
Dim yColumn As Integer

    yColumn = 1
    getMySum = 0

    For Each iRow In src.Rows

        If (Strings.StrConv(iRow.Cells(1, yColumn), vbLowerCase) <> "y") Or (iRow.Cells(1, sumColumn) <> 0) Then
            getMySum = getMySum + iRow.Cells(1, sumColumn)
        Else
            getMySum = 0
            Exit For
        End If
    Next iRow
End Function

You need to add this Code to a VBA Module

The function call for your Example would be: =getMySum("C:23:D25", 2)

The only other option I see would be to combine the value in c and d like =C23&";"&D23 and sumif VLOOKUP that searches for "y;0" returns an error.

Answer 2

I'm not sure if I understand the question 100%, but here's an answer anyway:

{=NOT(SUM((C23:C25="y")*(D23:D25=0)))*SUM(D23:D25)}

Don't enter the curly braces, rather enter the formula using Control+Shift+Enter to make it an array formula. Here's what it does:

First, it counts all the rows where C is 'y' and D is zero. That will return (in this example) 0, 1, 2, or 3. The NOT will change the zero to a 1 and change anything else to a zero. Then that one or zero will be multiplied by the sum of column D.

So if zero rows have both 'y' and 0, multiply the SUM by 1. If more than zero rows have both 'y' and 0, multiply the SUM by 0.