Excel Formula Help

Copper Contributor

I need a formula to retrieve data from one tab to bring back to another tab. I have 2 lookup criteria in the same column to bring back the sum of the data in a different column. 

11 Replies

@Tim-B 

It's hard to suggest any formula unless you provide more details like what are those criteria, where to look for them, what to be sum etc.

I would suggest you to upload a sample file and mock up the desired output manually to show us what exactly you are trying to achieve.

@Subodh_Tiwari_sktneer 

What I need is a formula to retrieve the sum of the dollars in column M in the Vendor Data tab. I need it based on column a in the vendor purchases tab. I also need it to be based on the individual vendors in the vendor data tab. I.E. Vendor purchases tab has 4201 in column a, the vendor data tab has this store under multiple vendors. I want Excel to sum the totals for each vendor for this store number. the first vendor is at cell A3 with different store numbers under it, then another vendor starting at cell A32. I will be downloading a new data tab each week so the data can change positions weekly based on the number of actual purchases per store. Under this example 4202 under 1 800 RADIATOR would total $374.60 for this example.

@Tim-B 

Thanks for the details.

I still suggest you to upload a small sample file with max 10 rows of data on both the Tabs along with the desired output mocked up manually to discard any confusion.

Please refer to the following image to know how to upload a sample file.

 

Upload File.jpg

@Subodh_Tiwari_sktneerI attached the spreadsheet

@Tim-B 

Try this and see if you get the desired output...

In D2

=SUMIF('Vendor Data'!A:A,A2,'Vendor Data'!M:M)

and then copy it down.

@Subodh_Tiwari_sktneerThat does not do the trick. That just gives me the total for all vendors instead of each specific vendor. I need to be able to have Excel look up the vendor name first then bring back the purchase totals per store under just that vendor. I want it to be able to look them up that way no matter how many rows are below each vendor for each store.

@Tim-B 

I tried with Power Query, but that's only mockup. Not all assumptions regarding Vendor Data are clear.

image.png

 

@Sergei BaklanQuery seems to be bringing back the correct info. I have never used Query and would need to know how to accomplish this with the full file attached. 

@Tim-B 

If you never used Power Query it will take some time to investigate it, but it worth.

First, if you are on Excel 2016 or later, Power Query is built in. Otherwise, for Excel 2010/2013 you shall download and install PowerQuery add-in.

I generated the query on you data, you may check it step by step. From current workbook you may query table or named range, in your case the latest works, named range VendorData is added as

image.png

In brief, we query this named range. Assuming you have no empty rows in between of data, we cut data up to first empty row. After that we keep only columns we need for the summary and remove the rest. With next block we select only vendor names removing vendor numbers form the texts. As result we have 3 columns - vendor, store and sum. We may pivot it them within the query, but since you'd like to have percentage and grand totals it's better to use Excel Pivot Table.

 

Thus we load the query into data model and create Pivot Table based on it. One column is the cost for each vendor by store, another one is % of cost for each column. At the end are grand totals.

 

Perhaps bit more cosmetic is required, but depends on how you'd like to present the result.

 

Please check the file attached.

@Sergei Baklan  What you did actually brought back the right information. The problem I have is, I need to be able to download a new file every week to update the values. This doesn't seem to solve that problem. I really need something that can extract the same data weekly with a new file. Thanks for everything you have done so far! 

@Tim-B 

You may separate data source and report. Source file could be downloaded to some folder locally or to the cloud as often as necessary, you query it from another file and build your report. The only you need is to click Refresh All to obtain fresh report.