Search results

Thanks, but the problem was not that I had changed the data. The answers that I was getting when I was trying to apply this formula to a much larger data sets (over 400 rows and 70 columns) were wrong, with static data. The data is not going to change because I am dealing with historical data...

Thank you! Unfortunately, even with absolute references, the formulas don't work properly with the large set of data, returning values that have 0's in the original data sheet

Thanks! I have 365 both at home and at work. Unfortunately, the formula didn't work with the large set of data. Do you think I can have column references, i.e., instead of Data!$B$2:$J$9, Data!$B:$J ;and instead of Data!$A$2:$A$9, just Data!$A:$A? Also, in the first part of the example, the...

Also, is it possible to reference the entire columns because my actual data sets includes hundreds of rows and dozens of columns? Thanks again!

Hi Naresh, Once again, thanks for such a fast and efficient reply. Just one quick question (again, sorry - my apologies, I haven't used the AGGREGATE function in years), but when you get a chance, could you please explain the role of a slash "/" in the formula? Thank you!

Thank you!

Hi Naresh, It certainly does - thank you very much!

Hello, I have a list of participating stores and products that the company carries (please see attached - it's highlighted in yellow). Not all stores have all products available. What I need to get is a list of available products for each store displayed next to the name of the store (no lbs)...

Thank you all for your input, it's greatly appreciated! I will have to go with SUMPRODUCT. I am well versed in Pivot tables (and love those), but the trick was to create something that was not dynamic and could not be refreshed/updated by other users. SUMPRODUCT should work for this example...

I understand that INDEX/MATCH might not be ideal as it's looking for the first column that matches the description. The company frowns upon using macros, so apart from that, what formulas can I use to cover all the columns in the range?

Hi guys, got stuck with the INDEX/MATCH functions, getting an incorrect result. Need to get a summary by product type by region in the example below (combined number of units of fruit and vegetables for both regions). When I added =SUMIF($B:$B,"Fruit",((INDEX($C:$H,,MATCH($N2,$C$1:$H$1,0)))))...

Hello... Here is a very condensed version of what I am trying to accomplish here. Several workers are assigned to several ongoing projects; their time is fully allocated in cells B2 through G4. 1) How I give their manager a summary similar to what I have in cells B12 through D14 without...

Does it work with absolute column references in 365? Just tried it in my real-life example and got the #VALUE error again :(

Wonderful - thank you very much!!!!!

Hello, The attached spreadsheet contains 2 tabs. Sheet1 is the original example where we got the summary of sales by region by distribution channel. Part 2 (red) is the same example, but with an additional criterion. I had to separate products by Type (Meat and Produce) and get the answer for...

Thanks again for posting your reply!!! Any idea how to add another element? Thank you!!!!

How about another iteration - what if we calculate it for Meat only (see Part 2), the red tab

It worked - thank you very much!!! Forgot all about good ol' SUMPRODUCT :)

Hello, Please see the attached example. The yellow part of the data section shows percentage allocation of each product by region. The green section shows the allocation of the same product by distribution channel. Now I need to "marry" the two and get the sum by region by channel (at this...

Yes, thank you very much - greatly appreciated. Very creative and easy on the eye :)

Thanks for your input, but I am looking for adjacent stacked columns, 2 per region, split between 2 different product types

Forgot to mention - I am not supposed to use macros or VB script as it will be on the shared drive

Hello, Here is the simplified version of a chart that I have to create in Excel. There is a sales chart that shows breakdown for 2020 by region, stacked by product type. Now I need to add a column for 2019 for each region, broken down in the same fashion (by product). I highlighted the columns...

In my original post I forgot to mention that the order in which locations appear is not static. My example includes 4 sites. The real-life situation includes over 300 rows that should be sorted, filtered, and manipulated the way the users see fit. Thanks!

Thanks! Unfortunately, I don't have this option at work. I also need to have the ability to shuffle the order of regions so it is not contingent on how it is sorted on the data tab, so I can't assume that the order will remain the same.