SUMPRODUCT Worksheet Function

[Kate]

Hi,

I have a spreadsheet that looks something like this:

a 0 June
a 0 June
b 1 May
c 0 May
a 1 June
d 1 May
d 0 June

I'm trying to get a formula that will calculate the number
of a's with "0" in column b and in June. I tried a
formula that looks something like this:

=SUMPRODUCT(--(A1:A7="a"))--(B1:B7="0")--(C1:C7="June")

But the formula result turned out a "0" incorrectly. Can
you help????

Thanks,

Kate

 

[JulieD]

Hi Kate

try
=SUMPRODUCT((A1:A7="a")*(B1:B7=0)*(C1:C7="June")) if column B is numeric or
=SUMPRODUCT((A1:A7="a")*(B1:B7="0")*(C1:C7="June")) if column B is text


Cheers
JulieD

 

[Kate]

Hi,

It still shows 0 for the result. Any other ideas??
-Kate

 

[Bob Phillips]

or your formula should be

=SUMPRODUCT(--(A1:A7="a"),--(B1:B7="0"),--(C1:C7="June"))
or

=SUMPRODUCT(--(A1:A7="a"),--(B1:B7=0),--(C1:C7="June"))



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[JulieD]

Hi Kate
are the values in column B numeric or text?
are you sure there are no spaces in front (or behind) the letters in
column A?

Cheers
JulieD

 

[Harlan Grove]

are the values in column B numeric or text?
are you sure there are no spaces in front (or behind) the letters in column A? ...

...

Both numeric/text and stray spaces can be dealt with in a single formula.

=SUMPRODUCT((TRIM(A1:A7)="a")*(TRIM(B1:B7)="0")*(TRIM(C1:C7)="June"))

[Yes, Bob, I know I'm not using separate -- terms.]

If this formula doesn't work with the sample data in the original post, then
there could be trailing nonbreaking spaces in these cells. That'd require
SUBSTITUTE(TRIM(..),CHAR(160),"") calls.

 

[Bob Phillips]

Both numeric/text and stray spaces can be dealt with in a single formula.

=SUMPRODUCT((TRIM(A1:A7)="a")*(TRIM(B1:B7)="0")*(TRIM(C1:C7)="June"))

[Yes, Bob, I know I'm not using separate -- terms.]

Have to smile!

Like the use opf TRIM.

If this formula doesn't work with the sample data in the original post, then
there could be trailing nonbreaking spaces in these cells. That'd require
SUBSTITUTE(TRIM(..),CHAR(160),"") calls.

By God it gets tricky trying to cater for the data!

 

[David]

If this formula doesn't work with the sample data in the original post, then
there could be trailing nonbreaking spaces in these cells. That'd require
SUBSTITUTE(TRIM(..),CHAR(160),"") calls.

Harlan,
I picked up on this as a potential problem in some of the data cleansing I am doing.
I imagined that an example of "nonbreaking trailing spaces" would be: A^^^ (where ^ = space) . However, I found that TRIM("A^^^") returns "A" (1 character), ie no problem.
What did you mean by nonbreaking spaces?
tia

 

[Harlan Grove]

...
...

What did you mean by nonbreaking spaces?

There are two kind of spaces, which can be seen from the formulas

="|"&REPT(CHAR(32),10)&"|"&REPT(CHAR(160),10)&"|"

Enter this formula in cell A1 in a blank worksheet, then enter the next formula
in cell A3.

=TRIM(A1)

This should illustrate one of the differences between them.