Still having a prob counting the blocks of 1's

  • Thread starter Thread starter Bryan De-Lara
  • Start date Start date
B

Bryan De-Lara

I am still having problems counting blocks of 1's.
A few are returning the correct values, but others are seem to be adding 1
from the previous column.
The formula I'm using is..
=SUMPRODUCT(--(C1:C511=1),--(C2:C512<>1))

=SUMPRODUCT(--(D1:D511=1),--(D2:D512<>1))

=SUMPRODUCT(--(E1:E511=1),--(E2:E512<>1))

=SUMPRODUCT(--(F1:F511=1),--(F2:F512<>1))

=SUMPRODUCT(--(G1:G511=1),--(G2:G512<>1)) and so on.
The first formula is entered in D1 =SUMPRODUCT(--(C1:C511=1),--(C2:C512<>1))
and so on.
Col A B C D E F G H I J K L
1 3 4 2 0 0 0 0 0
2007 A B C D E F G H I J
02-Jan-07 2
3-Jan-07 3 1 1
4-Jan-07 4 1 1
Jan-07 5
Jan-07 8 1 1 1 1
Jan-07 9
Jan-07 10 1
Jan-07 11 1
Jan-07 12
Jan-07 15 1

A B C D E etc are the names of people.
The first line reports 1, correct. The second 3 but should report 2. It is
random where some are correct.
If I put the formula across the sheet, approx 82 people then enter a few 1's
most of the report will be 1's in all cells.
Very confusing for a novice. Any help greatly received.
 
Hi Bryan,

=SUMPRODUCT(--(C2:C512=1),--(C3:C513<>1))
entered into C1 or D1 and copied across seems to work.

Part of the problem you're having is that your formula references the first row in the previous column and, if the formula in that
cell returns a '1', its value gets added to the total for the next column.

Cheers
 
Thanks Macropod, trouble is if I enter it into C row I get a circular
problem.

Bryan.

macropod said:
Hi Bryan,

=SUMPRODUCT(--(C2:C512=1),--(C3:C513<>1))
entered into C1 or D1 and copied across seems to work.

Part of the problem you're having is that your formula references the
first row in the previous column and, if the formula in that cell returns
a '1', its value gets added to the total for the next column.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Bryan De-Lara said:
I am still having problems counting blocks of 1's.
A few are returning the correct values, but others are seem to be adding
1 from the previous column.
The formula I'm using is..
=SUMPRODUCT(--(C1:C511=1),--(C2:C512<>1))

=SUMPRODUCT(--(D1:D511=1),--(D2:D512<>1))

=SUMPRODUCT(--(E1:E511=1),--(E2:E512<>1))

=SUMPRODUCT(--(F1:F511=1),--(F2:F512<>1))

=SUMPRODUCT(--(G1:G511=1),--(G2:G512<>1)) and so on.
The first formula is entered in D1
=SUMPRODUCT(--(C1:C511=1),--(C2:C512<>1)) and so on.
Col A B C D E F G H I J K L
1 3 4 2 0 0 0 0 0
2007 A B C D E F G H I J
02-Jan-07 2
3-Jan-07 3 1 1
4-Jan-07 4 1 1
Jan-07 5
Jan-07 8 1 1 1 1
Jan-07 9
Jan-07 10 1
Jan-07 11 1
Jan-07 12
Jan-07 15 1

A B C D E etc are the names of people.
The first line reports 1, correct. The second 3 but should report 2. It
is random where some are correct.
If I put the formula across the sheet, approx 82 people then enter a few
1's most of the report will be 1's in all cells.
Very confusing for a novice. Any help greatly received.
Click to expand...
Click to expand...
 
If it is in C it cannot be anywhere in the ranges mentioned in the
SUMPRODUCT. So it cannot be in C2:C513
Please stay with one thread
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


Bryan De-Lara said:
Thanks Macropod, trouble is if I enter it into C row I get a circular
problem.

Bryan.

macropod said:
Hi Bryan,

=SUMPRODUCT(--(C2:C512=1),--(C3:C513<>1))
entered into C1 or D1 and copied across seems to work.

Part of the problem you're having is that your formula references the
first row in the previous column and, if the formula in that cell returns
a '1', its value gets added to the total for the next column.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Bryan De-Lara said:
I am still having problems counting blocks of 1's.
A few are returning the correct values, but others are seem to be adding
1 from the previous column.
The formula I'm using is..
=SUMPRODUCT(--(C1:C511=1),--(C2:C512<>1))

