# SUMIFS and numbers/ text in the same cell

#### Insert_Key

##### New Member
Hi there

I would love some help in figuring out how to report subtotals from an existing dataset that has been created by combining values from two separate lists.

List 1 - Category: Adoption, Process, People, Technology, Tools, and Location
List 2 - Impact: 0 - No impact, 1 - Extremely Low Impact, 2 - Low impact, 3 - Moderate impact, 4 - High impact, and 5 - Extremely high impact

My requirement is to report a sum of impact severity ratings:

· per category, e.g. “People”
· over three severity brackets (Low: 0&1, Medium: 2&3, and High: 4&5)

The number listed next to the impact description is its value, e.g 1 x Low Impact has a value of 2, 3 x High Impacts have a value of 12.

The complication that I’m struggling with is that the impact rating contains a mix of text as well as the numerical value, e.g. “0 – No impact”. I have used the formula =NUMBERVALUE(LEFT(Data!B2,1)) to extract the number and report it to a third column.

The formula =SUMIFS(Data!\$C\$2:\$C\$22,Data!\$A\$2:\$A\$22,“Technology”,Data!\$C\$2:\$C\$22,">=4") is providing me the expected value for the Technology category for High and Extremely High Impacts, used in conjunction with the previous formula but I’m desperately trying to find a solution that will do all working/calculating etc. in cell, rather than having additional steps in between the source data and a ‘dashboard’ view of the data.

Is it possible? No VBAs or macros allowed in my workplace.

Any help would be extremely gratefully received!

Andrew

PS: Had some issues posting from work; hopefully haven't posted twice. For reference, a breakdown of my formula is below:

=SUMIFS(Data!\$C\$2:\$C\$22, [SUM range]
Data!\$A\$2:\$A\$22, [Criteria 1 range] Column A – “Categories”
M16, [Criteria 1] Name of Category – e.g. “Technology”
Data!\$C\$2:\$C\$22, [Criteria 2 range] Column C – Numbers extracted from column B
">=4") [Criteria 2] Impact rating of 4 and/ or 5

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### AlanY

##### Well-known Member
have a look if you can adopt this

#### FormR

##### MrExcel MVP
Hi, a few other options you can also take a look at.

#### Insert_Key

##### New Member
Thank you both very much for your responses!

I don't know why, but my work IT environment was making this website hellish to try to use.

Can I ask if you guys are using an any of the tools listed in the FAQs to help present your data? I totally prepped a dummy workbook ahead of typing my post but couldn't manage to share anything useful from work and my head still hurts as a result.

FormR's first two formulas are what I'll run with, I think - have managed to get both to work in my own workbook without any bother, for various ratings and categories, and without use of an additional column. I've never used SUMPRODUCT before, so am keen to learn more about it - and also arrays. I think it's probably this second formula that I'll end up adapting - I like keeping the formula as brief as possible and with this one I can replace the category name with a cell reference and the abbreviate the ratings to the number and a wildcard, e.g. =SUMPRODUCT(COUNTIFS(Data!A2:A22,M28,Data!B2:B22,{"2*","3 *"}),{2,3})

Last edited:

#### Insert_Key

##### New Member
Thanks. I didn't have the option to install anything at work (or to host my file anywhere) so did the best I could to phrase my issues/queries in text. It'll definitely be useful to have something installed at home to assist me in the future though - I'll suss out MrExcel HTML maker for use outside of work next time.

Replies
5
Views
313
Replies
1
Views
102
Replies
1
Views
404
Replies
1
Views
119
Replies
3
Views
69

1,129,310
Messages
5,635,463
Members
416,858
Latest member
yelofneb

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back