SUMPRODUCT Returning 0 or wrong totals

[Rbirdie]

I have been trying to figure this out for 3 days.
I have 2 criterias that need to be met and then I want the total of a column.
I am trying to get a total of each region with notes and without notes.


Col A is region
Col M are notes on accts
Col L are dollars

A M L

1. C Notes in field 3.15
2. W Blank 100.50
3. NE Notes in field 600.00

Here is my formula:
=SUMPRODUCT((Detail!$A2:$A12440 ="^C"),(Detail!$M2:$M12440
=""),(Detail!L2:L12440))

It is returning 0. If I play with the formula and change the , to -- then it
does return a value, but one that is not correct.
I also tried filling in the BLANKS of column M with a word and that did not
help.
Please any help is appreciated.

 

[Mike H]

Hi,

Try this

=SUMPRODUCT((Detail!B2:B12440="C")*(Detail!M2:M12440="")*(Detail!L2:L12440))

I don't understand what you were trying to do with "^C" in your formula. If
this doesn't work please explain
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Per Jessen]

Hi

If you use , between the arrays, you need -- also before the two first
arrays, to convert TRUE/FALSE to 1/0, or you can use * between the arrays:

=SUMPRODUCT(--(Detail!$A2:$A12440 ="^C"),--(Detail!$M2:$M12440
=""),Detail!L2:L12440)

=SUMPRODUCT((Detail!$A2:$A12440 ="^C")*(Detail!$M2:$M12440
="")*Detail!L2:L12440)

Also I am not sure if you should put ^ in front of 'C'

Hopes this helps.
....
Per

 

[John]

Hi
Not sure I understand your formula , but try this :
=SUMPRODUCT(--(Detail!$A2:$A12440 ="C"),--(Detail!$M2:$M12440
=""),(Detail!L2:L12440))
HTH
John

 

[Rbirdie]

Thanks for your reply Mike. The ^is before the letters in the Details page. I
tried taking it out and it does not work either.

Below is returning a "0"
=SUMPRODUCT((Detail!A2:A12440 ="C")*(Detail!M2:M12440 ="")*(Detail!L2:L12440))

If I take in less rows, (tried using just 6 as test), it worked. When I do
all of the data is failing.

 

[Mike H]

Hi,

Tell us what 'not working' means. Are you getting a #VALUE! error?

If you are then I suspect that some of your numbers aren't numbers and are
really text.

Try this formula in an empty column and drag down
=ISNUMBER(L2)

If your numbers in column L are really numbers then it wiil return TRUE. If
it returns FALSE then they aren't numbers.

Put a 1 in a cell and copy it. Select column L and then

Edit|Paste special - select 'Multiply' OK and try the formula again
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Rbirdie]

Thanks for the support.

I have updated to
=SUMPRODUCT(--(Detail!A2:A12440 ="C"),--(Detail!M2:M12440
=""),(Detail!L2:L12440))

I am still getting "0".

What is strange is that if I change the formula to
=SUMPRODUCT(--(Detail!A2:A12440 >"C"),--(Detail!M2:M12440
=""),(Detail!L2:L12440))
I receive the value of all regions greater than C with no notes. It works.

 

[Rbirdie]

Thanks John- It is still returning 0.

Hi
Not sure I understand your formula , but try this :
=SUMPRODUCT(--(Detail!$A2:$A12440 ="C"),--(Detail!$M2:$M12440
=""),(Detail!L2:L12440))
HTH
John


.

 

[Rbirdie]

I am getting a "0" for the result.
My numbers are numbers, I tested them. I am thinking that the Region (column
A) is the issue. Is there a way to do a wildcard with this value?

 

[Bob Phillips]

You could try this array formula

=SUM((RIGHT(Detail!$A2:$A12440,MAX(1,LEN(Detail!$A2:$A12440)-1))="C")*(Detail!$M2:$M12440="")*(Detail!L2:L12440))

 

[pmartglass]

is it possible that your column M is not actually blank
maybe you should try to trim the cell then check for ""

'=SUMPRODUCT(--(Detail!$A2:$A12440 ="C"),--(trim(Detail!$M2:$M12440)
=""),(Detail!L2:L12440))

 

[Rbirdie]

Column M is actually blank. But, you bring up a good point.

I believe that Column A in some cases has a space after the data.
If I can use the trim on column A then it might solve the issue.

=SUMPRODUCT(--(TRIM(Detail!$A3:$A12441)
="C"),--(Detail!$M3:$M12441<>""),(Detail!L3:L12441))

THIS WORKS!!!!!!!!!!!!!!!!!!

It eliminates that extra space when it is there.