Excel (or access ) help please

Stoner

https://www.dropbox.com/s/99earutyvkgo3lv/Capture.JPG?dl=0

Hi looking for some help adding up the total number of products with the same product number please

I've attached a pic of a data snip

This is from a procurement report, products were bought on a project at different time and different amount.

I'd like to find out the total amount of each product that was bought
So in the Field E2 I'd like to see the number 50

I'd like the formula to add the total number of product E000158 that we bought, it would need to Add up the numbers in column D per item in Column A


Any help would be appreciated.

If there is a formula i could use in excel please.

henryporter

Try SUMIF as follows:

=SUMIF(A1:A17,A2,D1:D17)

Explanation: A1:A17 is the range you want to test for the value that is given as A2, D1:D17 are the values that are tested (based on your example)

tanit

Okay just so I understand your question: you have a list with product numbers in column A, column B would be the number of products bought, column C would be the date, column D would be the price. If that is correct and what you want to do is find out the total amount of one product bought I suggest using Filters in the first row ordering the whole thing by product number you select the whole data in the worksheet and use the Subtotals function in the Data tab in excel. First create a column E multiplying column D (price) by column B (number of products bought. You will be asked which are the subtotals you want to create and what kind of operation to perform in your case in column and just click.

You can find more information about this function in this article from the Microsoft Office Website "support.office.com/en-ie/article/SUBTOTAL-function-7b027003-f060-4ade-9040-e478765b9939". I'm a newbie here and it doesn't let me post Urls so add the whole https:// to the rest of the stuff inside the quotes

I might be a bit biased towards this function but I'm pretty sure it will sort you out.

wavydave

Use a pivot table. Select all the range of cells that have data you require - item, number, price, number sold etc. Then Insert Pivot Table. You will be able to get price per item total sold all from the pivot. Check out a couple of videos on pivot tables on YouTube.

Stoner

Thanks guys,
I'm not looking for a price . I've already arranged the products in order, so the first three product numbers you see would be the only occurrences of that product in the whole data set , currently 5000 records

I've trimmed it all down using a database query left out price , total price , supplier , dates etc.


I'd simply want to know how many of each item we bought.
I've an identical project and I want to have an idea of order volumes to negotiate with single suppliers .

I'll look into all the suggestions.

Thank you all very much .

If something I've said here makes this clearer and someone has a different method please let me know.

tanit

Stoner wrote: »

Thanks guys,
I'm not looking for a price . I've already arranged the products in order, so the first three product numbers you see would be the only occurrences of that product in the whole data set , currently 5000 records

I've trimmed it all down using a database query left out price , total price , supplier , dates etc.


I'd simply want to know how many of each item we bought.
I've an identical project and I want to have an idea of order volumes to negotiate with single suppliers .

I'll look into all the suggestions.

Thank you all very much .

If something I've said here makes this clearer and someone has a different method please let me know.

Following the steps I gave you before you could use the Subtotal function adding the colum where you have the the amounts bought. You could either use Sum or Count in the function and you'll get the subtotal for each products. This is a very flexible function and there are more functions than just sum or count and basically it's pretty much a question of select and click.

But regardless I am intrigued just let us know how you sort out the whole thing it might be useful for me in the future and I'm not good with Pivot tables

Stoner

https://www.dropbox.com/s/4tmbfecu7v3f6rb/products.JPG?dl=0

ok I'm getting there

the sub totals function worked

I selected the product number and used the sum function, and selected quantity

However,

I am getting a sum I'm not getting it in the format I'd like,

Id have to copy this and do cut and paste across everything.

I need the cells populated

I'd have to get the totals information that it very quickly generated for me and remove the word Total for each line and then paste in the description and description 2 data for every product.

This would be less work but still a fair bit of work.

Regardless its a good help

thank you

db

As said above just use a pivot table and you can use a number of different formulas for your subtotals - count, sum, max, min etc. You can have as simple or complicated a pivot table as you want and present it in any number of different ways. Just select a range of cells and click Recommended PivotTables on the Insert tab and it will do all the work for you.

Stoner

I cant use a pivot table, or my understanding of same,

I need the following
Product number
Description
total Amount

I need each piece of information in its own cell in those columns (Product number, Description, total Amount)
When its arranged in a simple excel format ill add in a cost column

A supplier will fill that in, I'll save it as a CSV and import it into our purchasing package.

I cant have filter or combinations of product codes etc in one cell

The format for upload is .csv

The pivot table and subtotal are staying as active content, all that gets stripped away when i save it as a .csv

thanks for all your help guys i appreciate it.

info like this

https://www.dropbox.com/s/wey5y8iz7ymhma1/like%20this.JPG?dl=0

Stoner

i might have it now, enlisted more help.


Pivot table
changed the view to tablet view
turned off sub totals

Select the info and copy/paste into a new sheet