Sumproduct problems...
 

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

Sumproduct problems...
 

SUMPRODUCT doesn't allow wild cards.

Try:

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

"Johnny M" wrote:

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

Sumproduct problems...
 

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

"Johnny M" wrote in message
...
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

Sumproduct problems...
 

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" wrote:

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

"Johnny M" wrote in message
...
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

Sumproduct problems...
 

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

"Johnny M" wrote in message
...
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" wrote:

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

"Johnny M" wrote in message
...
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