+ Reply to Thread
Results 1 to 8 of 8

Only COUNT or SUM value in a row when triggered by a specific letter.

  1. #1
    Registered User
    Join Date
    10-15-2012
    Location
    az
    MS-Off Ver
    Excel 2010
    Posts
    4

    Only COUNT or SUM value in a row when triggered by a specific letter.

    Hello,

    I am trying to get a row of data to only total when a specific letter is typed in that row. I have attached two documents (EXCEL sheet that's in question/ WORD doc detailing my problem or lack of knowledge).

    My EXCEL knowledge is ZERO and this is my first shot (attached) at working with it. Please excuse my lack of knowledge when viewing my workbook as I am sure there is many things wrong. Sorry if I posted this incorrectly (title/body/attachments).

    My name is Alex and I am new here but have been reading the site for the last few days and its great and has helped me greatly!Flow Chart.xlsxProblem Example.docx

    Thanks for any help you may provide!
    Last edited by azalex; 10-16-2012 at 09:45 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: Only COUNT or SUM value in a row when triggered by a specific letter.

    Alex,

    The spreadsheet seems to be doing exactly what the Word doc is saying.

    Simply type in letters in the all the formulas are there.

    Please give an example of what you want/expect to see...

    Thanks,
    Dennis

  3. #3
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,841

    Re: Only COUNT or SUM value in a row when triggered by a specific letter.

    Put this formula in cell BA5 and copy down:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    - Vince

  4. #4
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,841

    Re: Only COUNT or SUM value in a row when triggered by a specific letter.

    The above formula will check to see how many times the letter "P" shows up in range N5:AR5. If it doesn't show up, BA5=0, otherwise, if "P" is in that range, it then searches for ANY entries in cells N5:AR5 and adds it to the number in L5 (in your example, that would be 20). At the end, it subtracts how many times it counts the letter "P" in that range, otherwise your results won't be correct.

    If you DO want to count when the letter "P" appears in the range and add that to the total, then just remove the " -(COUNTIF($N5:$AR5,"P")) " from the end of the function. You'll still need that third closing parentheses - so don't delete it.

    Note: The "*" will work, unless you plan to have multiple items from the legend in one cell. If that is the case, then adding each instance of each Legend Item will be necessary using a more complex formula.

    Hope this is what you were looking to do.

    - Vince

  5. #5
    Registered User
    Join Date
    10-15-2012
    Location
    az
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Only COUNT or SUM value in a row when triggered by a specific letter.

    Vince,

    You are correct that I DO NOT want to count the "P", "P" just signifies that the engine maintenance has been completed and should trigger BA5 to count (otherwise stay at ZERO). The ONLY letters (required for the total FLOW DAYS column) that I would like to count are: "R" "BU" "TD" "F" "TC" "M" ALL other data from my legend DO NOT count against the engine maintenance flow days. The formula you provided worked great only I need to be more specific in what it counts. You are genius in my eyes as I messed with this for two days!!!! Thanks for the help so far!!!!

    ~Alex

  6. #6
    Registered User
    Join Date
    10-15-2012
    Location
    az
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Only COUNT or SUM value in a row when triggered by a specific letter.

    Think I got it using what you provided! Thanks!!! Here is what I used:

    Please Login or Register  to view this content.

  7. #7
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,841

    Re: Only COUNT or SUM value in a row when triggered by a specific letter.

    It appears columns AT5:AY5 already do the job of counting what you want included. Each cell counts only "R" (AT5), "TD" (AU5), "BU" (AV5), "TC" (AW5), "F" (AX5) and "M" (AY5).

    Could we simplify the whole process and just check to see if "P" is present in the range, then sum only THOSE columns? Since they are the only ones that count against the engine maintenance flow days?

    If "P" ISN'T in the range (N5:AR5) then BA5=0.
    If "P" IS in the range, then BA5 = sum AT5:AY5, and add L5 to it as in the formula below:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    There's no need to re-count all of the entries if they are already totaled in other cells.

    -Vince

  8. #8
    Registered User
    Join Date
    10-15-2012
    Location
    az
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Only COUNT or SUM value in a row when triggered by a specific letter.

    I guess I like doing things the hard way!!! LOL I just did every cell the LONG WAY using the FILL feature of course. Like I stated in my OG post this is my first go around using EXCEL. Took me about 5 days to make this chart but DAMN did I learn some stuff! Thanks again, Ill use the short formula as it only makes things better!

    ~Alex

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1