•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
is there a way to modify formulas based on inputs?
Our take
If you’re looking to modify formulas based on dynamic inputs, there’s a way to streamline your experience. Instead of manually adjusting your SUM formula each time you want to change the number of rows, you can use a cell reference to dictate how many rows to sum. For example, if you input the desired number of rows in a specific cell, you can then incorporate that cell into your formula using the INDIRECT function.
let's say i want to sum 4 rows in one column. so i'd type something like =SUM(A1:D1)
but what if i want to suddenly sum 10 rows? i'd have to go in and re-type the formula to =SUM(A1:J1) or drag that box to sum more rows.
i'm wondering if there's a way i could type in a number in a cell, and a formula could use that to determine how many rows to select.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Increment row but not column when copying a formula?Trying to fix an old spreadsheet at work. The current formula is this: =SUM(R6,R16,R26,R36,R46,R56) I need to build 4 more columns to the right of the existing one with the same formula, that increments the row but leaves the column the same, like this: =SUM(R7,R17,R27,R37,R47,R57) I don't think I can use $ for this as that always locks the column right? Photo Example below. I need to add three more Flav columns on the right hand side, pulling from lists like on the left hand side. https://preview.redd.it/v9iyxye9xrwg1.png?width=604&format=png&auto=webp&s=c0ad4b7502a0ec344221e29965de8dda99ed045e submitted by /u/fullofpaint [link] [comments]
- Is there a way to Sumifs up to a certain column with multiple criteria?Hi everyone, I’m looking for a formula to sum all of the columns starting from a particular column up to a particular column. How do I best do that? On the data tab, Row 1 I have the following columns: A1 = date B1 = serial number C1 = customer name D1 = date 1, with the data being $ E1 = date 2, with the data being $ F1 = date 3, with the data being $ In a separate tab I have the same thing mirroring the data tab, but without the $, for the calculation what I intend to do is on E2, I want to say if $A2 = E$ 1, then sum all of D up to E column on the data tab provided that the value on B column of the data tab match B2 of the calc tab, and C column of the data tab match C2 of the calc tab. For F2, it would be the same thing, but sum column D up to column F instead of column E. I hope it makes sense. Thanks for your help! submitted by /u/botng [link] [comments]
- How to sum all cells in column B where column A in the same row contains a specific string of text?I have a spreadsheet with names in column A and numerical responses in columns B, C, etc. I want to make it so that if, in a given row, column A contains a specific name (say, "Bob"), then the numerical value in column B in that same row will be added. Basically, I want the sum of all the rows in column b for which column a contains "Bob". I can't just sort by column a because sometimes it will contain multiple names per cell (ie Susan, Bob), and I need the equivalent numerical value to be added to both Bob and Susan's total. I've tried google searching this but I can't figure out how to specify same row but different column. I know I could probably do this with what I know already using just like... a lengthy chain of conditionals, but last time I tried that I ended up with an excel file that crashes my computer every time I try to open it, so I was hoping there's a better option. This is Version 16.85 on a Mac btw. submitted by /u/Odd_Nectarine6622 [link] [comments]
- Excel formula automatically rewriting itself??Hi all, this is a really odd problem to run into and I am unsure how to proceed from here. I am currently entering data and I have Excel performing an =SUM(B5:AC5) for my data that is being entered. Because the data entry can go pretty fast sometimes, I would keep entering data past AC5 before I realize what happened. When I look at my SUM column, Excel has flagged my =SUM(B5:AC5) formula with the error below, which is fine and whatever, but it is automatically changing my =SUM(B5:AC5) formula to =SUM(B5:AF5) or something of similar depending on what column I stopped at. Just for reference, data from AD5 to AO5 is being summed up in another column with SUM=(AD5:AO5). Formula error being flagged by Excel I have no idea why it is doing this and I tried to ignore error, but it still automatically rewriting my formula anyways! This worksheet and formula worked smoothly 4 months ago so I don't know what is going on. submitted by /u/WildKhanine [link] [comments]
Tagged with
#rows.com#AI formula generation techniques#formula generator#Excel compatibility#Excel alternatives for data analysis#Excel alternatives#cloud-based spreadsheet applications#formulas#SUM#rows#cell#columns#inputs#modify#number#automation#selection#calculate#function#dynamic