SUMIF

[Nanaia]

How do you write the formula to sum the row if the cell contains a specific letter then sum the number beside it? I would sum each row independently. If I need to I can have the letters put before the numbers or put a space between them. I'm stuck on how to put the formula together. I can get it to total the numbers but it looks at all numbers regardless of the letter designation by using [ =SUM(IF(ISNUMBER(LEFT(B11:Z11,1)+0),LEFT(B11:Z11,1)+0)) ]. How do I get it to also look at the letter and only count the numbers next to the letters I designate?

If it helps, this is for tracking vacation, sick days and other HR related days missed. They want one calendar that contains all info. Names are in column A and days are column headers with a column at the end of the month that totals how many of each there are.

In the following example it would show that there the sum of the cells with S in them is 16.
Example:

4EU

8S

8F

5F

8V

8S

 

[GlennUK]

This sums the S cells, and must be entered using Ctrl+Shift+Enter instead of Enter:

=SUM(IF(NOT(ISERROR(SEARCH("S",A1:I1))),VALUE(SUBSTITUTE(A1:I1,"S","")),""))

 

[Robin1968]

This sums the S cells, and must be entered using Ctrl+Shift+Enter instead of Enter:

=SUM(IF(NOT(ISERROR(SEARCH("S",A1:I1))),VALUE(SUBSTITUTE(A1:I1,"S","")),""))

Found this thread yesterday. Nanaia and I have the same problem. Thank you Nanaia for posting.

I inherited a spread sheet to track vacation, sick and family emergency days. I am trying to put some consistency into the spread sheet. I have most of the formulas I need in place, all I have left is to sum the various days they take off. I have tried the array you suggested and I can't get it to work. When I open up the formula, I can see it is picking up the number value for "S" but it is getting hung up on the section starting with Value. It is something simple but I can't see it.

this is what I wrote.

This is the section of the worksheet it is looking at.

This is what I am seeing when I open it up. I haven't had to do anything like this for a very long time. I am hoping you can help. Thank you. Robin

 

[Fluff]

The problem is that some of your cells do not have a number before the S.

 

[Robin1968]

The problem is that some of your cells do not have a number before the S.

Thanks for your quick response. The cells with just the "S" represented full days. I have changed them Sick but still no luck. im stumped.

 

[Fluff]

To use that formula you will need to put a number into the cell along the the S.
If you do not want to do that, I would suggest you start a thread of your own, explaining what you want & include some sample data using the XL2BB add-in.

 

[Robin1968]

Thanks. I will.

 

[Fluff]