Sudden "0" output on Sumproduct formulas
 

Hello there!
I have two worksheets in a notebook:
One is the data source, the other is a lot of "sumproduct" formulas
tabulating totals.

Everything seemed to be working fine, then...
One of the rows in the tabulating worksheet started outputting zeros instead
of the totals it was giving me before.

Any idea what is happening?
(Below I state the problem in a different way:)

My tabulating worksheet looks like this (Column 'A' is labels, B and C
columns have formulas):
A B C
--|---------------------
1| P1 F1a F2a
2| P2 F1b F2b
3| P3 F1c F2c
4| P4 F1d F2d


This shows that the column B (sumproduct) formulas are exactly the same for
every 'P' except for one condition (i.e. factor).
Also, column C (sumproduct) formulas are exactly the same, except for one
condition/factor.
The factor (a, b, c, d....) that distinguishes P1's F1 formula from P2's F1
formula is the same factor that distinguishes P1's F2 formula from P2's F2
formula.

I did a sort on a section of data (that corresponding to P2) on the source
data worksheet, and upon calculating, all formulas in that row outputted "0".

I have tried reentering them, creating a new column, checked the source
data... I can't figure it out. It seems specifically to have to do with the
factor/condition in those formulas, although it was working fine previously.

Any leads are greatly appreciated!

Thanks!
--Rachel.

Sudden "0" output on Sumproduct formulas
 

Rachel wrote:
Everything seemed to be working fine, then...
One of the rows in the tabulating worksheet started outputting zeros instead
of the totals it was giving me before. Any idea what is happening?
[....]
I did a sort on a section of data (that corresponding to P2) on the source
data worksheet, and upon calculating, all formulas in that row outputted "0".

