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

[EricB]

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

 

[Duke Carey]

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"))

 

[Mike H]

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

 

[Duke Carey]

Oops - that should have been

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

 

[EricB]

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',
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]

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

 

[Arvi Laanemets]

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.

 

[Teethless mama]

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

 

[EricB]

Hi Tetthless mama

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

Duke, Mike, Arvi - Thank you for responding.

Regards

EricB