Forum Discussion

PaulM1970's avatar
PaulM1970
Copper Contributor
Apr 24, 2024

Need help with counting the last digit of a number

Hi team!   Respectfully request help with the following:   Need a formula in cell D3 of Sheet2 that accomplishes the following:   If Sheet1 column D says, "Acme", count the number of times the ...
excel
Formulas and Functions
  • PaulM1970's avatar
    PaulM1970
    Copper Contributor
    Apr 28, 2024

    Ok, thank you both. OliverScheurich and Hans_Vogelaar.

    Both of those formulas work. The issue was on my side. I had data in the Job Code and SJC columns that was the cause of the error on my end.

     

    If you enter values such as "Vacant" or "#NA!" into either of the columns we're pulling the data FROM, you'll see that you get the #VALUE! error with your formulas as well. I'm thinking it isn't possible to have the formula ignore data that isn't truly a number.

     

    Thanks again.

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Apr 27, 2024

    PaulM1970 

    =SUMPRODUCT((Table5[Site]=$B$2)*(Table5[Company]=$C$2)*(--RIGHT(Table5[Job Code])=C16)*(Table5[SJC]=C16))

     

    SUMPRODUCT returns the expected results in your file.

  • PaulM1970's avatar
    PaulM1970
    Copper Contributor
    Apr 27, 2024
    Thank you, but this did not work.

    Could it be because the data found in my proprietary version of the "Job Code" cell on the Sites tab is actually a formula that pulls data from an external workbook?

    If I'm reading your formula correctly, it appears as if you are telling the formula to divide the "number" in the Job Code column by 10. If Excel doesn't see it as a number, maybe that's the reason I'm getting a #VALUE! response?

    When I add a "@" symbol to each column reference in the formula, (example, changing "Table5[Site]" to 'Table5[@Site]", the VALUE error turns into a number, but the number does not match the adjacent SJC column.

    More info: The SITE | Job Code and SJC columns are formatted as Numbers, as are the USE tab SJC numbers.

    Question about possible alternate method: Is there a way to pull the last digit of all "numbers" in the Job Code column, and compare them to all of the last digits in the SJC column? Example:

    SJC column lists the following at the Acme site:
    1234
    5678
    9012

    Directly beside these numbers are the SJC numbers, also associated with the ACME site:
    4
    8
    2

    The formula should pull data identifying that we have (1) matching 4, (1) matching 8 and (1) matching 2 at the ACME site.

Resources