That was my guess! Sort does not update references to cells within the
sorted range. Sometimes you get lucky. But generally, you need to
find all the references (before the sort!) and update them after the
sort :-(. At least, that is what I have done. Perhaps someone can
suggest a better alternative. At this point, it might be very tedious
to do. If you are fortunate enough to have a recent-enough back-up, I
would go back to that, then work forward.

Sudden "0" output on Sumproduct formulas
 

What's the offending formula Rachel, and one that still works?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Rachel" wrote in message
...
Hello there!
I have two worksheets in a notebook:
One is the data source, the other is a lot of "sumproduct" formulas
tabulating totals.

Everything seemed to be working fine, then...
One of the rows in the tabulating worksheet started outputting zeros
instead
of the totals it was giving me before.

Any idea what is happening?
(Below I state the problem in a different way:)

My tabulating worksheet looks like this (Column 'A' is labels, B and C
columns have formulas):
A B C
--|---------------------
1| P1 F1a F2a
2| P2 F1b F2b
3| P3 F1c F2c
4| P4 F1d F2d


This shows that the column B (sumproduct) formulas are exactly the same
for
every 'P' except for one condition (i.e. factor).
Also, column C (sumproduct) formulas are exactly the same, except for one
condition/factor.
The factor (a, b, c, d....) that distinguishes P1's F1 formula from P2's
F1
formula is the same factor that distinguishes P1's F2 formula from P2's F2
formula.

I did a sort on a section of data (that corresponding to P2) on the source
data worksheet, and upon calculating, all formulas in that row outputted
"0".

I have tried reentering them, creating a new column, checked the source
data... I can't figure it out. It seems specifically to have to do with
the
factor/condition in those formulas, although it was working fine
previously.

Any leads are greatly appreciated!

Thanks!
--Rachel.

Sudden "0" output on Sumproduct formulas
 

An example of the formula that is returning "0" is:
=SUMPRODUCT((ELI!$D$3:$D$1699="012U")*(ELI!$U$3:$U $1699=0))

"012U" corresponds to "P2" in the simplified example I gave before... it's a
person's 'name'...
ELI! is my data source worksheet in the same notebook.

The same formula works when instead of "012U" I have "010U" or "102P" or any
other person's 'name' in that slot.
The same formula also works when I sort sections of data relevant to "010U",
"102P", etc...
All similar sumproduct formulas with "012U" [such as:
=SUMPRODUCT((ELI!$D$3:$D$1699="012U")*((ELI!$S$3:$ S$1699=12)+(ELI!$S$3:$S$1699=14))*(ELI!$V$3:$V$169 9=11)*(ELI!$U$3:$U$1699=0))]

also don't work...

Looking forward to your response.


"Bob Phillips" wrote:

What's the offending formula Rachel, and one that still works?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Rachel" wrote in message
...
Hello there!
I have two worksheets in a notebook:
One is the data source, the other is a lot of "sumproduct" formulas
tabulating totals.

Everything seemed to be working fine, then...
One of the rows in the tabulating worksheet started outputting zeros
instead
of the totals it was giving me before.

Any idea what is happening?
(Below I state the problem in a different way:)

My tabulating worksheet looks like this (Column 'A' is labels, B and C
columns have formulas):
A B C
--|---------------------
1| P1 F1a F2a
2| P2 F1b F2b
3| P3 F1c F2c
4| P4 F1d F2d


This shows that the column B (sumproduct) formulas are exactly the same
for
every 'P' except for one condition (i.e. factor).
Also, column C (sumproduct) formulas are exactly the same, except for one
condition/factor.
The factor (a, b, c, d....) that distinguishes P1's F1 formula from P2's
F1
formula is the same factor that distinguishes P1's F2 formula from P2's F2
formula.

I did a sort on a section of data (that corresponding to P2) on the source
data worksheet, and upon calculating, all formulas in that row outputted
"0".

I have tried reentering them, creating a new column, checked the source
data... I can't figure it out. It seems specifically to have to do with
the
factor/condition in those formulas, although it was working fine
previously.

Any leads are greatly appreciated!

Thanks!
--Rachel.

Sudden "0" output on Sumproduct formulas
 

Any chance that it's just a typo. Maybe it's a zero/oh thing in either the
formula or the data???

If you just used =countif() to test the stuff in column D, do you get something
bigger than 0?

If you apply data|filter|autofilter to that range. Filter to show 012U and then
=subtotal(9,u3:u1699), what's returned?

(I'd guess data problems--not formula.)

And check for leading/trailing spaces and text that looks like numbers.

Rachel wrote:

An example of the formula that is returning "0" is:
=SUMPRODUCT((ELI!$D$3:$D$1699="012U")*(ELI!$U$3:$U $1699=0))

"012U" corresponds to "P2" in the simplified example I gave before... it's a
person's 'name'...
ELI! is my data source worksheet in the same notebook.

The same formula works when instead of "012U" I have "010U" or "102P" or any
other person's 'name' in that slot.
The same formula also works when I sort sections of data relevant to "010U",
"102P", etc...
All similar sumproduct formulas with "012U" [such as:
=SUMPRODUCT((ELI!$D$3:$D$1699="012U")*((ELI!$S$3:$ S$1699=12)+(ELI!$S$3:$S$1699=14))*(ELI!$V$3:$V$169 9=11)*(ELI!$U$3:$U$1699=0))]

also don't work...

Looking forward to your response.

"Bob Phillips" wrote:

What's the offending formula Rachel, and one that still works?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Rachel" wrote in message
...
Hello there!
I have two worksheets in a notebook:
One is the data source, the other is a lot of "sumproduct" formulas
tabulating totals.

Everything seemed to be working fine, then...
One of the rows in the tabulating worksheet started outputting zeros
instead
of the totals it was giving me before.

Any idea what is happening?
(Below I state the problem in a different way:)

My tabulating worksheet looks like this (Column 'A' is labels, B and C
columns have formulas):
A B C
--|---------------------
1| P1 F1a F2a
2| P2 F1b F2b
3| P3 F1c F2c
4| P4 F1d F2d


This shows that the column B (sumproduct) formulas are exactly the same
for
every 'P' except for one condition (i.e. factor).
Also, column C (sumproduct) formulas are exactly the same, except for one
condition/factor.
The factor (a, b, c, d....) that distinguishes P1's F1 formula from P2's
F1
formula is the same factor that distinguishes P1's F2 formula from P2's F2
formula.

I did a sort on a section of data (that corresponding to P2) on the source
data worksheet, and upon calculating, all formulas in that row outputted
"0".

I have tried reentering them, creating a new column, checked the source
data... I can't figure it out. It seems specifically to have to do with
the
factor/condition in those formulas, although it was working fine
previously.

Any leads are greatly appreciated!

Thanks!
--Rachel.






--

Dave Peterson

Sudden "0" output on Sumproduct formulas
 

I considered and checked on the the zero/oh thing....the data is entered
correctly for sure.
Also, I'm pretty sure the formula is entered correctly because it is exactly
the same formula (copied and pasted) as I used in another cell the only
difference being that in the original cell instead of "012U", I have "010U".

Anyway, the formula was working (giving good output) until I did a sort on
the source data. The strange thing is that this sort only affected formulas
corresponding to this ONE person, containing the factor
(ELI!$D$3:$D$1699="012U").

Finally, when I do a COUNTIF, the "correct" value is being returned, so it
doesn't seem to be an error in the data source....

I'm not sure how to do a filter at the moment...
maybe you could another method of counting entries with the same two
criteria? I haven't been able to make nested if/countif's work for me,
although others have suggested it.

--Rachel.


"Dave Peterson" wrote:

Any chance that it's just a typo. Maybe it's a zero/oh thing in either the
formula or the data???

If you just used =countif() to test the stuff in column D, do you get something
bigger than 0?

If you apply data|filter|autofilter to that range. Filter to show 012U and then
=subtotal(9,u3:u1699), what's returned?

(I'd guess data problems--not formula.)

And check for leading/trailing spaces and text that looks like numbers.

Rachel wrote:

An example of the formula that is returning "0" is:
=SUMPRODUCT((ELI!$D$3:$D$1699="012U")*(ELI!$U$3:$U $1699=0))

"012U" corresponds to "P2" in the simplified example I gave before... it's a
person's 'name'...
ELI! is my data source worksheet in the same notebook.

The same formula works when instead of "012U" I have "010U" or "102P" or any
other person's 'name' in that slot.
The same formula also works when I sort sections of data relevant to "010U",
"102P", etc...
All similar sumproduct formulas with "012U" [such as:
=SUMPRODUCT((ELI!$D$3:$D$1699="012U")*((ELI!$S$3:$ S$1699=12)+(ELI!$S$3:$S$1699=14))*(ELI!$V$3:$V$169 9=11)*(ELI!$U$3:$U$1699=0))]

also don't work...

Looking forward to your response.

"Bob Phillips" wrote:

What's the offending formula Rachel, and one that still works?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Rachel" wrote in message
...
Hello there!
I have two worksheets in a notebook:
One is the data source, the other is a lot of "sumproduct" formulas
tabulating totals.

Everything seemed to be working fine, then...
One of the rows in the tabulating worksheet started outputting zeros
instead
of the totals it was giving me before.

Any idea what is happening?
(Below I state the problem in a different way:)

My tabulating worksheet looks like this (Column 'A' is labels, B and C
columns have formulas):
A B C
--|---------------------
1| P1 F1a F2a
2| P2 F1b F2b
3| P3 F1c F2c
4| P4 F1d F2d


This shows that the column B (sumproduct) formulas are exactly the same
for
every 'P' except for one condition (i.e. factor).
Also, column C (sumproduct) formulas are exactly the same, except for one
condition/factor.
The factor (a, b, c, d....) that distinguishes P1's F1 formula from P2's
F1
formula is the same factor that distinguishes P1's F2 formula from P2's F2
formula.

I did a sort on a section of data (that corresponding to P2) on the source
data worksheet, and upon calculating, all formulas in that row outputted
"0".

I have tried reentering them, creating a new column, checked the source
data... I can't figure it out. It seems specifically to have to do with
the
factor/condition in those formulas, although it was working fine
previously.

Any leads are greatly appreciated!

Thanks!
--Rachel.






--

Dave Peterson

Sudden "0" output on Sumproduct formulas
 

You could insert another column in your data:

=if(d3="012U",u3,"")

then drag down.

Then use =count() and =sum() to determine how many numbers you returned for that
column and what they added to.



Rachel wrote:

I considered and checked on the the zero/oh thing....the data is entered
correctly for sure.
Also, I'm pretty sure the formula is entered correctly because it is exactly
the same formula (copied and pasted) as I used in another cell the only
difference being that in the original cell instead of "012U", I have "010U".

Anyway, the formula was working (giving good output) until I did a sort on
the source data. The strange thing is that this sort only affected formulas
corresponding to this ONE person, containing the factor
(ELI!$D$3:$D$1699="012U").

Finally, when I do a COUNTIF, the "correct" value is being returned, so it
doesn't seem to be an error in the data source....

I'm not sure how to do a filter at the moment...
maybe you could another method of counting entries with the same two
criteria? I haven't been able to make nested if/countif's work for me,
although others have suggested it.

--Rachel.

"Dave Peterson" wrote:

Any chance that it's just a typo. Maybe it's a zero/oh thing in either the
formula or the data???

If you just used =countif() to test the stuff in column D, do you get something
bigger than 0?

If you apply data|filter|autofilter to that range. Filter to show 012U and then
=subtotal(9,u3:u1699), what's returned?

(I'd guess data problems--not formula.)

And check for leading/trailing spaces and text that looks like numbers.

Rachel wrote:

An example of the formula that is returning "0" is:
=SUMPRODUCT((ELI!$D$3:$D$1699="012U")*(ELI!$U$3:$U $1699=0))

"012U" corresponds to "P2" in the simplified example I gave before... it's a
person's 'name'...
ELI! is my data source worksheet in the same notebook.

The same formula works when instead of "012U" I have "010U" or "102P" or any
other person's 'name' in that slot.
The same formula also works when I sort sections of data relevant to "010U",
"102P", etc...
All similar sumproduct formulas with "012U" [such as:
=SUMPRODUCT((ELI!$D$3:$D$1699="012U")*((ELI!$S$3:$ S$1699=12)+(ELI!$S$3:$S$1699=14))*(ELI!$V$3:$V$169 9=11)*(ELI!$U$3:$U$1699=0))]

also don't work...

Looking forward to your response.

"Bob Phillips" wrote:

What's the offending formula Rachel, and one that still works?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Rachel" wrote in message
...
Hello there!
I have two worksheets in a notebook:
One is the data source, the other is a lot of "sumproduct" formulas
tabulating totals.

Everything seemed to be working fine, then...
One of the rows in the tabulating worksheet started outputting zeros
instead
of the totals it was giving me before.

Any idea what is happening?
(Below I state the problem in a different way:)

My tabulating worksheet looks like this (Column 'A' is labels, B and C
columns have formulas):
A B C
--|---------------------
1| P1 F1a F2a
2| P2 F1b F2b
3| P3 F1c F2c
4| P4 F1d F2d


This shows that the column B (sumproduct) formulas are exactly the same
for
every 'P' except for one condition (i.e. factor).
Also, column C (sumproduct) formulas are exactly the same, except for one
condition/factor.
The factor (a, b, c, d....) that distinguishes P1's F1 formula from P2's
F1
formula is the same factor that distinguishes P1's F2 formula from P2's F2
formula.

I did a sort on a section of data (that corresponding to P2) on the source
data worksheet, and upon calculating, all formulas in that row outputted
"0".

I have tried reentering them, creating a new column, checked the source
data... I can't figure it out. It seems specifically to have to do with
the
factor/condition in those formulas, although it was working fine
previously.

Any leads are greatly appreciated!

Thanks!
--Rachel.






--

Dave Peterson


--

Dave Peterson