How to sum up integers from multiple sources

[mark91345]

My goal is to add up the Short Volume for each stock, based on the same Stock Symbol and same date. Some stocks show on both lists, some don't. Some dates match on each list, some don't.

I have tried using vlookup, as well as Index/Match, but I get confused because I am unfamiliar with dealing with multiple criteria. (Note I am using Excel 2016. Also, I don't seem to know how to copy an excel worksheet, except Copy & Paste... is there a more proper way on this website?)

[TABLE="width: 1436"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Stock Symbol (List 1)[/TD]
[TD]Short
Volume[/TD]
[TD][/TD]
[TD]Date[/TD]
[TD]Stock Symbol (List 2)[/TD]
[TD]Short
Volume[/TD]
[TD][/TD]
[TD]Unique Symbols from both lists[/TD]
[TD]Total Short Volume 8/1/2017[/TD]
[TD]Total Short Volume 8/2/2017[/TD]
[TD]Total Short Volume 8/3/2017[/TD]
[/TR]
[TR]
[TD]08/01/17[/TD]
[TD]A[/TD]
[TD="align: right"]66,333[/TD]
[TD][/TD]
[TD]08/01/17[/TD]
[TD]A[/TD]
[TD="align: right"]122[/TD]
[TD][/TD]
[TD]A[/TD]
[TD="align: right"]66,455[/TD]
[TD="align: right"]92,258[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]08/02/17[/TD]
[TD]A[/TD]
[TD="align: right"]92,258[/TD]
[TD][/TD]
[TD]08/03/17[/TD]
[TD]A[/TD]
[TD="align: right"]28[/TD]
[TD][/TD]
[TD]B[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]426,004[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]09/13/17[/TD]
[TD]A[/TD]
[TD="align: right"]92,530[/TD]
[TD][/TD]
[TD]08/24/17[/TD]
[TD]A[/TD]
[TD="align: right"]2,900[/TD]
[TD][/TD]
[TD]C[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]09/14/17[/TD]
[TD]A[/TD]
[TD="align: right"]58,635[/TD]
[TD][/TD]
[TD]08/25/17[/TD]
[TD]A[/TD]
[TD="align: right"]161[/TD]
[TD][/TD]
[TD]D[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]230,824[/TD]
[TD="align: right"]69,742[/TD]
[/TR]
[TR]
[TD]11/09/17[/TD]
[TD]A[/TD]
[TD="align: right"]35,674[/TD]
[TD][/TD]
[TD]08/28/17[/TD]
[TD]A[/TD]
[TD="align: right"]857[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/10/17[/TD]
[TD]A[/TD]
[TD="align: right"]60,920[/TD]
[TD][/TD]
[TD]08/29/17[/TD]
[TD]A[/TD]
[TD="align: right"]482[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08/02/17[/TD]
[TD]B[/TD]
[TD="align: right"]426,004[/TD]
[TD][/TD]
[TD]08/30/17[/TD]
[TD]A[/TD]
[TD="align: right"]1,964[/TD]
[TD][/TD]
[TD] ^^^^^[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08/03/17[/TD]
[TD]B[/TD]
[TD="align: right"]194,379[/TD]
[TD][/TD]
[TD]08/31/17[/TD]
[TD]A[/TD]
[TD="align: right"]1,654[/TD]
[TD][/TD]
[TD="colspan: 4"]Note: In above table, I have a "0", when there is[/TD]
[/TR]
[TR]
[TD]08/04/17[/TD]
[TD]B[/TD]
[TD="align: right"]246,117[/TD]
[TD][/TD]
[TD]09/01/17[/TD]
[TD]A[/TD]
[TD="align: right"]1,164[/TD]
[TD][/TD]
[TD="colspan: 3"]no data available from column on the left.[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08/07/17[/TD]
[TD]B[/TD]
[TD="align: right"]602,109[/TD]
[TD][/TD]
[TD]09/05/17[/TD]
[TD]A[/TD]
[TD="align: right"]678[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08/08/17[/TD]
[TD]B[/TD]
[TD="align: right"]965,440[/TD]
[TD][/TD]
[TD]11/02/17[/TD]
[TD]A[/TD]
[TD="align: right"]638[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08/09/17[/TD]
[TD]B[/TD]
[TD="align: right"]433,013[/TD]
[TD][/TD]
[TD]11/03/17[/TD]
[TD]A[/TD]
[TD="align: right"]426[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08/10/17[/TD]
[TD]B[/TD]
[TD="align: right"]16,713[/TD]
[TD][/TD]
[TD]08/08/17[/TD]
[TD]B[/TD]
[TD="align: right"]56,600[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08/11/17[/TD]
[TD]B[/TD]
[TD="align: right"]9,721[/TD]
[TD][/TD]
[TD]08/09/17[/TD]
[TD]B[/TD]
[TD="align: right"]59,568[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]09/12/17[/TD]
[TD]B[/TD]
[TD="align: right"]28,125[/TD]
[TD][/TD]
[TD]09/06/17[/TD]
[TD]C[/TD]
[TD="align: right"]454,004[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]09/13/17[/TD]
[TD]B[/TD]
[TD="align: right"]40,076[/TD]
[TD][/TD]
[TD]09/07/17[/TD]
[TD]C[/TD]
[TD="align: right"]541,540[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]09/14/17[/TD]
[TD]B[/TD]
[TD="align: right"]36,849[/TD]
[TD][/TD]
[TD]09/08/17[/TD]
[TD]C[/TD]
[TD="align: right"]124,652[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]09/15/17[/TD]
[TD]B[/TD]
[TD="align: right"]33,945[/TD]
[TD][/TD]
[TD]09/11/17[/TD]
[TD]C[/TD]
[TD="align: right"]245,004[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08/02/17[/TD]
[TD]D[/TD]
[TD="align: right"]115,412[/TD]
[TD][/TD]
[TD]09/12/17[/TD]
[TD]C[/TD]
[TD="align: right"]54,693[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08/03/17[/TD]
[TD]D[/TD]
[TD="align: right"]34,871[/TD]
[TD][/TD]
[TD]09/13/17[/TD]
[TD]C[/TD]
[TD="align: right"]567,410[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08/08/17[/TD]
[TD]D[/TD]
[TD="align: right"]24,654[/TD]
[TD][/TD]
[TD]08/02/17[/TD]
[TD]D[/TD]
[TD="align: right"]115,412[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08/09/17[/TD]
[TD]D[/TD]
[TD="align: right"]65,188[/TD]
[TD][/TD]
[TD]08/03/17[/TD]
[TD]D[/TD]
[TD="align: right"]34,871[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08/22/17[/TD]
[TD]D[/TD]
[TD="align: right"]27,401[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08/23/17[/TD]
[TD]D[/TD]
[TD="align: right"]243,200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08/25/17[/TD]
[TD]D[/TD]
[TD="align: right"]546,358[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mrshl9898]

Wouldn't SUMIFS work here? eg

=SUMIFS(C:C,A:A,"08/01/17",B:B,"A")+SUMIFS(F:F,D:D,"08/01/17",E:E,"A")

 

[mark91345]

Ok, I tried it and yes, it works, but only for the specific stock (in this case "A"). I should have mentioned that I have thousands of stocks I will be looking at, so I am not sure how to continue.

 

[mrshl9898]

instead of "A" have the cell reference with the value in it. Judging from the above something like:

=SUMIFS(C:C,A:A,"08/01/17",B:B,$G2)+SUMIFS(F:F,D:D,"08/01/17",E:E,$G2)

Same with date, though that looks like it is currently part of the heading, you can change the heading, move the date or use something (maybe DATEVALUE and MID/LEFT/RIGHT to extract it from the string)

 

[mark91345]

Thanks again, but the real list is thousands of lines, with about 6 months of dates.
Thus, I have to "think big" and not search by entering specific dates.

Simply put, my goal is to have the total Volume, per stock, per date. For example, Apple traded on both Exchange 1 and 2. On 8/1/17, there was only one exchange that traded (200 shares total), while on 8/5/17, both exchanges traded (900 shares total).

So, I am attempting to match both the Stock and the Date, then when there is a match, show the total amount traded. When there is NOT a date match, I still want the # of shares traded.

Here is a simplified version of the table.

[TABLE="width: 1057"]
<tbody>[TR]
[TD]Exchange 1[/TD]
[TD]Date[/TD]
[TD]Volume[/TD]
[TD][/TD]
[TD]Exchange 2[/TD]
[TD]Date[/TD]
[TD]Volume[/TD]
[TD] _______
[/TD]
[TD]Stocks[/TD]
[TD]Total Vol 8/1[/TD]
[TD]Total Vol 8/2[/TD]
[TD]Total Vol 8/3[/TD]
[TD]Total Vol 8/4[/TD]
[TD]Total Vol 8/5[/TD]
[/TR]
[TR]
[TD]APPLE[/TD]
[TD]8/1/17[/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[TD]APPLE[/TD]
[TD]8/5/17[/TD]
[TD="align: right"]400[/TD]
[TD][/TD]
[TD]Apple[/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]900[/TD]
[/TR]
[TR]
[TD]APPLE[/TD]
[TD]8/5/17[/TD]
[TD="align: right"]500[/TD]
[TD][/TD]
[TD]HP[/TD]
[TD]8/3/17[/TD]
[TD="align: right"]500[/TD]
[TD][/TD]
[TD]Dell[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DELL[/TD]
[TD]8/4/17[/TD]
[TD="align: right"]300[/TD]
[TD][/TD]
[TD]HP[/TD]
[TD]8/5/17[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD]HP[/TD]
[TD][/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]600[/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]HP[/TD]
[TD]8/2/17[/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[TD]MICROSOFT[/TD]
[TD]8/1/17[/TD]
[TD="align: right"]300[/TD]
[TD][/TD]
[TD]Microsoft[/TD]
[TD="align: right"]800[/TD]
[TD][/TD]
[TD="align: right"]400[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HP[/TD]
[TD]8/3/17[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD]MICROSOFT[/TD]
[TD]8/3/17[/TD]
[TD="align: right"]400[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MICROSOFT[/TD]
[TD]8/1/17[/TD]
[TD="align: right"]500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]