SUMPRODUCT - Count Various criteria in same column (exclude other)
 

Hi

Im trying to count various items in a column, but not all. I can either do
this by stipulating a range of items or using a wildcard. However, Sumproduct
is not returning any results when I use more than one item/criteria for a
specified column.

Example:
=SUMPRODUCT((A2:A60000=€�Debit Order€�)*(B2:B60000=€�Not Affordable due to*))
Or
=SUMPRODUCT((A2:A60000=€�Debit Order€�)*(B2:B60000=€�Not Affordable due to
nett)*(B2:B60000=€�Not Affordable due to gross))

Both results gives me a 0 result. I tried replacing the * with ,-- and , and +

Any advice?

Regards

EricB

SUMPRODUCT - Count Various criteria in same column (exclude other)
 

Well, in addition to some missing closing quotes, the logic in the second
formula will ALWAYS drive you to a zero result, since you're looking at
mutually exclusive alternatives in the the same column

(B2:B60000=€�Not Affordable due to nett)*(B2:B60000=€�Not Affordable due to
gross))

For the first one, try
=SUMPRODUCT(--(A2:A60000=€�Debit Order€�),--(left(B2:B60000,18)=€�Not
Affordable due to"))


"EricB" wrote:

Hi

Im trying to count various items in a column, but not all. I can either do
this by stipulating a range of items or using a wildcard. However, Sumproduct
is not returning any results when I use more than one item/criteria for a
specified column.

Example:
=SUMPRODUCT((A2:A60000=€�Debit Order€�)*(B2:B60000=€�Not Affordable due to*))
Or
=SUMPRODUCT((A2:A60000=€�Debit Order€�)*(B2:B60000=€�Not Affordable due to
nett)*(B2:B60000=€�Not Affordable due to gross))

Both results gives me a 0 result. I tried replacing the * with ,-- and , and +

Any advice?

Regards

EricB

SUMPRODUCT - Count Various criteria in same column (exclude other)
 

Hi,

You can't use wildcards in sumproduct, try this

=SUMPRODUCT((A2:A60000="Debit Order")*(LEFT(B2:B60000,26)="Not Affordable
due to nett"))

Mike

"EricB" wrote:

Hi

Im trying to count various items in a column, but not all. I can either do
this by stipulating a range of items or using a wildcard. However, Sumproduct
is not returning any results when I use more than one item/criteria for a
specified column.

Example:
=SUMPRODUCT((A2:A60000=€�Debit Order€�)*(B2:B60000=€�Not Affordable due to*))
Or
=SUMPRODUCT((A2:A60000=€�Debit Order€�)*(B2:B60000=€�Not Affordable due to
nett)*(B2:B60000=€�Not Affordable due to gross))

Both results gives me a 0 result. I tried replacing the * with ,-- and , and +

Any advice?

Regards

EricB

SUMPRODUCT - Count Various criteria in same column (exclude ot
 

Oops - that should have been

=SUMPRODUCT(--(A2:A60000=€�Debit Order€�),--(left(B2:B60000,21)=€�Not
Affordable due to"))

"Duke Carey" wrote:

Well, in addition to some missing closing quotes, the logic in the second
formula will ALWAYS drive you to a zero result, since you're looking at
mutually exclusive alternatives in the the same column

(B2:B60000=€�Not Affordable due to nett)*(B2:B60000=€�Not Affordable due to
gross))

For the first one, try
=SUMPRODUCT(--(A2:A60000=€�Debit Order€�),--(left(B2:B60000,18)=€�Not
Affordable due to"))


"EricB" wrote:

Hi

Im trying to count various items in a column, but not all. I can either do
this by stipulating a range of items or using a wildcard. However, Sumproduct
is not returning any results when I use more than one item/criteria for a
specified column.

Example:
=SUMPRODUCT((A2:A60000=€�Debit Order€�)*(B2:B60000=€�Not Affordable due to*))
Or
=SUMPRODUCT((A2:A60000=€�Debit Order€�)*(B2:B60000=€�Not Affordable due to
nett)*(B2:B60000=€�Not Affordable due to gross))

Both results gives me a 0 result. I tried replacing the * with ,-- and , and +

Any advice?

Regards

EricB

SUMPRODUCT - Count Various criteria in same column (exclude ot
 

Hi Duke & Mike

I possibly gave too little detail of the extent of the sheet, my columns
range to AF (Not A & B as stipulated below): Here is the 'modified formula',
=SUMPRODUCT(--(sscrq!C2:C60000=€�Debit Order€�),--(left(sschq!AF2:AF60000,18)=€�Not Affordable due to"))

EXCEL is spitting errors at me.

What does the phrase "LEFT" and number "18" or "26" refer to in your formula

Regards

EricB

"Duke Carey" wrote:

Well, in addition to some missing closing quotes, the logic in the second
formula will ALWAYS drive you to a zero result, since you're looking at
mutually exclusive alternatives in the the same column

(B2:B60000=€�Not Affordable due to nett)*(B2:B60000=€�Not Affordable due to
gross))

For the first one, try
=SUMPRODUCT(--(A2:A60000=€�Debit Order€�),--(left(B2:B60000,18)=€�Not
Affordable due to"))


"EricB" wrote:

Hi

Im trying to count various items in a column, but not all. I can either do
this by stipulating a range of items or using a wildcard. However, Sumproduct
is not returning any results when I use more than one item/criteria for a
specified column.

Example:
=SUMPRODUCT((A2:A60000=€�Debit Order€�)*(B2:B60000=€�Not Affordable due to*))
Or
=SUMPRODUCT((A2:A60000=€�Debit Order€�)*(B2:B60000=€�Not Affordable due to
nett)*(B2:B60000=€�Not Affordable due to gross))

Both results gives me a 0 result. I tried replacing the * with ,-- and , and +

Any advice?

Regards

EricB

SUMPRODUCT - Count Various criteria in same column (exclude ot
 

What kinds of errors?

The LEFT() function returns the leftmost portion of a string, up to the
number of characters you specify. Although my first post said 18, I updated
that to be 21. That happens to be the length of the string

€�Not Affordable due to"

So..the SUMPRODUCT formula is returning true for each cell in
sschq!AF2:AF60000 that STARTS with €�Not Affordable due to", assuming you
change the 18 to 21

"EricB" wrote:

Hi Duke & Mike

I possibly gave too little detail of the extent of the sheet, my columns
range to AF (Not A & B as stipulated below): Here is the 'modified formula',
=SUMPRODUCT(--(sscrq!C2:C60000=€�Debit Order€�),--(left(sschq!AF2:AF60000,18)=€�Not Affordable due to"))

EXCEL is spitting errors at me.

What does the phrase "LEFT" and number "18" or "26" refer to in your formula

Regards

EricB

"Duke Carey" wrote:

Well, in addition to some missing closing quotes, the logic in the second
formula will ALWAYS drive you to a zero result, since you're looking at
mutually exclusive alternatives in the the same column

(B2:B60000=€�Not Affordable due to nett)*(B2:B60000=€�Not Affordable due to
gross))

For the first one, try
=SUMPRODUCT(--(A2:A60000=€�Debit Order€�),--(left(B2:B60000,18)=€�Not
Affordable due to"))


"EricB" wrote:

Hi

Im trying to count various items in a column, but not all. I can either do
this by stipulating a range of items or using a wildcard. However, Sumproduct
is not returning any results when I use more than one item/criteria for a
specified column.

Example:
=SUMPRODUCT((A2:A60000=€�Debit Order€�)*(B2:B60000=€�Not Affordable due to*))
Or
=SUMPRODUCT((A2:A60000=€�Debit Order€�)*(B2:B60000=€�Not Affordable due to
nett)*(B2:B60000=€�Not Affordable due to gross))

Both results gives me a 0 result. I tried replacing the * with ,-- and , and +

Any advice?

Regards

EricB

SUMPRODUCT - Count Various criteria in same column (exclude ot
 

Hi

In all such comparisions, compared values must be EXACTLY same. I.e. When
you have in column AF something like ”Not Affordable due to " or ”Not
Affordable due to", and then search for ”Not Affordable due to", no match
is found.

Try remove all abundant spaces from column AF. A possible way to do this:
1. Select the column AF. Use ReplaceAll to replace all double spaces (" ")
with single space (" ");
Into some free column, enter the formula like
=TRIM(AF2)
, copy the formula down for whole table, and then use PasteSpecial.Values to
replace values in column AF with newly calculated ones (after that you can
delete additional column).

Duke Carey tried to do almost same by determining the length of compared
string, but missed with string length (21 instead of 18 was right one). And
the formula helps only, when you have trailing spaces, but no leading ones
or double spaces between words.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"EricB" wrote in message
...
Hi Duke & Mike

I possibly gave too little detail of the extent of the sheet, my columns
range to AF (Not A & B as stipulated below): Here is the 'modified
formula',
=SUMPRODUCT(--(sscrq!C2:C60000=”Debit
Order”),--(left(sschq!AF2:AF60000,18)=”Not Affordable due to"))

EXCEL is spitting errors at me.

What does the phrase "LEFT" and number "18" or "26" refer to in your
formula

Regards

EricB

"Duke Carey" wrote:

Well, in addition to some missing closing quotes, the logic in the second
formula will ALWAYS drive you to a zero result, since you're looking at
mutually exclusive alternatives in the the same column

(B2:B60000=”Not Affordable due to nett)*(B2:B60000=”Not Affordable due to
gross))

For the first one, try
=SUMPRODUCT(--(A2:A60000=”Debit Order”),--(left(B2:B60000,18)=”Not
Affordable due to"))


"EricB" wrote:

Hi

I’m trying to count various items in a column, but not all. I can
either do
this by stipulating a range of items or using a wildcard. However,
Sumproduct
is not returning any results when I use more than one item/criteria for
a
specified column.

Example:
=SUMPRODUCT((A2:A60000=”Debit Order”)*(B2:B60000=”Not Affordable due
to*))
Or
=SUMPRODUCT((A2:A60000=”Debit Order”)*(B2:B60000=”Not Affordable due to
nett)*(B2:B60000=”Not Affordable due to gross))

Both results gives me a 0 result. I tried replacing the * with ,-- and
, and +

Any advice?

Regards

EricB

SUMPRODUCT - Count Various criteria in same column (exclude ot
 

=SUMPRODUCT(--(sscrq!C2:C60000="Debit Order"),--(ISNUMBER(SEARCH("Not
Affordable",sscrq!AF2:AF60000))))


"EricB" wrote:

Hi Duke & Mike

I possibly gave too little detail of the extent of the sheet, my columns
range to AF (Not A & B as stipulated below): Here is the 'modified formula',
=SUMPRODUCT(--(sscrq!C2:C60000=€�Debit Order€�),--(left(sschq!AF2:AF60000,18)=€�Not Affordable due to"))

EXCEL is spitting errors at me.

What does the phrase "LEFT" and number "18" or "26" refer to in your formula

Regards

EricB

"Duke Carey" wrote:

Well, in addition to some missing closing quotes, the logic in the second
formula will ALWAYS drive you to a zero result, since you're looking at
mutually exclusive alternatives in the the same column

(B2:B60000=€�Not Affordable due to nett)*(B2:B60000=€�Not Affordable due to
gross))

For the first one, try
=SUMPRODUCT(--(A2:A60000=€�Debit Order€�),--(left(B2:B60000,18)=€�Not
Affordable due to"))


"EricB" wrote:

Hi

Im trying to count various items in a column, but not all. I can either do
this by stipulating a range of items or using a wildcard. However, Sumproduct
is not returning any results when I use more than one item/criteria for a
specified column.

Example:
=SUMPRODUCT((A2:A60000=€�Debit Order€�)*(B2:B60000=€�Not Affordable due to*))
Or
=SUMPRODUCT((A2:A60000=€�Debit Order€�)*(B2:B60000=€�Not Affordable due to
nett)*(B2:B60000=€�Not Affordable due to gross))

Both results gives me a 0 result. I tried replacing the * with ,-- and , and +

Any advice?

Regards

EricB

SUMPRODUCT - Count Various criteria in same column (exclude ot
 

Hi Tetthless mama

Working like a charm....... Many thx

Duke, Mike, Arvi - Thank you for responding.

Regards

EricB

"Teethless mama" wrote:

=SUMPRODUCT(--(sscrq!C2:C60000="Debit Order"),--(ISNUMBER(SEARCH("Not
Affordable",sscrq!AF2:AF60000))))


"EricB" wrote:

Hi Duke & Mike

I possibly gave too little detail of the extent of the sheet, my columns
range to AF (Not A & B as stipulated below): Here is the 'modified formula',
=SUMPRODUCT(--(sscrq!C2:C60000=€�Debit Order€�),--(left(sschq!AF2:AF60000,18)=€�Not Affordable due to"))

EXCEL is spitting errors at me.

What does the phrase "LEFT" and number "18" or "26" refer to in your formula

Regards

EricB

"Duke Carey" wrote:

Well, in addition to some missing closing quotes, the logic in the second
formula will ALWAYS drive you to a zero result, since you're looking at
mutually exclusive alternatives in the the same column

(B2:B60000=€�Not Affordable due to nett)*(B2:B60000=€�Not Affordable due to
gross))

For the first one, try
=SUMPRODUCT(--(A2:A60000=€�Debit Order€�),--(left(B2:B60000,18)=€�Not
Affordable due to"))


"EricB" wrote:

Hi

Im trying to count various items in a column, but not all. I can either do
this by stipulating a range of items or using a wildcard. However, Sumproduct
is not returning any results when I use more than one item/criteria for a
specified column.

Example:
=SUMPRODUCT((A2:A60000=€�Debit Order€�)*(B2:B60000=€�Not Affordable due to*))
Or
=SUMPRODUCT((A2:A60000=€�Debit Order€�)*(B2:B60000=€�Not Affordable due to
nett)*(B2:B60000=€�Not Affordable due to gross))

Both results gives me a 0 result. I tried replacing the * with ,-- and , and +

Any advice?

Regards

EricB