How to sum up integers from multiple sources

M

mark91345

Board Regular
Joined
Feb 11, 2011
Messages
113
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]
 
Last edited:

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
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")
 
Upvote 0
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.
 
Upvote 0
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)




 
Last edited:
Upvote 0
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]
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Two way look up with mutiple duplicate criteria with mutiple return
Replies
3
Views
215
sanrama
S
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
423
dshtesy
D
E
  • Question Question
Pulling together data from multiple MS Forms spreadsheets and displaying into a single league table
Replies
6
Views
339
excelnewbie999
E
M
  • Question Question
Macro or Power Automate to Identify and Move "Washes" from the "Master" Sheet to the "Washes" Sheet
Replies
0
Views
192
Madam_Xavier
M
K
  • Solved
Index with Multiple Conditions
Replies
2
Views
110
kivikatz
K

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top