=SUMPRODUCT(--(D1:D511=1),--(D2:D512<>1))

=SUMPRODUCT(--(E1:E511=1),--(E2:E512<>1))

=SUMPRODUCT(--(F1:F511=1),--(F2:F512<>1))

=SUMPRODUCT(--(G1:G511=1),--(G2:G512<>1)) and so on.
The first formula is entered in D1
=SUMPRODUCT(--(C1:C511=1),--(C2:C512<>1)) and so on.
Col A B C D E F G H I J K L
1 3 4 2 0 0 0 0 0
2007 A B C D E F G H I J
02-Jan-07 2
3-Jan-07 3 1 1
4-Jan-07 4 1 1
Jan-07 5
Jan-07 8 1 1 1 1
Jan-07 9
Jan-07 10 1
Jan-07 11 1
Jan-07 12
Jan-07 15 1

A B C D E etc are the names of people.
The first line reports 1, correct. The second 3 but should report 2. It
is random where some are correct.
If I put the formula across the sheet, approx 82 people then enter a few
1's most of the report will be 1's in all cells.
Very confusing for a novice. Any help greatly received.
Click to expand...
Click to expand...
Click to expand...
 
Thanks Bernard, =SUMPRODUCT(--(C1:C511=1),--(C2:C512<>1)) is entered in D1
which checks row C, then the formula advance one i.e.
=SUMPRODUCT(--(D1:D511=1),--(D2:D512<>1)) which is entered in row E and so
on right up to row DH. Trouble is it seems to be also adding the row before.
Even if the row before is completely empty, I get a value of 1 in every
reporting cell where the formula is. Very confusing to me. I can understand
what the formula is doing, but not the adding of one or the entering of 1
when the row is completely empty.

Thanks.

Bryan.

Bernard Liengme said:
If it is in C it cannot be anywhere in the ranges mentioned in the
SUMPRODUCT. So it cannot be in C2:C513
Please stay with one thread
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


Bryan De-Lara said:
Thanks Macropod, trouble is if I enter it into C row I get a circular
problem.

Bryan.

macropod said:
Hi Bryan,

=SUMPRODUCT(--(C2:C512=1),--(C3:C513<>1))
entered into C1 or D1 and copied across seems to work.

Part of the problem you're having is that your formula references the
first row in the previous column and, if the formula in that cell
returns a '1', its value gets added to the total for the next column.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

I am still having problems counting blocks of 1's.
A few are returning the correct values, but others are seem to be
adding 1 from the previous column.
The formula I'm using is..
=SUMPRODUCT(--(C1:C511=1),--(C2:C512<>1))

=SUMPRODUCT(--(D1:D511=1),--(D2:D512<>1))

=SUMPRODUCT(--(E1:E511=1),--(E2:E512<>1))

=SUMPRODUCT(--(F1:F511=1),--(F2:F512<>1))

=SUMPRODUCT(--(G1:G511=1),--(G2:G512<>1)) and so on.
The first formula is entered in D1
=SUMPRODUCT(--(C1:C511=1),--(C2:C512<>1)) and so on.
Col A B C D E F G H I J K L
1 3 4 2 0 0 0 0 0
2007 A B C D E F G H I J
02-Jan-07 2
3-Jan-07 3 1 1
4-Jan-07 4 1 1
Jan-07 5
Jan-07 8 1 1 1 1
Jan-07 9
Jan-07 10 1
Jan-07 11 1
Jan-07 12
Jan-07 15 1

A B C D E etc are the names of people.
The first line reports 1, correct. The second 3 but should report 2. It
is random where some are correct.
If I put the formula across the sheet, approx 82 people then enter a
few 1's most of the report will be 1's in all cells.
Very confusing for a novice. Any help greatly received.
Click to expand...
Click to expand...
Click to expand...
 
By entering the formula in Row1 you are picking up the preceding column's
result.

If you enter this formula in D1 and drag/copy across you will get better
results.

=SUMPRODUCT(--(C2:C512=1),--(C3:C513<>1))

Just don't have anything in Row1 but the formulas.

Shift everything down by inserting a new row1 for your formulas..

BTW the letters C, D, E etc. are columns.........not rows.


Gord

Thanks Bernard, =SUMPRODUCT(--(C1:C511=1),--(C2:C512<>1)) is entered in D1
which checks row C, then the formula advance one i.e.
=SUMPRODUCT(--(D1:D511=1),--(D2:D512<>1)) which is entered in row E and so
on right up to row DH. Trouble is it seems to be also adding the row before.
Even if the row before is completely empty, I get a value of 1 in every
reporting cell where the formula is. Very confusing to me. I can understand
what the formula is doing, but not the adding of one or the entering of 1
when the row is completely empty.

