SUMIFS and numbers/ text in the same cell

Insert_Key

New Member
Joined
Jun 4, 2019
Messages
3
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

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
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
have a look if you can adopt this

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">0 – No impact</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Technology</td><td style="text-align: right;background-color: #E2EFDA;;">6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">1 - High Impacts</td><td style=";">People</td><td style="text-align: right;;"></td><td style=";">People</td><td style="text-align: right;;">12</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">2 - High Impacts</td><td style=";">Technology</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">3 - High Impacts</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">4 - High Impacts</td><td style=";">Technology</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">5 - High Impacts</td><td style=";">People</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">6 - High Impacts</td><td style=";">People</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">7 - High Impacts</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E1</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">LEFT(<font color="Red">$A$1:$A$8,1</font>)*1,--(<font color="Red">$B$1:$B$8=D1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,489
Office Version
  1. 365
Platform
  1. Windows
Hi, a few other options you can also take a look at.

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">categories</td><td style=";">impact</td><td style="text-align: right;;"></td><td style=";">formula</td><td style="text-align: right;;"></td><td style=";">impact</td><td style=";">score</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Technology</td><td style=";">0 – No impact</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF00;;">13</td><td style="text-align: right;;"></td><td style=";">0 – No impact</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Other</td><td style=";">0 – No impact</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF00;;">13</td><td style="text-align: right;;"></td><td style=";">1 - Extremely Low Impact</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Technology</td><td style=";">3 – Moderate</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF00;;">13</td><td style="text-align: right;;"></td><td style=";">2 - Low impact</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Other</td><td style=";">0 – No impact</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">3 - Moderate impact</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Technology</td><td style=";">4 - High impact</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">4 - High impact</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Other</td><td style=";">0 – No impact</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">5 - Extremely high impact</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Other</td><td style=";">0 – No impact</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Technology</td><td style=";">5 - Extremely high impact</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Other</td><td style=";">0 – No impact</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Other</td><td style=";">0 – No impact</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Other</td><td style=";">0 – No impact</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Technology</td><td style=";">4 - High impact</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Other</td><td style=";">0 – No impact</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Other</td><td style=";">0 – No impact</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">Other</td><td style=";">0 – No impact</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">Technology</td><td style=";">0 – No impact</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">Other</td><td style=";">0 – No impact</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">Other</td><td style=";">0 – No impact</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">Other</td><td style=";">0 – No impact</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style=";">Other</td><td style=";">0 – No impact</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style=";">Technology</td><td style=";">0 – No impact</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">A2:A22="Technology"</font>),--(<font color="Red">NUMBERVALUE(<font color="Green">LEFT(<font color="Purple">B2:B22,1</font>)</font>)>=4</font>),NUMBERVALUE(<font color="Red">LEFT(<font color="Green">B2:B22,1</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">COUNTIFS(<font color="Red">A2:A22,"Technology",B2:B22,{"4 - High Impact","5 - Extremely high impact"}</font>),{4,5}</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D4</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">COUNTIFS(<font color="Red">A2:A22,"Technology",B2:B22,F6:F7</font>),G6:G7</font>)</td></tr></tbody></table></td></tr></table><br />
 

Insert_Key

New Member
Joined
Jun 4, 2019
Messages
3
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. :)
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,489
Office Version
  1. 365
Platform
  1. Windows
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:

Insert_Key

New Member
Joined
Jun 4, 2019
Messages
3
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top