Forum Discussion

Shane Rogers's avatar
Shane Rogers
Copper Contributor
May 15, 2018

Excel Formula Help

I at best a novice with Excel so I am reaching out for help. I am trying to calculate hours based of of a letter code for an activity and need the formula to calculate a sum based of of the letter in the cell. Here is a rough way I understand to write it to explain it. I know these are not even close to working formulas for Excel. 

IF B3=G Then +1 in AC3 

IF B3=A Then +1 in AD3

IF B3= R Then +1 in AE3

 

I have many other cells that will have the letters A,R, or G in them and I am trying to calculate how many of each of these letters there are throughout the sheet into another cell. i.e. total number of "G" in cells B3,K3. But those letters could change to A or R in those same cells and I want the new letter calculated in a different cell. 

 

Attached is what I am working on.

 

Any help would be greatly appreciated.

 

Thanks

 

 

 

 

Excel Help
  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    May 15, 2018

    Shane-

     

    Try something like this (See attached Excel File):

     

    The formula I used can be simplified:

     

    =COUNTIF(B3:AA3,LEFT($AC$2,1))

     

    To

     

    =COUNTIF(B3:AA3,"G")

     

      • Matt Mickle's avatar
        Matt Mickle
        Bronze Contributor
        May 16, 2018

        Always happy to help.  Please feel free to post back to the community if you have further issues!  This forum is a great resource!

  • John Jairo Vergara Domínguez's avatar
    John Jairo Vergara Domínguez
    Brass Contributor
    May 16, 2018
    The Matt Mickle's formula must be entered in individual cells (AC3, AD3 and AE3) and later drag it down. You can use instead, in AC3:

    =COUNTIF($B3:$AA3,LEFT(AC$2))

    And later drag it right and down. Blessings!

Resources