How to sum up integers from multiple sources

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?)

DateStock Symbol (List 1)Short
Volume
DateStock Symbol (List 2)Short
Volume
Unique Symbols from both listsTotal Short Volume 8/1/2017Total Short Volume 8/2/2017Total Short Volume 8/3/2017
08/01/17A66,33308/01/17A122A66,45592,2580
08/02/17A92,25808/03/17A28B0426,0040
09/13/17A92,53008/24/17A2,900C000
09/14/17A58,63508/25/17A161D0230,82469,742
11/09/17A35,67408/28/17A857
11/10/17A60,92008/29/17A482
08/02/17B426,00408/30/17A1,964 ^^^^^
08/03/17B194,37908/31/17A1,654Note: In above table, I have a "0", when there is
08/04/17B246,11709/01/17A1,164no data available from column on the left.
08/07/17B602,10909/05/17A678
08/08/17B965,44011/02/17A638
08/09/17B433,01311/03/17A426
08/10/17B16,71308/08/17B56,600
08/11/17B9,72108/09/17B59,568
09/12/17B28,12509/06/17C454,004
09/13/17B40,07609/07/17C541,540
09/14/17B36,84909/08/17C124,652
09/15/17B33,94509/11/17C245,004
08/02/17D115,41209/12/17C54,693
08/03/17D34,87109/13/17C567,410
08/08/17D24,65408/02/17D115,412
08/09/17D65,18808/03/17D34,871
08/22/17D27,401
08/23/17D243,200
08/25/17D546,358

<tbody>
</tbody>
 
Last edited:

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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.

Exchange 1DateVolumeExchange 2DateVolume _______
StocksTotal Vol 8/1Total Vol 8/2Total Vol 8/3Total Vol 8/4Total Vol 8/5
APPLE8/1/17200APPLE8/5/17400Apple200900
APPLE8/5/17500HP8/3/17500Dell300
DELL8/4/17300HP8/5/17100HP200600100
HP8/2/17200MICROSOFT8/1/17300Microsoft800400
HP8/3/17100MICROSOFT8/3/17400
MICROSOFT8/1/17500

<tbody>
</tbody>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

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