RE: 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 ***, 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

.