Help with Advanced Sum Function (Sumif/Sumproduct Combo)

[Cyclone364]

Hi all,

Long time user, first time poster, so I know I'm coming to the right place! Anyway, I had a friend reach out to me for some help with an excel formula she is trying to write. She has a table of data with Deal Numbers in the first column, a Strategy ID in the second, an associated date in the third, and a value in the last column (screenshot below).

Essentially what she wants done is for the values to be summed only for strategy A and only for the deals that exist in both January and February. To clarify, she does not want the Jan and Feb values added together (which would be easy enough), she just wants the Jan values summed for deals that have associated entries in Feb.

I created the below formula which accomplishes this, but now she is wanting to know if there is a way to do it without having to directly reference the range of Feb data (i.e., use full columns instead of specific ranges ($D:$D, etc.). The Feb and Jan data would be mixed together so the formula would need to run the test to see if the deal was in both Jan and Feb somehow without referencing data from a Feb section as I have done. I've spent around 4 and half hours with this, scouring forums, playing with Arrays and every version of a Sumifs I can think of. The answer is 320 from the below data set. Any help would be greatly appreciated! Please let me know if I can provide any other information that would be helpful!

Thanks,
Adam

 

[Aladin Akyurek]

Thank you so much Aladin! That's exactly what I was looking for! Now I'm going to dig through the formula to make sure I understand how it's working.

You are welcome. Try to select the relevant bits on the formula bar and apply F9 in order to see to what or how they evaluate...

I tried updating the column references to exclude the Row numbers (i.e., $D:$D), and it stopped working, but I'm assuming that's just because it overloads the memory available for the array (not sure if that's the appropriate terminology). I'll just adjust the range to go through 1000 or something instead. Thanks again for the help and for the patience with my being a first time poster!

Adam

In fact, you want the formula to be dynamic, that is, whenever you add/remove a record from the data, the formulas pick out the valid range at any time. Three methods are available...

1) Whole column references. A good idea when we have a SumIfs formula for example, but not with an array-processing formula as you found out.

2) Convert the data area into a table (thru Insert | Table). This forces one to use the so called structured references. You must become fond of this baroque addition to Excel first...

3) Resort to the so called dynamic named ranges.

What follows takes up the 3rd option.

Define Lrow using Formulas | Name Manager as referring to:

=MATCH(9.99999999999999E+307,Sheet1!$C:$C,1)

Define Deals as referring to:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,Lrow)

Define Strategies as:

=Sheet1!$B$2:INDEX(Sheet1!$B:$B,Lrow)

Define Dates as:

=Sheet1!$C$2:INDEX(Sheet1!$C:$C,Lrow)

Define Values as:

=Sheet1!$D$2:INDEX(Sheet1!$D:$D,Lrow)

Adjust the sheet name to suit.

Now our formula becomes, control+shift+enter:

=SUM(IF(ISNUMBER(MATCH(IF(Dates=F2,IF(Strategies=F4,Deals)),
    IF(Dates=F3,IF(Strategies=F4,Deals,"#"),"#"),0)),Values))

 

[Cyclone364]

Awesome, I'll go with method 3 then! That seems more my speed. Thanks again for all of the help with this! I hope you have a great weekend!

 

[Aladin Akyurek]

Awesome, I'll go with method 3 then! That seems more my speed. Thanks again for all of the help with this! I hope you have a great weekend!

You are welcome.