Cyclone364
New Member
- Joined
- Mar 20, 2015
- Messages
- 8
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
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
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. 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!
