Sumproduct problems...

[Guest]

Hi

Am trying to sumif based on four conditions.I have tried both the formulas
below but they keep returning zero. In the formulas, column C nees to contain
the digits 6131 within a text string, column G is a list of dates, R1 is the
1st April and S1 is 1st May, Column M is a list of shipping codes and column
L is volume shipped.

Is there something I am doing wrong?

=SUMPRODUCT((GAP!$C$2:$C$800="*6131*")*(GAP!$G$2:$G$800>=Data!R$1)*(GAP!$G$2:$G$800<$S$1)*(GAP!$M$2:$M$800="AUA")*(GAP!$L$2:$L$800))

=SUMPRODUCT(--(GAP!$C$2:$C$800="*6131*"),--(GAP!$G$2:$G$800>=Data!R$1),--(GAP!$G$2:$G$800<$S$1),--(GAP!$M$2:$M$800="AUA"))*(GAP!$L$2:$L$800)

Thanks in advance

 

[Guest]

SUMPRODUCT doesn't allow wild cards.

Try:

=SUMPRODUCT(--(ISNUMBER(FIND("6131",GAP!$C$2:$C$800)))) etc

 

[T. Valko]

Try this:

=SUMPRODUCT(--(ISNUMBER(SEARCH(6131,GAP!$C$2:$C$800))),--(GAP!$G$2:$G$800>=Data!R$1),--(GAP!$G$2:$G$800<$S$1),--(GAP!$M$2:$M$800="AUA"),GAP!$L$2:$L$800)

Biff

 

[Guest]

Thanks Guys, still coming up with zero. Must be something wrong with my raw
data I guess (it was exported from another system).

Thanks anyway,

 

[T. Valko]

it was exported from another system

Yep, that's usually the culprit.

Unseen characters like leading/trailing spaces. HTML "junk". Numbers
formatted as text. etc

Biff