Search results

Fixed it - one of the columns didn't have the right formats. Thanks again!

Thank you! For some reason, it didn't work in my main file. I tried checking the MAXIFS formula, and for some reason, it always returns 0. I am using Office 365. Are there any add-ins that I need to activate? I am familiar with the MAXIFS function and used it before. Do I have to use column...

Thanks! My last question is if I could use the absolute column reference, e.g., $A:$A. Thanks again!

Thank you!

Thank you! Here is the updated file. No, we are talking about a file with thousands of rows with multiple customers with monthly purchases over the period of almost 2 years. The file is currently not sorted.

Hello, I am working on an Excel project where I get rows and rows of data and need to identify the most recent invoice for a given customer. I am looking for a suggestion as to how to get that "latest" indicator out there without writing a macro if possible (this is a shared file, and macros are...

Thank you! Yes, I messed up my example, it should've been A1

Thank you, but it still didn't work - the error now is #VALUE. I haven't used the FILTER function in a while, but why did you put the minus sign before LEFT? Thanks again!

And on my actual file, I got #N/A

Thanks, but it gave me an error: CALC (see attached)

Here you go. Thanks

Hello, I know that there is an error and it's no the right way to do it, but how do I incorporate the text value function into my SUMIFS formula? I need to get a sum of the numbers if the first 2 characters of a cost center are numeric vs letters. Here is what I got so far (I know it's not...

Hello, I need to sum the values when the first characters are numeric: =SUMIFS('Data Tab'!C:C,'Data Tab'!A:A,$A2, 'Data Tab'!B:B,

It worked - thank you so much!

Hello... I am trying to add multiple columns through VLOOKUP to get the sum for the entire year rather than adding up 12 lookups for 12 months, doing something like this: , =VLOOKUP($B$6,'Data by Dept'!$A:$BU,{38,39,40,41,42,43,44,45,46,47,48,49},FALSE) I am getting the #SPILL! error. Does...

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?