SUMIF Using Dynamic Ranges based on text similarities

  • Hello,


    Im wanting to do something like this. Where one formula will give me back the average of the entirety of the cells labeled APPLE, and then ORANGE, and etc. Im guessing i need an array, but im not sure how to do this one. I've been doing A2<>A3 to distinguish when the text changes, but i cant figure out a way to get a range in AVG(). Can someone help me?


    [TABLE="width: 192"]

    [tr]


    [TD="width: 64"][/TD]
    [TD="width: 64"]Stock[/TD]
    [TD="width: 64"]Average[/TD]

    [/tr]


    [tr]


    [td]

    Apple

    [/td]


    [TD="align: right"]123[/TD]
    [TD="align: right"]10678.5[/TD]

    [/tr]


    [tr]


    [td]

    APPle

    [/td]


    [TD="align: right"]1234[/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    APPLE

    [/td]


    [TD="align: right"]123[/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    APPLE

    [/td]


    [TD="align: right"]41234[/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    ORANGES

    [/td]


    [TD="align: right"]123[/TD]
    [TD="align: right"]3123[/TD]

    [/tr]


    [tr]


    [td]

    ORANGES

    [/td]


    [TD="align: right"]4123[/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    ORANGES

    [/td]


    [TD="align: right"]4123[/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    ORANGES

    [/td]


    [TD="align: right"]4123[/TD]

    [td][/td]


    [/tr]


    [/TABLE]

  • Re: SUMIF Using Dynamic Ranges based on text similarities


    and thanks to whoever helps! sorry just realized i didnt say anything. was awfully rude of me

  • Re: SUMIF Using Dynamic Ranges based on text similarities


    Hi,


    Assuming your data is in A2:B9, try this formula in C2:


    [COLOR="#0000FF"]=IF(A2=A1,"",AVERAGEIF($A$2:$A$9,A2,$B$2:$B$9))[/COLOR]


    copied down.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: SUMIF Using Dynamic Ranges based on text similarities



    Similar problem again, btu cant use this argument


    [TABLE="width: 427"]

    [tr]


    [td]

    Age

    [/td]


    [td]

    Average Board Age

    [/td]


    [td]

    First digit

    [/td]


    [td][/td]


    [td]

    Count

    [/td]


    [/tr]


    [tr]


    [TD="align: right"]68[/TD]
    [TD="align: right"]54.58333333[/TD]

    [td]

    6

    [/td]


    [TD="align: right"]1[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]59[/TD]

    [td][/td]


    [td]

    5

    [/td]


    [TD="align: right"]1[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]60[/TD]

    [td][/td]


    [td]

    6

    [/td]


    [TD="align: right"]0[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]55[/TD]

    [td][/td]


    [td]

    5

    [/td]


    [TD="align: right"]0[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]41[/TD]

    [td][/td]


    [td]

    4

    [/td]


    [TD="align: right"]1[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]50[/TD]

    [td][/td]


    [td]

    5

    [/td]


    [TD="align: right"]0[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]47[/TD]

    [td][/td]


    [td]

    4

    [/td]


    [TD="align: right"]0[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]68[/TD]

    [td][/td]


    [td]

    6

    [/td]


    [TD="align: right"]0[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]32[/TD]

    [td][/td]


    [td]

    3

    [/td]


    [TD="align: right"]1[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]35[/TD]

    [td][/td]


    [td]

    3

    [/td]


    [TD="align: right"]0[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]72[/TD]

    [td][/td]


    [td]

    7

    [/td]


    [TD="align: right"]1[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]68[/TD]

    [td][/td]


    [td]

    6

    [/td]


    [TD="align: right"]0[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]70[/TD]
    [TD="align: right"]63.92857143[/TD]

    [td]

    7

    [/td]


    [TD="align: right"]0[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]71[/TD]

    [td][/td]


    [td]

    7

    [/td]


    [TD="align: right"]0[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]55[/TD]

    [td][/td]


    [td]

    5

    [/td]


    [TD="align: right"]0[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]57[/TD]

    [td][/td]


    [td]

    5

    [/td]


    [TD="align: right"]0[/TD]

    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]72[/TD]

    [td][/td]


    [td]

    7

    [/td]


    [TD="align: right"]0[/TD]

    [td][/td]


    [/tr]


    [/TABLE]
    So im looking how many "Age Groups" there are in a given data set. Again i have apples and oranges in the far right. Im using =LEFT($A2,1) to get the first digit, and then using =(COUNTIF($A$2:$A2,$A2)=1)+0 to count the number of different unique digits. problem is, that I cant figure out the same way i did before, that is figuring out dynamic ranges, so i get different countifs for each category (apple, oranges

  • Re: SUMIF Using Dynamic Ranges based on text similarities


    Have you tried the COUNTIFS function? It allows you to reference multiple ranges, each with it's own criteria....

    Where there is a will there are many ways. Finding one that works for you is the challenge!