•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How to sum a conditional formula across multiple cells?
Our take
If you’re looking to sum values from a row of cells that may include text, there’s an elegant solution to streamline your formula. Instead of using multiple IF statements, you can leverage an array formula that processes the entire range at once. This method not only simplifies your formula but also enhances readability. By using a combination of SUM and IF functions within an array formula, you can effectively sum numeric values while ignoring any text, making your spreadsheet more efficient.
Hi, I'm looking for an elegant solution to this problem. Mostly just a curiosity because I currently do have it working, but I know there is a cleaner way to do this. Basically I am trying to sum the number values from a row of 5 cells, where the cell may or may not start with a letter (i.e., the row could be "8, 9, S6.5, 8, A6" and I want the result of 8+9+6.5+8+6=37.5). I have many rows of this, and the summed value ends up in the column to the right of the 5 values.
I've managed to do this with the following formula, where the result is in cell J5:
=IF(ISTEXT(E5), RIGHT(E5, LEN(E5)-1), E5) + IF(ISTEXT(F5), RIGHT(F5, LEN(F5)-1), F5) + IF(ISTEXT(G5), RIGHT(G5, LEN(G5)-1), G5) + IF(ISTEXT(H5), RIGHT(H5, LEN(H5)-1), H5) + IF(ISTEXT(I5), RIGHT(I5, LEN(I5)-1), I5) But, if I recall from years ago, there is a way to make the formula act on an array of cells using ctrl+shift+enter so the formula would essentially look like this, and do the same thing:
={SUM(IF(ISTEXT(E5:I5), RIGHT(E5:I5, LEN(E5:I5)-1), E5:I5))} Thing is, it doesn't seem to cooperate, and I have gotten pretty rusty with excel. This array formula will sum values but not if there is text in the cell.
Example data:
| M | T | W | Th | F | SUM |
|---|---|---|---|---|---|
| T8 | T8 | 9 | 9 | 5 | 39 |
| 9 | 8 | 8 | A8 | 8 | 41 |
| 8 | 8 | 8 | 8 | 8 | 40 |
| S8 | 9.5 | 8 | 8 | 6 | 39.5 |
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Index Match or Xlookup to return sum of row values based on matching two horizontal lookups?Ask: If E59:S59=E111 AND E60:s:60<-E112, give me the sum of E61:71,else return 0 What is the smoothest formula for this? I added in what I'm trying to do on the formula line. ChatGPT is less helpful with this than I am. I want to bundle by phase because our timesheet system doesn't allow me to pull a report by phase/task, only by person. I tried Xlookup but kept getting SPILL errors so I am at a loss. FTE1 is a different category and should not be included (PM, I can plug that as it's always in the same position). Thanks in advance for your help. https://preview.redd.it/yp4yzfn2rcxg1.png?width=2560&format=png&auto=webp&s=86eed1d2c8f3bb97c6d49592a0b1978d5f80f365 submitted by /u/Regular_or_BQ [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 every other row in a table?Hello everyone, I don’t use Excel very often, so I don’t have much experience with formulas, but i have a basic understanding. I need to sum every other row in a table. Previously, this was done using the following formula: =SUM(F31:I31+F33:I33+F35:I35+F37:I37+F39: I39+F41:I41+F43:I43+F45:I45+F49:I49+F51:I51) (this isn’t the whole formula—it continues like this through row 150) There has to be a better way to sum rows like this. I use the Newest Excel Version from M365 submitted by /u/CorrectTechnician473 [link] [comments]
- 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]
Tagged with
#AI formula generation techniques#formula generator#Excel alternatives for data analysis#financial modeling with spreadsheets#rows.com#generative AI for data analysis#Excel compatibility#row zero#no-code spreadsheet solutions#Excel alternatives#natural language processing for spreadsheets#big data management in spreadsheets#conversational data analysis#real-time data collaboration#intelligent data visualization#data visualization tools#enterprise data management#big data performance#data analysis tools#data cleaning solutions