Thanks.

Bryan.

Bernard Liengme said:
If it is in C it cannot be anywhere in the ranges mentioned in the
SUMPRODUCT. So it cannot be in C2:C513
Please stay with one thread
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


Bryan De-Lara said:
Thanks Macropod, trouble is if I enter it into C row I get a circular
problem.

Bryan.

Hi Bryan,

=SUMPRODUCT(--(C2:C512=1),--(C3:C513<>1))
entered into C1 or D1 and copied across seems to work.

Part of the problem you're having is that your formula references the
first row in the previous column and, if the formula in that cell
returns a '1', its value gets added to the total for the next column.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

I am still having problems counting blocks of 1's.
A few are returning the correct values, but others are seem to be
adding 1 from the previous column.
The formula I'm using is..
=SUMPRODUCT(--(C1:C511=1),--(C2:C512<>1))

=SUMPRODUCT(--(D1:D511=1),--(D2:D512<>1))

=SUMPRODUCT(--(E1:E511=1),--(E2:E512<>1))

=SUMPRODUCT(--(F1:F511=1),--(F2:F512<>1))

=SUMPRODUCT(--(G1:G511=1),--(G2:G512<>1)) and so on.
The first formula is entered in D1
=SUMPRODUCT(--(C1:C511=1),--(C2:C512<>1)) and so on.
Col A B C D E F G H I J K L
1 3 4 2 0 0 0 0 0
2007 A B C D E F G H I J
02-Jan-07 2
3-Jan-07 3 1 1
4-Jan-07 4 1 1
Jan-07 5
Jan-07 8 1 1 1 1
Jan-07 9
Jan-07 10 1
Jan-07 11 1
Jan-07 12
Jan-07 15 1

A B C D E etc are the names of people.
The first line reports 1, correct. The second 3 but should report 2. It
is random where some are correct.
If I put the formula across the sheet, approx 82 people then enter a
few 1's most of the report will be 1's in all cells.
Very confusing for a novice. Any help greatly received.
Click to expand...
Click to expand...
Click to expand...
 
Not using the formula I posted and following the instructions I gave ...

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Bryan De-Lara said:
Thanks Macropod, trouble is if I enter it into C row I get a circular
problem.

Bryan.

macropod said:
Hi Bryan,

=SUMPRODUCT(--(C2:C512=1),--(C3:C513<>1))
entered into C1 or D1 and copied across seems to work.

Part of the problem you're having is that your formula references the
first row in the previous column and, if the formula in that cell returns
a '1', its value gets added to the total for the next column.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Bryan De-Lara said:
I am still having problems counting blocks of 1's.
A few are returning the correct values, but others are seem to be adding
1 from the previous column.
The formula I'm using is..
=SUMPRODUCT(--(C1:C511=1),--(C2:C512<>1))

=SUMPRODUCT(--(D1:D511=1),--(D2:D512<>1))

=SUMPRODUCT(--(E1:E511=1),--(E2:E512<>1))

=SUMPRODUCT(--(F1:F511=1),--(F2:F512<>1))

=SUMPRODUCT(--(G1:G511=1),--(G2:G512<>1)) and so on.
The first formula is entered in D1
=SUMPRODUCT(--(C1:C511=1),--(C2:C512<>1)) and so on.
Col A B C D E F G H I J K L
1 3 4 2 0 0 0 0 0
2007 A B C D E F G H I J
02-Jan-07 2
3-Jan-07 3 1 1
4-Jan-07 4 1 1
Jan-07 5
Jan-07 8 1 1 1 1
Jan-07 9
Jan-07 10 1
Jan-07 11 1
Jan-07 12
Jan-07 15 1

A B C D E etc are the names of people.
The first line reports 1, correct. The second 3 but should report 2. It
is random where some are correct.
If I put the formula across the sheet, approx 82 people then enter a few
1's most of the report will be 1's in all cells.
Very confusing for a novice. Any help greatly received.
Click to expand...
Click to expand...
Click to expand...
 
I owe everyone a big apology. The reason I was getting the odd 1 added was
for a very silly reason.
What I'd done was to do a count of 1's at the bottom of the sheet and hidden
them, where I had only 1..1 then it would add that one to the final total.
Where the total of 1's was greater or less than 1 it was ignored.
I was jumping ahead of myself in trying to do as much as possible then
adding the block formula.
Thank you kind gents and ladies.

