•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How to cross reference data in multiple columns then add together data in a specific column?
Our take
Cross-referencing data across multiple columns can significantly enhance your spreadsheet analysis, especially when seeking to calculate averages based on specific criteria. In your case, you're looking to identify rows containing a certain word, then retrieve and sum the corresponding values from another column. While COUNTIF is effective for single-column counts, functions like XLOOKUP, INDEX/MATCH, or VLOOKUP can facilitate more complex queries. This approach will empower you to derive an average time for case assignments, streamlining your data management process.
I have a table full of cases, who they belong too, and how long it takes to get it assigned basically. I am trying to find a formula to check a column for a certain word then follow that row to another column and find the number notated. Then to add all numbers it found together to give me an average. My goal is to find an average number it takes for certain cases to get assigned.
I have been using countif for most of what I need as it only pulls from one column then tells me to the total of times something appears. The cross-referencing is out of my wheelhouse. I've seen recommendations xlookup/index/match/vlookup but not sure which is best or if they will meet the need.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Matching Numeric Data across two columns?What I thought would be a simple formula has taken me over 2 hours of searching and no results as yet.. So im reaching out for help please. Heres where Im at. - Using 16.108.2 version - I have 2 sets of data of differing row amounts. There is one numeric column in each set that definitely has matching numbers across them. I need to see which numbers are matching and which numbers aren't. - I need to align those 2 columns where the numbers are matching, and also keep all other information aligned across the columns that are alongside the 2 I am matching. I have tried the XLOOKUP formulas that I can see listed when i do a google search, but i cant get it to work, either some information or step is being left out, or I am finding the wrong info on how to do this. Can anyone provide a step by step on this please? submitted by /u/Sentallmentall [link] [comments]
- Two workbooks, two columns in second book could include matching data, neatest most efficient way to query between them on multiple fields using excel formula only, no VBA / Power Query etc?Not even sure how to surmise this but here we go: Workbook 1, column i has a reference code, "EntID" which could be a number OR text. Workbook 2, that reference code might be found in Column A, or Column B, or neither. In Workbook 1, I need 5 lookup columns to pull data from 5 corresponding columns in Workbook 2 where there is a match for " EntID" in EITHER column A or B. If there is no match then the cell should state "Not Found". I have it working by using nested if statements, isnumber & xmatch for the first lookup column to ascertain where the match is found and then xlookups on remaining columns depending on the result returned to that cell but the formula are huge if for no other reason than the file naming and locations (which cannot be changed) and it feels slow, clunky and unrefined. How would you do it? submitted by /u/Delinquent90 [link] [comments]
- I am averaging multiple rows and not all rows have data. I want to apply that result to all rows. Picture attached.Callouts need to be calculated with the formula I have in G2 for each set of data in the data column to its respective callout. Simple Volume Weighting. I need a formula for G where I can drag it down and it look like what I have in Column H. In short, I need Column G to look like how I populated H based on a formula. I am hung up on the order as in there are values low in the rows that need to be applied for the whole callout. I’m over thinking it. Solutions?? https://imgur.com/a/Gcz4Dd7 submitted by /u/networkfound [link] [comments]
- Cross checking data from two different systems but they export in different formats - how to marry them up?I have a table with two sets of data exported from two different systems that I need to reconcile - names and values. Trouble is they export in different formats so I'm needing partial look-up? The lists are also not exact duplicates so there are names on one that don't appear on the other. Once the names have been matched up I then need to cross check the value to isolate any variances. So two formulas for two columns. thanks submitted by /u/pandoras_picnic [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#natural language processing for spreadsheets#rows.com#AI formula generation techniques#big data management in spreadsheets#conversational data analysis#Excel compatibility#row zero#real-time data collaboration#intelligent data visualization#data visualization tools#formula generator#enterprise data management#big data performance#Excel alternatives#data analysis tools#data cleaning solutions#cross reference#data