SUMPRODUCT Returning 0 or wrong totals
 

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.

Answer: SUMPRODUCT Returning 0 or wrong totals
 

Hi there! It sounds like you're having trouble with the SUMPRODUCT formula in Excel. Let's see if we can figure out what's going on.

First, let's take a look at your formula:

This formula is looking for two criteria to be met: region "C" and no notes in the "M" column. It then sums up the values in the "L" column for those rows that meet both criteria.

One thing to note is that the "^" symbol in your formula is not necessary. This symbol is used to indicate the start of a string, but since you're looking for an exact match with the region "C", you can remove it.

Now, let's take a look at why your formula might be returning 0 or incorrect totals. One possibility is that there are no rows that meet both criteria. In this case, the formula would return 0. Another possibility is that there are errors or blank cells in the "L" column, which could throw off the total.

To troubleshoot this, try breaking down the formula into smaller parts. First, try just looking for the rows that meet the region "C" criteria:

1. Code:

   ---

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

   ---

If this returns a value, then add in the notes criteria:

1. Code:

   ---

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

   ---

If this still returns 0 or an incorrect total, check for errors or blank cells in the "L" column. You can do this by using the COUNTBLANK and ISERROR functions:

1. Code:

   ---

   =COUNTBLANK(Detail!L2:L12440)

   ---

   - counts the number of blank cells in the "L" column
2. Code:

   ---

   =SUM(IF(ISERROR(Detail!L2:L12440),1,0))

   ---

   - counts the number of cells in the "L" column that contain errors

If there are errors or blank cells, you'll need to clean up the data before the formula will work correctly.

SUMPRODUCT Returning 0 or wrong totals
 

Hi,

Try this

=SUMPRODUCT((Detail!B2:B12440="C")*(Detail!M2:M124 40="")*(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.


"Rbirdie" wrote:

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.

SUMPRODUCT Returning 0 or wrong totals
 

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

"Rbirdie" skrev i meddelelsen
...
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.

SUMPRODUCT Returning 0 or wrong totals
 

Hi
Not sure I understand your formula , but try this :
=SUMPRODUCT(--(Detail!$A2:$A12440 ="C"),--(Detail!$M2:$M12440
=""),(Detail!L2:L12440))
HTH
John
"Rbirdie" wrote in message
...
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.

SUMPRODUCT Returning 0 or wrong totals
 

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" wrote:

Hi,

Try this

=SUMPRODUCT((Detail!B2:B12440="C")*(Detail!M2:M124 40="")*(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.


"Rbirdie" wrote:

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.

SUMPRODUCT Returning 0 or wrong totals
 

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" wrote:

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" wrote:

Hi,

Try this

=SUMPRODUCT((Detail!B2:B12440="C")*(Detail!M2:M124 40="")*(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.


"Rbirdie" wrote:

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.

SUMPRODUCT Returning 0 or wrong totals
 

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.


"Per Jessen" wrote:

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

"Rbirdie" skrev i meddelelsen
...
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.

.

SUMPRODUCT Returning 0 or wrong totals
 

Thanks John- It is still returning 0.


"John" wrote:

Hi
Not sure I understand your formula , but try this :
=SUMPRODUCT(--(Detail!$A2:$A12440 ="C"),--(Detail!$M2:$M12440
=""),(Detail!L2:L12440))
HTH
John
"Rbirdie" wrote in message
...
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.


.

SUMPRODUCT Returning 0 or wrong totals
 

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?



"Mike H" wrote:

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" wrote:

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" wrote:

Hi,

Try this

=SUMPRODUCT((Detail!B2:B12440="C")*(Detail!M2:M124 40="")*(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.


"Rbirdie" wrote:

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.

SUMPRODUCT Returning 0 or wrong totals
 

You could try this array formula

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

--

HTH

Bob

"Rbirdie" wrote in message
...
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?



"Mike H" wrote:

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" wrote:

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" wrote:

Hi,

Try this

=SUMPRODUCT((Detail!B2:B12440="C")*(Detail!M2:M124 40="")*(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.


"Rbirdie" wrote:

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.

SUMPRODUCT Returning 0 or wrong totals
 

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" wrote:

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.

SUMPRODUCT Returning 0 or wrong totals
 

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.


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

"pmartglass" wrote:

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" wrote:

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.