PDA

View Full Version : Solved: sumif feature needed for multiple criteria using wildcards



Melinda
07-18-2008, 10:22 AM
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.:banghead:

Melinda

Bob Phillips
07-18-2008, 10:33 AM
=SUMPRODUCT(('A1'!$C$13:$C$150={733,829,834,854})*('A1'!$E$13:$E$150))

but this returns zero as none match

Melinda
07-18-2008, 10:48 AM
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.

Bob Phillips
07-18-2008, 11:36 AM
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))

Melinda
07-18-2008, 11:46 AM
xld, Thank you very much. This fix it. You make it look so simple.:thumb