SUMIFS and numbers/ text in the same cell

Insert_Key

New Member
Joined
Jun 4, 2019
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
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! :)

Thanks in advance,

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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
have a look if you can adopt this


Book1
ABCDE
10 No impactTechnology6
21 - High ImpactsPeoplePeople12
32 - High ImpactsTechnology
43 - High Impacts
54 - High ImpactsTechnology
65 - High ImpactsPeople
76 - High ImpactsPeople
87 - High Impacts
Sheet4
Cell Formulas
RangeFormula
E1=SUMPRODUCT(LEFT($A$1:$A$8,1)*1,--($B$1:$B$8=D1))
 
Upvote 0
Hi, a few other options you can also take a look at.


Excel 2013/2016
ABCDEFG
1categoriesimpactformulaimpactscore
2Technology0 No impact130 No impact0
3Other0 No impact131 - Extremely Low Impact1
4Technology3 Moderate132 - Low impact2
5Other0 No impact3 - Moderate impact3
6Technology4 - High impact4 - High impact4
7Other0 No impact5 - Extremely high impact5
8Other0 No impact
9Technology5 - Extremely high impact
10Other0 No impact
11Other0 No impact
12Other0 No impact
13Technology4 - High impact
14Other0 No impact
15Other0 No impact
16Other0 No impact
17Technology0 No impact
18Other0 No impact
19Other0 No impact
20Other0 No impact
21Other0 No impact
22Technology0 No impact
Sheet1
Cell Formulas
RangeFormula
D2=SUMPRODUCT(--(A2:A22="Technology"),--(NUMBERVALUE(LEFT(B2:B22,1))>=4),NUMBERVALUE(LEFT(B2:B22,1)))
D3=SUMPRODUCT(COUNTIFS(A2:A22,"Technology",B2:B22,{"4 - High Impact","5 - Extremely high impact"}),{4,5})
D4=SUMPRODUCT(COUNTIFS(A2:A22,"Technology",B2:B22,F6:F7),G6:G7)
 
Upvote 0
Solution
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. :eek:

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})

Thanks again for your help - I consider my question answered. :)
 
Upvote 0
Can I ask if you guys are using an any of the tools listed in the FAQs to help present your data?

I'm using the MrExcel HTML maker - additional notes on installing it can be found in post 3 here:
https://www.mrexcel.com/forum/about-board/508133-attachments.html

=SUMPRODUCT(COUNTIFS(Data!A2:A22,M28,Data!B2:B22,{"2*","3 *"}),{2,3})

That's good :) I think the COUNTIFS() is probably going to be the more efficient of the options.
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top