•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How to count how many columns two rows have in common
Our take
Counting how many columns two rows share can be a daunting task, especially when juggling multiple functions like INDEX, LOOKUP, and COUNTIF. In this guide, we’ll simplify the process for counting how often character A aligns with character D across your dataset. By focusing on identifying common occurrences within your chart, you can streamline your analysis. Moreover, we’ll explore how to account for variations in cell values, allowing you to capture both "1" and "1*" seamlessly.
Hello, I'm a hobbyist driving myself insane hopping between index, lookups, and countif functions trying to count:
How many times does character A feature with character B across multiple events
This is a slice of the whole chart, I'll call the characters A-D in descending order.
For example, say I want to count how many times character A and D share a column. In this slice of the chart, I'd want for the function to return 1.
In total there are 32 columns, and I want to be able to compare one character row with 21 other rows.
I suspect I'll need to change the cells that read "1*" to simplify the matter, but if there's a way to count for both "1" and "1*" cells I'd be very thankful.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Help counting number of columns containing values greater than zeroHello! I have a table where I'm trying to count the number of columns containing values greater than zero, but only counting once per column and I'm struggling with the formula. The cells D12:O12 could potentially contain any value from zero or above. If only D12 contained a value this would count as 1, if D12 and E12 contained values this would count as 2, and if D12, D13 and E12 contained values this would also count as 2 as I'm only interested if the column contains data or not. Any help anyone can provide would be greatly appreciated! https://preview.redd.it/vyghp9drsokg1.png?width=1535&format=png&auto=webp&s=71597563196beb29ad08d6fdbbbfb9d736db374f submitted by /u/Brooksolicious [link] [comments]
- How do I count the amount of cells which has a number in it in two columns but not count it twice?For example, count it if it has a number in A1 but not count it again if there is a number in B1? Hope that makes sense. submitted by /u/Ok-Strategy6708 [link] [comments]
- COUNTIF counts cells with Exact Matches; I need it to be less Exact & count cells that "contain" but don't match exactly as wellI've got a spreadsheet - large, 336 rows & 29 columns - myself and a few friends have been amassing of various original characters, and that means that there's both a lot of data, but also the cells have to be a little inconsistent in order to cut down on the amount of columns - plus also capture the variability of human nature. For instance, our "Place of Origin" column lists place by City, Country/State as necessary, and that means that two characters from Ireland might have "Dublin, Ireland" and "Belfast, Ireland" as the listed data, so the cells wouldn't be exact matches. This is readable by the eyes, but we've gotten to a point where trying to make anything meaningful of the data with a visual scan just isn't feasible, and I'm also a novice with Excel and historically bad with numbers - so I also decided I wanted to use the data we have to brute force my way through figuring out how to actually collect the points into usable numbers and display them. (I state my experience with this so that, when I have inevitably gone around my elbow to get to my thumb and there's an easier way to get what I want, y'all get why I did what I did lol). However, the inconsistency of the data is where I'm getting tripped up. I've been using COUNTIF (=COUNTIF(RelevantSheet!F1:F336, "City"), for example), and it's working for what I want to do - characters of a certain class (not social), counted. Characters of a city, counted. Brilliant, I'm getting numbers! Except, it's throwing inaccurate numbers, as I've realized it's only counting Exact Matches. If I'm trying to, say, count the number of characters we have Originally From Ireland, and I use COUNTIF and input Ireland, it's only counting the cells that contain exactly "Ireland", and not cells that contain "Dublin, Ireland" or "Belfast, Ireland" since it's not an exact match. I don't want to exclude those, however, because logically, they're all still from Ireland. Is there a way to tally cells that "contain" instead of exact matches? My goal is a formula where I can search "Ireland" under our "Origin" column and return the number of characters that are from Anywhere At All In Ireland, without having to break the column up into "City" and "Country" separately. There's several different String values I want to try this for, but it's all the same idea - searching and counting part of the cell, but not necessarily all of the cell. Things to note: The way the data is filled in is as consistent as we can make it, but with 300+ rows, there's still a lot of variance. While I could probably do separate COUNTIF searches and then add the results (and that may be what I have to do), I'm hesitant to do that until all possible options are looked at for fear of missing something. Being human, I know I'm gonna miss a single variant label somewhere, so the less I have to rely on my own eyes to search and collate, the better lol. If I must, I must, but that's the last bridge I'll cross regardless. I am also tallying the numbers in a separate Sheet within the whole document (thus why the example had "RelevantSheet!"), since I don't want to muddy the main data set, especially considering we do continue to add columns and rows. I have successfully made this work - yay, small victories - but I know that can also bungle the method of solution as well. submitted by /u/crazythatcounts [link] [comments]
- For some reason my COUNTA function is returning one too many results (also how to quickly fill many cells with sequential numbers?)https://preview.redd.it/1hz8k8yc75kg1.png?width=689&format=png&auto=webp&s=560395ff19be6c9f7d617fd57702f49fbdbd9939 I have COUNTA to read from A2, to 502, since that goes up the the '500' that I need. However, for some reason, the function is returning 501. The first cell it's reading, a2, starts at 0 (labeled with the number zero), then up to cell 502, which is labeled 500. Also, for desktop/free use, is there a faster way to do sequential numbering. I had a tough time doing the =sequence function, and then I don't have access to some of the other fill tools, and since I'm just learning, I don't have the money for a subscription right now. submitted by /u/Kieotyee [link] [comments]