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