Bryan, a very happy chappy now....:)


macropod said:
Not using the formula I posted and following the instructions I gave ...

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Bryan De-Lara said:
Thanks Macropod, trouble is if I enter it into C row I get a circular
problem.

Bryan.

macropod said:
Hi Bryan,

=SUMPRODUCT(--(C2:C512=1),--(C3:C513<>1))
entered into C1 or D1 and copied across seems to work.

Part of the problem you're having is that your formula references the
first row in the previous column and, if the formula in that cell
returns a '1', its value gets added to the total for the next column.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

I am still having problems counting blocks of 1's.
A few are returning the correct values, but others are seem to be
adding 1 from the previous column.
The formula I'm using is..
=SUMPRODUCT(--(C1:C511=1),--(C2:C512<>1))

=SUMPRODUCT(--(D1:D511=1),--(D2:D512<>1))

=SUMPRODUCT(--(E1:E511=1),--(E2:E512<>1))

=SUMPRODUCT(--(F1:F511=1),--(F2:F512<>1))

=SUMPRODUCT(--(G1:G511=1),--(G2:G512<>1)) and so on.
The first formula is entered in D1
=SUMPRODUCT(--(C1:C511=1),--(C2:C512<>1)) and so on.
Col A B C D E F G H I J K L
1 3 4 2 0 0 0 0 0
2007 A B C D E F G H I J
02-Jan-07 2
3-Jan-07 3 1 1
4-Jan-07 4 1 1
Jan-07 5
Jan-07 8 1 1 1 1
Jan-07 9
Jan-07 10 1
Jan-07 11 1
Jan-07 12
Jan-07 15 1

A B C D E etc are the names of people.
The first line reports 1, correct. The second 3 but should report 2. It
is random where some are correct.
If I put the formula across the sheet, approx 82 people then enter a
few 1's most of the report will be 1's in all cells.
Very confusing for a novice. Any help greatly received.
Click to expand...
Click to expand...
Click to expand...
 
Good to hear.

Thanks for posting back with the results and fix.


Gord

I owe everyone a big apology. The reason I was getting the odd 1 added was
for a very silly reason.
What I'd done was to do a count of 1's at the bottom of the sheet and hidden
them, where I had only 1..1 then it would add that one to the final total.
Where the total of 1's was greater or less than 1 it was ignored.
I was jumping ahead of myself in trying to do as much as possible then
adding the block formula.
Thank you kind gents and ladies.

Bryan, a very happy chappy now....:)


macropod said:
Not using the formula I posted and following the instructions I gave ...

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

Bryan De-Lara said:
Thanks Macropod, trouble is if I enter it into C row I get a circular
problem.

Bryan.

Hi Bryan,

=SUMPRODUCT(--(C2:C512=1),--(C3:C513<>1))
entered into C1 or D1 and copied across seems to work.

Part of the problem you're having is that your formula references the
first row in the previous column and, if the formula in that cell
returns a '1', its value gets added to the total for the next column.

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

I am still having problems counting blocks of 1's.
A few are returning the correct values, but others are seem to be
adding 1 from the previous column.
The formula I'm using is..
=SUMPRODUCT(--(C1:C511=1),--(C2:C512<>1))

=SUMPRODUCT(--(D1:D511=1),--(D2:D512<>1))

=SUMPRODUCT(--(E1:E511=1),--(E2:E512<>1))

=SUMPRODUCT(--(F1:F511=1),--(F2:F512<>1))

=SUMPRODUCT(--(G1:G511=1),--(G2:G512<>1)) and so on.
The first formula is entered in D1
=SUMPRODUCT(--(C1:C511=1),--(C2:C512<>1)) and so on.
Col A B C D E F G H I J K L
1 3 4 2 0 0 0 0 0
2007 A B C D E F G H I J
02-Jan-07 2
3-Jan-07 3 1 1
4-Jan-07 4 1 1
Jan-07 5
Jan-07 8 1 1 1 1
Jan-07 9
Jan-07 10 1
Jan-07 11 1
Jan-07 12
Jan-07 15 1

A B C D E etc are the names of people.
The first line reports 1, correct. The second 3 but should report 2. It
is random where some are correct.
If I put the formula across the sheet, approx 82 people then enter a
few 1's most of the report will be 1's in all cells.
Very confusing for a novice. Any help greatly received.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top