This is my third entry today!

I have a data source and a worksheet that is "counting" with sumproduct
formulas the number of entries that meet specific criteria.
My problem: When I "change" the data source and recalculate the formula, the
output changes in unexpected ways. In particular, I can re-enter in the data
source a number, the exact same number as was in a cell previously, but the
formulas which want to count that number no longer seem to 'recognize' it.


For instance, with the following data set source, I want to count the number
of entries for which column B equals 0 and column C equals 2 or 3.

A B C
-------------------------------
1| entry1 0 2
2| entry2 0 3
3| entry3 0 4
4| entry4 1 2
5| entry5 1 3
6| entry6 1 4

I use a formula like: =SUMPRODUCT($B$1:$B$6=0)*(NOT($C$1:$C$6=4)
At first, I was getting the right output: "2"

Then, I would fidgit with the source data, say, by deleting the contents of
cell B1 ("0"), and re-entering the exact same value ("0").
When I update my sumproduct formula, it gives the output: "1".

I notice that if I delete and re-enter the value of C1 ("2"), however, this
does not happen. That is, it is only when I delete and re-enter "0" in the
data source that the formula seems to not recognize it as "0" anymore.

Does anyone know what's going on here?

Since this is my third issue today, I'm wondering if Excel gets too "heavy"
with formulas after a while and starts to malfunction.

Thanks for any help.
--Rachel.

My guess is that your column B contains numbers, but the cells are formatted
as text. Thus, your formula recognizes the numbers that are currently stored
there, but if you delete and reenter any of the values, the new value will be
text. Since your formula is looking for the number 0, it will not recognize
the text value 0.

Try changing the cell format of your Column B to "General" and see if that
fixes the problem.

HTH,
Elkar


"Rachel" wrote:

This is my third entry today!

I have a data source and a worksheet that is "counting" with sumproduct
formulas the number of entries that meet specific criteria.
My problem: When I "change" the data source and recalculate the formula, the
output changes in unexpected ways. In particular, I can re-enter in the data
source a number, the exact same number as was in a cell previously, but the
formulas which want to count that number no longer seem to 'recognize' it.


For instance, with the following data set source, I want to count the number
of entries for which column B equals 0 and column C equals 2 or 3.

A B C
-------------------------------
1| entry1 0 2
2| entry2 0 3
3| entry3 0 4
4| entry4 1 2
5| entry5 1 3
6| entry6 1 4

I use a formula like: =SUMPRODUCT($B$1:$B$6=0)*(NOT($C$1:$C$6=4)
At first, I was getting the right output: "2"

Then, I would fidgit with the source data, say, by deleting the contents of
cell B1 ("0"), and re-entering the exact same value ("0").
When I update my sumproduct formula, it gives the output: "1".

I notice that if I delete and re-enter the value of C1 ("2"), however, this
does not happen. That is, it is only when I delete and re-enter "0" in the
data source that the formula seems to not recognize it as "0" anymore.

Does anyone know what's going on here?

Since this is my third issue today, I'm wondering if Excel gets too "heavy"
with formulas after a while and starts to malfunction.

Thanks for any help.
--Rachel.