•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
HSTACK where second column is based on values from first column
Our take
Are you struggling to create a two-column array with HSTACK in your spreadsheet? You’re not alone. Many users encounter challenges when trying to dynamically sum values based on corresponding entries from another tab. In your case, it seems the formula is only working for the first row, leaving you with incomplete data. By leveraging the LET function, you can streamline your approach. Keep reading for a clearer breakdown of how to adjust your formula for seamless integration, ensuring all rows reflect the correct sums dynamically.
Hi all,
I am trying to use HSTACK to make a two column array. The column A is very straight forward, just copying a column from another tab. The second column however, is giving me problems. I want it to check other tabs and sum the amount of times the corresponding row in column A appears. I can only seem to do this for the first row before it throws a fit.
What I have right now that's not working:
=HSTACK( 'Sheet1'!$B$2:$B$27, SUM(COUNTIF('Other Sheets'!$B$54:$B$73,'Sheet1'!$B$2:$B$27)) ) If I change it to:
=HSTACK( 'Sheet1'!$B$2:$B$27, SUM(COUNTIF('Other Sheets'!$B$54:$B$73,'Sheet1'!$B$2)) ) It prints all of Column A correctly, but only Row 1 of Column B as expected, but that tells me the general idea works.
Thanks in advance for any advice!
EDIT: I had summarized some of the other stuff in the formula to not have to explain it all, realized after some misunderstanding that's not a great idea, here's the actual function that's not working:
=LET( x,'Background Data'!$B$2:$B$27, HSTACK(x, SUM(COUNTIF(INDIRECT("'"&IncludedGames&"'!$B$54:$B$73"),x)) ) ) I have a Name set up for "IncludedGames" to list all sheets to be checked based on a checkbox list. This works perfectly for everything else on the sheet, including referencing it with INDIRECT that way, so that's not the issue, and if I replace the last "x" in the formula with "'Background Data'!$B$2" it will print the correct value, just can't list it.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Anomalous Sorting of COUNTIF ColumnsEDIT: Resolved as I was trimming the worksheet to upload a version with data redacted with replacement text. As I was removing extraneous worksheets, the formula broke with a #REF value. When I fixed them, the problem resolved. Looks like I was actually connected to another set of the same data, but since it as outside of the table, it was creating the anomaly inside of it. Essentially the issue outlined in this blog article, except A) I am not using the unnecessary sheet reference that fixes the problem if it's removed and B) the formula displays correctly: https://excelcharts.com/excel-sort-countif-function-mess-fix-it-how-to/ (Unfortunately, the data I'm working on is proprietary, so I can't share specifics. :( Images are mockups that simulate the results I'm getting.) I have a large table on Sheet1. Column A is a Date column that runs back several years. I would like to count instances in text column B going one year back as of the most recent update and then sort that from largest to smallest, so I can make a simple line graph with the top ten items. Updated iterations of this graph will be used in a routine report, I'm ultimately trying to draft a plug and play tool so my Excel-deficient coworkers can just Copy/Paste into Powerpoint. https://preview.redd.it/ad56jbjp0oog1.png?width=364&format=png&auto=webp&s=8fc6b43dfe338b2c93d3ce7945d5fe9b7e45e0e5 On Sheet2 I create a secondary table that will have 2 columns. The text column A and the Count column B. I have entered each unique Data value from Sheet1 into the Data column in Sheet2. In the Count column I enter the following formula: =COUNTIFS('Sheet1'!A:A,">="&MAX('Sheet1'!A:A)-365,'Sheet1'!A:A,">="&MAX('Sheet1'!A:A),'Sheet1'!B:B,A#) (Where # equal the row) The table on Sheet1 has over 20 columns and I ultimately want to create line graphs for the data in several columns. Since there will be several of these graphs, and the data in Sheet1 is appended with new, up to date data at least once a week, it makes more sense to list the range as 'Sheet1'!A:A rather than 'Sheet1'!A1:A# The formula works just fine. https://preview.redd.it/0gpltacq2oog1.png?width=260&format=png&auto=webp&s=0ba8b74005b82fa9f37813dde8f838af29d49b96 But when I try to sort by Count, it does what's pretty much spelled out in the above linked article, moving the Data cell to the correct row, with the formula adjusting to reflect it's new replacement, but returning the value associated with the Data in the row it was in before the sort. So we see "Item27" in row 28 has the highest count, with 56 occurrences. The formula in row 28 column B, as expected, is: =COUNTIFS('Sheet1'!A:A,">="&MAX('Sheet1'!A:A)-365,'Sheet1'!A:A,">="&MAX('Sheet1'!A:A),'Sheet1'!B:B,A28) After the sort, "Item27" is in row 2 (as expected) but is returning a count of 4, the amount of occurrences "Item17" (now sitting in "Item27"'s old position in row 28) returned before the sort. Of course, "Item17" is returning the wrong count as well. "Item1" was obviously displaced by "Item27" and is in it's expected row 19 but returning a count of 56, which is "Item47"'s actual count. Essentially, each "Item#" is returning a count of whatever "Item#" replaced them in their old position in the table. https://preview.redd.it/aknjet2x5oog1.png?width=249&format=png&auto=webp&s=d069620eed3867df4eabae42532654c089f1cef7 HOWEVER, unlike the example in the article, the formula sitting in B2 next to "Item47" is still correct: =COUNTIFS('Sheet1'!A:A,">="&MAX('Sheet1'!A:A)-365,'Sheet1'!A:A,">="&MAX('Sheet1'!A:A),'Sheet1'!B:B,A2) So.... what's the issue and how do I resolve it? Version: Microsoft Excel for Microsoft 365 MSO (v2508) Environment: Desktop My Knowledge: Intermediate submitted by /u/LikeCCClockwork [link] [comments]
- Is there a way to reference an array of arrays?Is there a way to get Excel to use an array of arrays in in a formula? Instead of writing a formula that references an array and dragging it down through a column, can I write the formula in a way that results in an output of SUM(O2#),SUM(O3#),SUM(O4#),SUM(O5#),...,SUM(O1000#)}? I work with enormous data sets, but they vary in size. Since I need the workbooks to work with any amount of data, I end up dragging formulas down extra far, leaving rows that just output empyy values when the data doesn't reach that far. If I can make the formula reference an array of arrays then I could eliminate all those garbage rows. Thank you. Unfortunately, because a security restrictions, I can't use macros or VBA at all. I need to do it everything through standard excel syntax. EDIT: Sorry, I didn't make it clear that I used SUM() as an example to try and simplify and generalize. Here's a more explicit example of what I'm doing: ID Data Col C Col D A 14 =UNIQUE(ID#) =IF($C1="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C2,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) A 23 =IF($C2="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C2)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) A 42 =IF($C3="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C3,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) B 1 =IF($C4="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C4,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) B 2 =IF($C5="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C5,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) B =IF($C6="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C6,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) B =IF($C7="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C7,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) B 90 =IF($C8="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C8,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) B 94 =IF($C9="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C9,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) =IF($C10="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C10)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) ... =IF($C1000="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C1000,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) Output: ID Data Col C Col D Col E Col F F A 14 A 14 23 42 A 23 B 1 2 90 94 A 42 "" B 1 "" B 2 "" B "" B "" B 90 "" B 94 "" "" ... "" There are a lot of data manipulations omitted, so if something seems weird (like the empty cells in Data Col) please ignore it. The questions is how to eliminate the unneeded rows in Col D. Thank you. EDIT2: Studying the BYROW, SCAN, and MAP suggestions, led to this page on Exceljet, https://exceljet.net/glossary/array-of-arrays , that suggests my question is a known problem in Excel's engine and there is no work-around. Thank you to everyone who tried to help. submitted by /u/TwitchyDingo [link] [comments]
- Getting the sum of multiple row if an identifier is matched?So I have several sheets of data. Sheet1: Identifier https://preview.redd.it/7p7vv2lg4gyg1.png?width=128&format=png&auto=webp&s=7bce4e6ca02e9cb9ea5e3080ade52017af0a179d Sheet2: Find the identifier within the row. I have hundreds of rows of data. If an identifier is in the first 3 values, I want to add the first column after the break, 10. If the identifier is in the second set of 3 values, I want to add the column after that, 100. I want to add a column Sheet1 to contain the sum of that given identifier for my all my data rows. The data in the 6 columns will never have duplicates. https://preview.redd.it/vjdopflt4gyg1.png?width=1336&format=png&auto=webp&s=abf8ad27472f93a56dcd8864f42dcbea7cb6aa7a It would be something like this if I wanted to add a single row, but I want it to add all of my data rows. =IF(COUNTIF(Sheet1!B1:D1, A2) > 0, Sheet1!L1, 0) + IF(COUNTIF(Sheet1!E1:G1, A2) > 0, Sheet1!M1, 0) How do I find a single sum for a given identifier for multiple rows of data where I want to get the sum of a column if that identifier is in a different column? submitted by /u/Confused_Crossroad [link] [comments]
- HSTACK, Autofill, and Skipping rows of dataHi everyone. I had a question about data and manipulating it. I am currently working on collecting data for my thesis and I have created an excel sheet and it is going pretty well. However I am having trouble formatting my sheet. I have a sheet for the original data and a condensed version with the information I care about. This was relatively easy to do. The problem I am running into is the I have 3 sets of data per participant. So I organized it as E_1, E_2, E_3. Basically it is the first set, second set, and third set listed across the columns. The rows are the participants ID number. The problem I am running into is getting the data from the condensed sheet into the organized sheet effectively. I originally was just copying and pasting each row into the other sheet but I am supposed to have 150 participants so doing this for every one would be annoying. So I turned to code to make it less difficult. I was using HSTACK which does make it easier. However when I try to autofill, it goes to the next available cell instead of skipping 3 cells. So it looks kinda like this =HSTACK(A1:C1,A2:C2, A3:C3). Then the next one should be =HSTACK(A4:C4,A5:C5,A6:C6) and so on. It is autofilling it as =HSTACK(A1:C1,A2:C2, A3:C3) then =HSTACK(A2:C2,A3:C3, A4:C4). I don't know how to fix this. I thought that maybe it just needed more examples of the format so I put the HSTACK filled out correctly in rows 1-4. Sometimes it recognizes that it needs to skip 3 but it is inconsistently doing so. And it is like back tracking (like HSTACK(A1:C1, A2:C2) then HSTACK(A2:C2, A3:C3)) instead of just carrying on the pattern. Am I autofilling incorrectly? I am so confused and I am sure there is a way to get it to work but I cannot figure it out on my own submitted by /u/Lower-Duck-5779 [link] [comments]