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