=SUMPRODUCT((A6:A20007=A20012)*(B6:B20007=B20012)* (D6:D20007=D20012)*(F6:F20007))

This is a very nice way to do multiple sumif's to return a sum.

Is there a way to use wild characters. For example one of the columns I am
using is for departments. A certian goup starts with "42##" so in B20012 per
the function above I put 42* in the cell but it did not work. Is there a way
to do this.

Thank you,

Steven

Steven wrote:
=SUMPRODUCT((A6:A20007=A20012)*(B6:B20007=B20012)* (D6:D20007=D20012)*(F6:F20007))

This is a very nice way to do multiple sumif's to return a sum.

Is there a way to use wild characters. For example one of the columns I am
using is for departments. A certian goup starts with "42##" so in B20012 per
the function above I put 42* in the cell but it did not work. Is there a way
to do this.

Thank you,

Steven



LEFT(B6:B20007,2)="42"

This will produce a true/false array:

ISNUMBER(MATCH("42??",A6:A20007,0))

Whole formula:


=SUMPRODUCT((A6:A20007=A20012)*ISNUMBER(MATCH(B200 12&"*",B6:B20007,0))*(D6:D20007=D20012)*(F6:F20007 ))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Steven" wrote:


=SUMPRODUCT((A6:A20007=A20012)*(B6:B20007=B20012)* (D6:D20007=D20012)*(F6:F20007))

This is a very nice way to do multiple sumif's to return a sum.

Is there a way to use wild characters. For example one of the columns I am
using is for departments. A certian goup starts with "42##" so in B20012 per
the function above I put 42* in the cell but it did not work. Is there a way
to do this.

Thank you,

Steven

(LEFT(B6:B20007,2)="42")


"Steven" wrote:


=SUMPRODUCT((A6:A20007=A20012)*(B6:B20007=B20012)* (D6:D20007=D20012)*(F6:F20007))

This is a very nice way to do multiple sumif's to return a sum.

Is there a way to use wild characters. For example one of the columns I am
using is for departments. A certian goup starts with "42##" so in B20012 per
the function above I put 42* in the cell but it did not work. Is there a way
to do this.

Thank you,

Steven