Excel Spreadsheet

[Redturtle098]

Hey everyone!

I've been trying to create a graph in Excel that contains individual purchases, and presents the sum of these purchases at the end of a day.
For example:

 

Column1          Column 2

7 June 2018     $5.50

7 June 2018     $25.00

8 June 2018     $10.00

9 June 2018     $52.00

 

I want to create a line graph that shows each day, but not each purchase. Creating a line graph, it shows the date on the x axis, and the purchase amount on the y axis.
However, it shows the greatest purchase of each day, instead of the sum.

Ideally, it'd have 7,8,9 June on the x axis, with each day having $30.50 (instead of $25.00!!), $10.00 and $52.00 on the y axis.

Hope this all makes sense!
Any help with this topic would be awesome!
Thanks!

[Charrette]

Try doing a sum function by day and just point the graph to the sum output.

[LogicalDrm]

Create another sheet where you have just single row per day. Then have sum in there for each day and use that sheet to make graph. You can do this in same sheet too, but for larger dayasets another sheet is more clear.

[marten.aap2.0]

18 hours ago, Redturtle098 said:

However, it shows the greatest purchase of each day, instead of the sum.

I tried to remake your table with graph, it doesn't show the greatest, it shows both, but on top of each other both starting from 0 where one goes to 25 and one to 5.50.

I haven't found a way to show the sum yet, I'll try some more methods and tell you when I find something useful.

[Charrette]

Okay, let's start off simple.

 

Start with your column headers. In my case I just went with date, vendor, product and price. For the sake of making this example work, put 'Date' in A1, 'Vendor' in B1, 'Product' in C1, and 'Price' in D1. I formatted the date column to show DD/MM/YY but you can get away with DD/MM.

 

Start entering in your purchase data, being sure to start with the earliest date you can think of, however, you don't necessarily have to enter your purchase data in chronological order after that. Just be sure that your first date (cell A2) is the earliest date you started.

 

In a different spot, even a different sheet of the workbook, enter in a starting date using the same format as your column headers. In order for my example to work, start typing the date in cell F1. Click/hold and drag down from the lower right corner of the cell to populate a date range large enough to cover the time you think you'll need; Excel will auto increment each day by one. Then, in cell G1, type in:

 

" =SUMIF(A:A,F1,D:D) "

 

What this does is it will find all dates matching F1, and will in turn sum the corresponding costs for that date only. Be sure to hit 'enter'. Now double-click the lower-right corner of cell G1 to have Excel auto-populate the function down the sheet to match all of your dates in column 'F'.

 

Now select cell F1 and insert your desired chart type.

 

Excel will probably increment in one-week chunks.

 

The nice thing is if you forgot to enter in a purchase, again, as long as it's not prior to your start date, you can enter purchases in your data columns (A-D) and the graph will auto update without having to refresh.

 

Sorry for any 'mansplaining' but I don't know your level of experience.

[Charrette]

Sorry, for the example to work, type everything on the same sheet. You can redirect later once you get the concept.