Still having a prob counting the blocks of 1's
Bryan De-Lara
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.
macropod
=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" <bryan.d...@btinternet.com> wrote in message news:%23FLBhAV...@TK2MSFTNGP03.phx.gbl...
Bryan De-Lara
problem.
Bryan.
"macropod" <inv...@invalid.invalid> wrote in message
news:e1aTswVc...@TK2MSFTNGP05.phx.gbl...
Bernard Liengme
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" <bryan.d...@btinternet.com> wrote in message
news:u53Fc5Vc...@TK2MSFTNGP03.phx.gbl...
Bryan De-Lara
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" <blie...@stfx.TRUENORTH.ca> wrote in message
news:etuXvQW...@TK2MSFTNGP02.phx.gbl...
Gord Dibben
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
macropod
Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------
"Bryan De-Lara" <bryan.d...@btinternet.com> wrote in message news:u53Fc5Vc...@TK2MSFTNGP03.phx.gbl...
Bryan De-Lara
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" <inv...@invalid.invalid> wrote in message
news:OQN$KYbcIH...@TK2MSFTNGP05.phx.gbl...
Gord Dibben
Good to hear.
Thanks for posting back with the results and fix.
Gord