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

Put this formula in cell BA5 and copy down:

Formula:

Please Login or Register  to view this content.

- Vince

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

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

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

Please Login or Register  to view this content.

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:

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

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