SUMIFS Formula for Column with Various Possible Returns
Hi there! I am a big Excel user switching to Smartsheet and need help with a SUMIFS formula.
In Excel, the formula was:
=SUM(SUMIFS('Outside Counsel Invoices'!$G:$G,'Outside Counsel Invoices'!$M:$M,"FY23",'Outside Counsel Invoices'!$D:$D,{"=7607","=7607*"}))
Since Wildcards (*) are not used in Smartsheet, I am struggling with creating a SUMIFS formula that looks for multiple variations of a number in a column. In the example above, I want the formula to find any variation of 7607 in that column. By variations, sometimes the number will be 7607 or 7607-1 or 7607-2, ect.
Any help you can give is greatly appreciated. Thanks!
Best Answer
-
Paul Newcome ✭✭✭✭✭✭
It looks like you have a mix of text strings and numerical values. Try the below. If that doesn't work then we do have one more option.
SUMIFS({Range To Sum}, {Range To Evaluate}, OR(@cell = 7607, CONTAINS("7607", @cell)))
thinkspi.com
Answers
-
Paul Newcome ✭✭✭✭✭✭
You would need something along these lines:
=SUMIFS({Range To Sum}, {Range To Evaluate}, CONTAINS("7607", @cell))
thinkspi.com
-
Thank you Paul for your suggestion. When I added the CONTAINS("7607", @cell) to my formula, it only picked up the totals for any amount with a dash after 7607; example 7607- ~ it didn't pick up the totals for those with 7607; meaning without a dash.
Maybe to better understand, let me provide a little different info. Below is an example of my table in Smartsheet. What I would like to happen is to collect the total amount for every "row" where the account number is 7607 is listed. This includes 7607 AND 7607-2 AND 7607-5.
Account # Amount
7607 $100.00
7632 $200.00
7607-2 $50.00
8016 $5,000.00
7607-5 $500.00
Hopefully, this makes better sense. Again, any help with this request is greatly appreciated.
-
Paul Newcome ✭✭✭✭✭✭
It looks like you have a mix of text strings and numerical values. Try the below. If that doesn't work then we do have one more option.
SUMIFS({Range To Sum}, {Range To Evaluate}, OR(@cell = 7607, CONTAINS("7607", @cell)))
thinkspi.com
-
Paul, that worked. Thank you so much for your help ~ I was feeling defeated.
Have a great rest of your day!
-
Paul Newcome ✭✭✭✭✭✭
Help Article Resources
Categories
I guess you forget to use AND( ).<\/p>
For example, <\/p>
- IF([Total Value to User Score]@row = <3.8, >4.7, \"Mild,<\/li><\/ul>
should be<\/p>
- IF(AND(<\/strong>[Total Value to User Score]@row < 3.8, [Total Value to User Score]@row > 4.7)<\/strong>, \"Mild\",<\/li><\/ul>
However, the following would be more straightforward.<\/p>
- =IF([Total Value to User Score]@row >= 4.8, \"No Pain\", <\/li>
- IF([Total Value to User Score]@row >= 3.8, \"Mild\", <\/li>
- IF([Total Value to User Score]@row >= 2.8, \"Moderate\", <\/li>
- IF([Total Value to User Score]@row >= 1.8, \"Very Severe\", <\/li>
- IF([Total Value to User Score]@row < 1.8, \"Extreme\", \"//www.santa-greenland.com/community/discussion/106771/\")))))<\/li><\/ul>
- IF(AND(<\/strong>[Total Value to User Score]@row < 3.8, [Total Value to User Score]@row > 4.7)<\/strong>, \"Mild\",<\/li><\/ul>