SUMIFS and numbers/ text in the same cell

[Insert_Key]

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

 

[AlanY]

have a look if you can adopt this

Book1
	A	B	C	D	E
1	0 No impact			Technology	6
2	1 - High Impacts	People		People	12
3	2 - High Impacts	Technology
4	3 - High Impacts
5	4 - High Impacts	Technology
6	5 - High Impacts	People
7	6 - High Impacts	People
8	7 - High Impacts
Sheet4

Cell Formulas
Range	Formula
E1	=SUMPRODUCT(LEFT($A$1:$A$8,1)*1,--($B$1:$B$8=D1))

 

[FormR]

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

Excel 2013/2016
	A	B	C	D	E	F	G
1	categories	impact		formula		impact	score
2	Technology	0 No impact		13		0 No impact	0
3	Other	0 No impact		13		1 - Extremely Low Impact	1
4	Technology	3 Moderate		13		2 - Low impact	2
5	Other	0 No impact				3 - Moderate impact	3
6	Technology	4 - High impact				4 - High impact	4
7	Other	0 No impact				5 - Extremely high impact	5
8	Other	0 No impact
9	Technology	5 - Extremely high impact
10	Other	0 No impact
11	Other	0 No impact
12	Other	0 No impact
13	Technology	4 - High impact
14	Other	0 No impact
15	Other	0 No impact
16	Other	0 No impact
17	Technology	0 No impact
18	Other	0 No impact
19	Other	0 No impact
20	Other	0 No impact
21	Other	0 No impact
22	Technology	0 No impact
Sheet1

Cell Formulas
Range	Formula
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)

 

[Insert_Key]

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

Thanks again for your help - I consider my question answered.

 

[FormR]

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.

 

[Insert_Key]

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.