Consulting

Results 1 to 5 of 5

Thread: Solved: sumif feature needed for multiple criteria using wildcards

  1. #1
    VBAX Regular
    Joined
    Apr 2007
    Location
    Saluda SC
    Posts
    23
    Location

    Solved: sumif feature needed for multiple criteria using wildcards

    I have developed a formula that will sum column e from the A1 sheet that corresponds with the cell in column c of the total sheet if it matches the text located in column c of the A1 sheet. However, I can not get one to work for cell +C43 since it has several codes I am looking for. I would like to be able to sum all the values in column e on sheet A1 that corresponds with 733, 829, 834, or 854. I can get it to work if I ask for just one value. But if I ask for 733 or 829, it pulls a bad value.

    I have attached the spreadsheet.

    Thanks in advance for the help. I have spent two days trying to get it to work but to no prevail.

    Melinda

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    =SUMPRODUCT(('A1'!$C$13:$C$150={733,829,834,854})*('A1'!$E$13:$E$150))

    but this returns zero as none match
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Regular
    Joined
    Apr 2007
    Location
    Saluda SC
    Posts
    23
    Location

    sumif feature needed for multiple criteria using wildcards

    Xld Thanks for such fast response.

    However, I need it to sum column e on sheet A1 for any cell that has either 733, 829, 834, or 854 in column c on sheet A1. For example, Sheet A1 has 733 in cell +C30 so the total should have been $3,723.46. Another sheet may have listed in cell +c30 704/829/834 and in cell +e30 it may have $2,674.24. I would want it to pull the $2,674.24 because the 829 was listed in c30. I hope that helps explain things a little clearer.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    It doesn't have &33 in it, it contains 733, that is different.

    This should do what you now want

    =SUMPRODUCT((ISNUMBER(FIND({733,829,834,854},'A1'!$C$13:$C$150)))*('A1'!$E$ 13:$E$150))
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Regular
    Joined
    Apr 2007
    Location
    Saluda SC
    Posts
    23
    Location

    sumif feature needed for multiple criteria using wildcards

    xld, Thank you very much. This fix it. You make it look so simple.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •