Sum number in cell with specific text

[semperduro]

I have several columns I want to sum involving a specific text string.

In E11 I want to sum "1-RAD" (the number only) when entered into H15:H62.

So if "1-RAD" is entered into H15:H62 once and "3-RAD" is entered into H15:H65 a few rows later, I want E11 to sum "4".

The VBA for it is: varTotRadar = varTotRadar + NumericOnly(varRptToSplit(0))

However, I want a formula. Having issues with VBA populating after password protecting sheet.

I may not be asking my question correctly since I haven't found anything on it. I stumbled across something the other day, but was not able to save it and have forgotten where I found what I think I'm looking for.

Thanks in advance.

 

[Rick Rothstein]

Let's see if we can "tighten up" your requirements a little. Is the following correct? In H15:H62 you have values that start with a number, is followed by a dash, and ends with some text. You want to sum the numbers for those cells that end in the same specified text. So, your cells could have 1-RAD, 3-OKAY, 5-RAD, 2-RAD, 9-GOOD and you want to put the text RAD in some cell (which one?) and have the formula return 8 (the sum of the number with RAD at the end... is that correct?

 

[semperduro]

Yes, there is a number followed by a dash and then the abbreviation of RAD (short for radar). I want to be able to count the number only.

If H16 has "1-RAD", H22 has "1-RAD" and H23 has "3-RAD" then E11 should count only the numbers totaling 5. Otherwise, E11 will be a Zero, which I was un-check under the options tab so that all cells with a Zero in them will show as a blank. For what I'm doing a blank cell will look "cleaner" than one with a zero in it.

I will also have truant (TRU) and curfew (CUR) to count in H15:H62, but they will be totaled up in other cells.

 

[Rick Rothstein]

Formulas are not my "thing" (I am mainly a VBA person), so this may not be the most efficient formula, but it works, so you can use it until someone posts a better one.

This formula is an array-entered formula meaning you commit it using CTRL+SHIFT+ENTER and not just Enter by itself.

=SUM((MID(H15:H62,FIND("-",H15:H62&"-")+1,99)="RAD")*IFERROR(0+LEFT(H15:H62,FIND("-",H15:H62)-1),0))

 

[semperduro]

Thanks, I don't understand VBA and I half way understand formulas. I was just running into a protection issue with the VBA when I protected the worksheet before. It mostly had VBA, but a few of the "simple" tasks were completed with formulas. The formulas would work once locked down, but the VBA would not. I'm trying to learn as I go. I appreciate your patience.

 

[Rick Rothstein]

With VBA, you need to have the code unprotect the worksheet before you do anything on it, then put the protection back on afterwards. Look up the Unprotect and Protect methods of the worksheet object in the help files to get an idea how to use them.

 

[Tom.Jones]

Try this:

In E11 =SUMPRODUCT(IFERROR(LEFT(H15:H65,FIND("-RAD",H15:H65)-1)*1,0))
Then change in formula "-RAD" with "-CUR" .....

 

[semperduro]

Thank you, that one works, also.

 

[Rick Rothstein]

Not only does Tom's formula work, but it is much better than the one I posted... definitely use it.

 

[Peter_SSs]

Thank you, that one works, also.

I'm not suggesting that it does not work, but I don't think that you will need SUMPRODUCT, try just SUM

=SUM(IFERROR(LEFT(H15:H65,FIND("-RAD",H15:H65)-1)*1,0))