SUMPRODUCT over dynamic range - No VBA

  • Hi,
    been a long time since ive needed to ask a question!


    i have a difficult datasource where data comes in as text
    and i cant use text to columns


    i need to count the numbers of cells in column K where 1st character is 0 to 9 or the string is 'Yes'


    added to the complication i need to do this over a variable range of Col K dependent on a repeating ID of variable length in column I


    i can do this for the 'yes' with this
    =COUNTIF(I:I,I5)=SUM(COUNTIFS(K:K,{"Yes"},I:I,I5))


    and i can do this for 0 to 9 part but i cant get it to work for anyting but an explicit range!
    worst part is i bet im missing something simple!


    =SUMPRODUCT(--(CODE(RIGHT(K5:K105,1))<=99))


    (yes i know i can be more specific with 'code'

  • Re: SUMPRODUCT over dynamic range - No VBA


    Please attach a sample workbook. Thanks!

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!