Find all similar entry names and add their qty

Hello I have a sheet I made where I load in all my orders of items needed for a job. Each Item has a code assigned to it I'm calling the Entry Name. There will be times where the same Code/Entry Name will appear several times during the life of the project.

For Example This week I might need 4 - BT01 (Entry Name) then as the project goes on the next phase I'll need 5 more - BTO1.

I'm trying to make a Master sheet Look at the order sheet every time it finds a BT01 it will take the qty ordered and add it total For that one item and display it on this new Maser list sheet I'm trying to make.

I plan to do this for the item for QTY Rec'd, QTY Ordered, Outstanding, How many shipped to the job.

Any suggestions how to make this come to life?

Find more posts tagged with

Construction

Formulas

functionality

Project Management

Accepted answers

Paul Newcome

You would only need to list each item one time. Then you can use a cell reference in the SUMIFS with a cross sheet reference to tally everything up for that particular item.

Item..........Formula

Item A............f

Item B............f

Item C............f

=SUMIFS({Working Sheet Count Column}, {Working Sheet Item Column}, Item@row)

Then you would dragfill the formula and it will automatically look for each of the items and tally them up.

Or do you mean you want something that will automatically generate the list of each used item one time for the tally to run against?

All comments

Andrée Starå

Hi @Grant Graves

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Grant Graves

Hope this helps @Andrée Starå Basically what I'm making is a shipping order/shipment tracker for items I order for a construction job. So for example sometimes there is a area of early turn over items I need so they can complete the section that must be finished and turned over to the owner. Then later I will have either 1 Bulk order of the remaining items to finish the project. Or possibly several small orders.

Each of these Orders can have duplicate entry names. As we might need more as the project goes on or need the same items for another area of the building.

What I need help with is I'm trying to make something Look at this sheet Find Same Entry Names Then add the Qty's Ordered. and list that. So I can have an overall view of how many of that item was ordered over the whole timeline of the project.

So this example below the first 2 Orders that were placed One order has 3 Unique items and how many we ordered. And the 2nd order has more items. The first item on both orders are the same Entry Name. So I'm looking a way to make another sheet Report Hey you have so far ordered (22+3=) 25 Total of the EA04 (entry name) Then I plan on making another column on that same sheet show me How many of that item were Received (using the data in the Rec'd Column)

Paul Newcome

Try working with the SUMIFS function (link included).

Grant Graves

@Paul Newcome If I used that I would have to write each "Entry Name" in the formula?

There will be hundreds/thousands of items I would have to list. I'm wondering If I can do something with the Index & match Feature to do the work.....

Paul Newcome

You would only need to list each item one time. Then you can use a cell reference in the SUMIFS with a cross sheet reference to tally everything up for that particular item.

Item..........Formula

Item A............f

Item B............f

Item C............f

=SUMIFS({Working Sheet Count Column}, {Working Sheet Item Column}, Item@row)

Then you would dragfill the formula and it will automatically look for each of the items and tally them up.

Or do you mean you want something that will automatically generate the list of each used item one time for the tally to run against?

Grant Graves

@Paul Newcome So Let's say the first order I get 3 - EA04 I'm looking to either have a column or another sheet give a summary Saying EA04 you ordered 3 of them.

Then as the time goes on I place another order for 10 more EA04. I'm looking to have that sheet Show now the new Total of 13 EA04 (Goal is to only show each item once with combined count of qty. Rather than showing EA04 listed several times with different QTY.)

Hope I'm explaining this clearly.

Paul Newcome

Either way you are going to need somewhere to track each individual order. You could do this with a form on a separate sheet (my personal suggestion) or any number of other ways.

Then on your "summary sheet" you would only list out EA04 one time and then use a SUMIFS to get that 13. It will automatically update as new entries are made and consolidates the data so that you only have one row to look at for a live total.

Andrée Starå

@Grant Graves

You're more than welcome!

I saw that Paul answered already!

Let me know if I can help with anything else!

Best,

Andrée

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.