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

 

[Cyclone364]

Every time I try to paste the image in from Excel, it shows the image until I save the post, and then it only shows a long URL string after it saves. Do I have to link to an external URL to include my image?

 

[Cyclone364]

Nevermind, here's the pic!

 

[Cyclone364]

Anybody?

 

[Aladin Akyurek]

Care to post that sample in a way that is Excel readable?

 

[Cyclone364]

Sorry, are you saying to attach the file? I'm not entirely sure what you mean by Excel Readable. If that is what you're saying, what is the best way to do that? I don't see an option to direct attach a file, and I'm not sure where I could upload it to link to a URL.

 

[Aladin Akyurek]

Sorry, are you saying to attach the file? I'm not entirely sure what you mean by Excel Readable. If that is what you're saying, what is the best way to do that? I don't see an option to direct attach a file, and I'm not sure where I could upload it to link to a URL.

1) https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

2) http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

 

[Cyclone364]

Thanks Aladin! Below is the table. Please let me know if this is still not in the desired format.

Deal	Strategy	Date	Value
100023	B	1/31/2015	201		320
100024	B	1/31/2015	302
101102	A	1/31/2015	105
101103	A	1/31/2015	106
101104	A	1/31/2015	107
101105	A	1/31/2015	108
101106	A	1/31/2015	109
101107	A	1/31/2015	110
101108	A	1/31/2015	111
100055	C	1/31/2015	102
100066	C	1/31/2015	202
100023	B	2/28/2015	201
100024	B	2/28/2015	302
101102	A	2/28/2015	201
101104	A	2/28/2015	205
101105	A	2/28/2015	206
101316	A	2/28/2015	208
100763	C	2/28/2015	102
100066	C	2/28/2015	202

<colgroup><col span="2"><col><col span="2"><col></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

Thanks Aladin! Below is the table. Please let me know if this is still not in the desired format.
[...]

It's exactly the format for a sample. Thanks.

Row\Col	A​	B​	C​	D​	E​	F​
1​	Deal	Strategy	Date	Value
2​	100023​	B	1/31/2015​	201​		1/31/2015​
3​	100024​	B	1/31/2015​	302​		2/28/2015​
4​	101102​	A	1/31/2015​	105​		A
5​	101103​	A	1/31/2015​	106​		Total
6​	101104​	A	1/31/2015​	107​		320​
7​	101105​	A	1/31/2015​	108​
8​	101106​	A	1/31/2015​	109​
9​	101107​	A	1/31/2015​	110​
10​	101108​	A	1/31/2015​	111​
11​	100055​	C	1/31/2015​	102​
12​	100066​	C	1/31/2015​	202​
13​	100023​	B	2/28/2015​	201​
14​	100024​	B	2/28/2015​	302​
15​	101102​	A	2/28/2015​	201​
16​	101104​	A	2/28/2015​	205​
17​	101105​	A	2/28/2015​	206​
18​	101316​	A	2/28/2015​	208​
19​	100763​	C	2/28/2015​	102​
20​	100066​	C	2/28/2015​	202​

F6, control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(MATCH(IF($C$2:$C$20=F2,IF($B$2:$B$20=F4,$A$2:$A$20)),
    IF($C$2:$C$20=F3,IF($B$2:$B$20=F4,$A$2:$A$20,"#"),"#"),0)),$D$2:$D$20))

 

[Cyclone364]

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. 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