how to make IF conditions in SUMIF automatically extend to more columns?-CodePudding

Hi everyone I have the following excel table:

my task (performed in the green cell) is that for each year I compute the sum of the rows that contain a negative number in at least one of the previous years.

For instance the -1.29 in year two is the sum of the values in year to corresponding to rows 1,5,6,10,12 which are the rows containing negative numbers in the previous year. For year 3 same logic except that now I can sum all the values for that year from rows containing negative values either in Year 1 or Year 2.

for now in each of the green cells I am using the following formulas:

Year1 : 0 Year2 : =SUM(IF(B3:B13="-";0;B3:B13)($A$3:$A$13<0)) Year3 : =SUM(IF(C3:C13="-";0;C3:C13)((($B$3:$B$13<0) ($A$3:$A$13<0))>0)) Year4 : =SUM(IF(D3:D13="-";0;D3:D13)*((($C$3:$C$13<0) ($B$3:$B$13<0) ($A$3:$A$13<0))>0))

but I would like to make recursive, in the sense that I can just drag it to the next year without having to add one more of this ($C$3:$C$13<0) terms

would that be possible by just using Excel Formulas (hence no VBA)? An excel function computing the product of two vectors element by element would be enough but I cannot find anything like that unfortunately.

Hope to find some help! Best, Federico

CodePudding user response：

Use MMULT:

=SUM(B2:B13*(MMULT(--($A$2:A13<0);TRANSPOSE(COLUMN($A$1:A1))^0)>0))

add formula to B14 and copy to the right. This is array formula.

CodePudding user response：

If I understand you correctly, the following does what you want.

{=SUM(IF(A$1:A$13<0,B$1:B$13))}

Notes:
o The formula as shown is as it would be entered at B14
o Note the {}: This is an array formal so use Ctrl, Shift and Enter to enter the first case
o You don't need to eliminate text values (SUM does that automatically)
o I prefer to include headings (if they exist and are text) as it makes growing the table easier

Not sure why, but your example formulas started at row 3 (excluding row 2).
If that is truly what's wanted amend the above to:

{=SUM(IF(A$3:A$13<0,B$3:B$13))}

CodePudding user response：

There is a quite simple solution in VBA:

Function getSum(rng As Range) As Variant
    
    Dim r As Long, c As Long
    
    For r = 1 To rng.Rows.Count
        For c = 1 To rng.Columns.Count - 1
            If rng.Cells(r, c) < 0 Then
                getSum = getSum   rng.Cells(r, rng.Columns.Count)
                Exit For
                End If
            Next
        Next
    
End Function

Where the range passed to the function is written with a combination static/dynamic definition so the range expands. The upper left cell of the range will be the first cell of your data, static: say $A$3, and the lower right corner will be the bottom cell of the column with the values you are wanting to sum and dynamic, say: B14. In that case the formula =getSum($A$3:B14) would go in B15 and could be dragged over. Here's a picture:

I originally answered a different question than was asked. Currently editing to address the asked question using formulas only.