counting blank cells in multi columns, small sample file attached
lb
The column headings represent different programs an individual can
participate in.
I want to determine those individuals whose file is closed (see headings),
as indicated by a closed date.
They're still Open if at least one program is still running, indicated with
a blank cell where the closed date would be.
My real file is linked to another sheet in the workbook, Apr02.
Thanks.
lb
Jerry W. Lewis
the threatened attachment). It greatly reduces your chance of getting
an answer since most knowledgeable users won't open them, and many users
entirely filter out posts with attachments. Attachments that do not
contain viruses still harm (in time or $ or both) users who have dial-up
connections or pay for connect time. Plus attachments are not archived
in Google.com, so the thread becomes a waste of space there since
subsequent readers cannot determine what the question was. A good
description usually communicates more effectively than an attachment anyway.
Look at help for COUNTBLANK(), and explain why that does not meet your
needs if it doesn't.
Jerry
Ken Wright
can just describe in words the layout of the records etc
with some example cell references then we should very
quickly be able to get you an answer.
Regards
Ken................
>.
>
lb
dummy file a couple of times in the past, however won't do that again!
My workbook has two sheets, one with my formulas for stats which is linked
to another sheet named "Apr02". I want to get a total that will tell me
how many individuals still have a program OPEN.
A B C D
E F G
NAME PROGRAM1 OPEN CLOSED PROGRAM2 OPEN
CLOSED
Joe Shop Jan-02 Mar-02
Music June-02 Sep-02
Fran Shop Jan-02
Music June-02 Sep-02
J.R. Shop Jan-02 Mar-02
Music June-02 Sep-02
Paul Shop Jan-02 Mar-02
Music June-02 Sep-02
The result I want will give me the number of individuals who are still
participating in a program. The sample above tells me that 3 people have
all CLOSED programs and 1 person has 1 program still OPEN.
I've formatted the CLOSED columns in RED. I've tried using COUNTBLANK and
tried COUNTBYCOLOR but still not getting it.
I hope this is clearer.
lb
"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
news:3E1223EA.4000904@no_e-mail.com...
lb
NAME program1 open closed program2 open closed
JOE shop Jan 02 Jan 02 music Jan 02 Jan 02
FRAN shop Feb 02 music Feb 02
J.R. shop Mar 02 Mar 02 music Mar 02 Mar 02
PAUL shop Apr 02 Apr 02 music Apr 02
=COUNTBLANK(D2:D5)+COUNTBLANK(G2:G5) gives me a total of 3. The problem
with that is it's counting all the blanks whereas I want a count of
INDIVIDUALS with programs open, not the number of PROGRAMS open, meaning the
total should be 2.
lb
David McRitchie
If I understand correctly, and my Array Formulas are correct as well
count of completed tasks, and incomplete tasks with multiple criteria.
Name class1 open completed class2 open completed
JOE shop Jan-2002 Jan-2002 music Jan-2002 Jan-2002
Fran shop Feb-2002 --- music Feb-2002 ---
J.R. shop Mar-2002 Mar-2002 music Mar-2002 Mar-2002
Paul shop Apr-2002 Apr-2002 music Apr-2002 ---
Meg shop Apr-2002 --- music Apr-2002 Mar-2002
Sara shop May-2002 --- music May-2002 May-2002
--- represent empty cells, specifically cells ="")
The following formulas are array formulas.
When you enter these formulas, use Ctrl+Shift+Enter:
number with completed coursework both column D and G have entries
2 =SUM((D2:D7<>"")*(G2:G7<>""))
number with incomplete coursework
4 =ROWS(D2:D7)-SUM((D2:D7<>"")*(G2:G7<>""))
Related information on John Wakenbach's site:
Excel Developer Tip: Summing and Counting Using Mutliple Criteria
http://www.j-walk.com/ss/excel/tips/tip74.htm
Plain text can be read by everyone, and this reply can be seen on
Google Groups, so people searching for answers may find this reply.
---
HTH, I expanded and changed your example a little bit.
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"lb" <brys...@rogers.com> wrote in message news:u5sRnSfsCHA.1632@TK2MSFTNGP12...
[clipped]
>
David McRitchie
Excel Developer Tip: Summing and Counting Using Mutliple Criteria
http://www.j-walk.com/ss/excel/tips/tip74.htm
you might also take a look at Chip Pearson's
Array Formulas
http://www.cpearson.com/excel/array.htm
Some additional sites found in Google
Pierre Leclerc's -- Excel-vba.com
Microsoft Excel Tips: Reporting
http://www.excel-vba.com/excel-tips-reporting.htm
Pierre makes a valid point using SumProduct instead of array formulas
what was I thinking of anyway. Here are the two formulas
I previously supplied rewritten as SumProduct formulas,
same as the arrary formulas but you don't have to use
Ctrl+Shift+Enter and they are easier to work with.
In fact I think the original question in this thread makes a
good example and you will be able to
find it on my site:
Summarizing Data Examples (an Overview)
http://www.mvps.org/dmcritchie/excel/sumdata.htm
number with completed coursework both column D and G have entries
2 =SUMPRODUCT((D2:D7<>"")*(G2:G7<>""))
number with incomplete coursework
4 =ROWS(D2:D7)-SUMPRODUCT((D2:D7<>"")*(G2:G7<>""))
WinPlanet - Tips - Using Excel's Array Formulas - Arrays of Light
http://www.winplanet.com/winplanet/tips/4045/1/
This one is listed last only because it will take you longer to go through
this in depth article by Bob Umlas
What Are Array Formulas
http://www.emailoffice.com/excel/arrays-bobumlas.html
---
HTH,
lb
any responses, tried your formulas and they work ! I can't tell you how
much help this will be for stuff I have to do, my actual file has several
hundred individuals.
One thing, in my actual file, I have a minimum of 6 programs people may be
in and will have up to 18 programs over the course of this year. Is there a
limit as to how many columns I can add to the formulas? Could you do one
more favor and show me what the formulas would look like with more than 2
columns?
Thanks so much and will indeed check out the links you supplied as well -
the one on multiple criteria looks really, really good.
lb (that's how I usually sign emails)
"David McRitchie" <dmcri...@msn.com> wrote in message
news:#CgW7NgsCHA.456@TK2MSFTNGP09...
David McRitchie
You're welcome. Getting a good night's sleep seldom hurts.
probably better to start working on a problem in the morning.
But one of the neater things about newsgroups is being able to
ask at night and get your answer in the morning, unless it is another
question because the question wasn't clear..
Previously had this formula.
number of individuals with incomplete coursework
4 =ROWS(D2:D7)-SUMPRODUCT((D2:D7<>"")*(G2:G7<>""))
Extending the formula for more courses (additional columns)
=ROWS(D2:D7)-SUMPRODUCT((D2:D7<>"")*(G2:G7<>"")*(I2:I7<>"")*(K2:K7<>"")*(M2:M7<>""))
as far as limits go there is no additional nesting, there is a
specification limit of 1,024 characters in a formula.
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"lb" <brys...@rogers.com> wrote in message news:#yK03BisCHA.2556@TK2MSFTNGP10...
lb
however when I apply it to my actual file, with 887 rows, it's not giving me
the correct answer nor is it refreshing when I change information.
Apologies for the length, however, this is what I'm trying to accomplish:
=ROWS(E2:E888)-SUM((E2:E888<>"")*(Z2:Z888<>"")*(AC2:AC888<>"")*(AF2:AF888<>"
")*(AI2:AI888<>"")*(AL2:AL888<>"")*(AN2:AN888<>"")*(AP2:AP888<>"")*(AR2:AR88
8<>"")*(AT2:AT888<>"")*(AV2:AV888<>"")*(AX2:AX888<>"")*(AZ2:AZ888<>"")*(BB2:
BB888<>"")*(BD2:BD888<>"")*(BF2:BF888<>"")*(BH2:BH888<>"")*(BJ2:BJ888<>"")*(
BL2:BL888<>"")*(BN2:BN888<>"")*(BP2:BP888<>"")*(BR2:BR888<>"")*(BT2:BT888<>"
"))
=SUM((E2:E888<>"")*(Z2:Z888<>"")*(AC2:AC888<>"")<>"")*(AF2:AF888<>"")*(AI2:A
I888<>"")*(AL2:AL888<>"")*(AN2:AN888<>"")*(AP2:AP888<>"")*(AR2:AR888<>"")*(A
T2:AT888<>"")*(AV2:AV888<>"")*(AX2:AX888<>"")*(AZ2:AZ888<>"")*(BB2:BB888<>""
)*(BD2:BD888<>"")*(BF2:BF888<>"")*(BH2:BH888<>"")*(BJ2:BJ888<>"")*(BL2:BL888
<>"")*(BN2:BN888<>"")*(BP2:BP888<>"")*(BR2:BR888<>"")*(BT2:BT888<>""))
I've double checked everything and can't figure out what the problem is
after 2 hours of fiddling. Any suggestions?
lb
"David McRitchie" <dmcri...@msn.com> wrote in message
news:OGX0u8msCHA.440@TK2MSFTNGP12...
lb
lb
"Dave Patton" <dgpat...@telus.net> wrote in message
news:O1LILOqsCHA.2352@TK2MSFTNGP09...
> The formula seems to work. Did you Array Enter it?
> The seond formula has some extra text in it.
>
>
>
> "lb" <brys...@rogers.com> wrote in message
> news:Ojv#t0psCHA.432@TK2MSFTNGP10...
Dave Patton
The seond formula has some extra text in it.
"lb" <brys...@rogers.com> wrote in message
news:Ojv#t0psCHA.432@TK2MSFTNGP10...
David McRitchie
SUM to SUMPRODUCT you can enter as a regular formulas.
I get something like 380 characters in the formula, a long way
from the 1024 character specification limit.
Try Recalculating F9, if that works make sure you have
tools, General, calculation: automatic
if that doesn't cause recalculation, bring in the heavy artillery:
Ctrl+Alt+F9 Recalculates all cells on all worksheets in all open workbooks.
I don't see the extra text that Dave Peterson said you have.
Suspect Dave got an extra ">" from broken lines when he copied
your formula, both formulas work for me.
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"lb" <brys...@rogers.com> wrote in message news:Ojv#t0psCHA.432@TK2MSFTNGP10...
lb
The count should be 620 and 267, however it is not refreshing and the count
remains at 887 and 0. Can't understand as I've copied you the exact same
formulas. Totally stumped. Like I said before tho, my dummy file works - I
have 6 columns, 70 rows that it's calculating on and there's no problem.
lb
"David McRitchie" <dmcri...@msn.com> wrote in message
news:uoPstaqsCHA.2344@TK2MSFTNGP10...
Peo Sjoblom
--
Regards,
Peo Sjoblom
"David McRitchie" <dmcri...@msn.com> wrote in message
news:uoPstaqsCHA.2344@TK2MSFTNGP10...
lb
just one more thing I can try to eliminate as a problem, if you still have
it that is.
Thanks
lb
"David McRitchie" <dmcri...@msn.com> wrote in message
news:uoPstaqsCHA.2344@TK2MSFTNGP10...
David McRitchie
I used a concatenate to bring them together then reentered so it
would be a formula. I got my formula from your posting so don't know
what you expect if I paste the formula I used.
The second formula with the ROWS and then subtracting
the SUMPRODUCT is the number of incompletes.
Basically if you have anything that looks like a blank in
cells E2:BT888 then that row will not be subtracted because
it is imcomplete.
I suspect that you've lost track of what you asked for and
can't find the problem because it is working correctly.
Number of incomplete project rows:
=ROWS(E2:E888)-SUMPRODUCT((E2:E888<>"")*(Z2:Z888<>"")*(AC2:AC888<>"")*(AF2:AF888<>"")*(AI2:AI888<>"")*(AL2:AL888<>"")*(AN2:AN888<>""
)*(AP2:AP888<>"")*(AR2:AR888<>"")*(AT2:AT888<>"")*(AV2:AV888<>"")*(AX2:AX888<>"")*(AZ2:AZ888<>"")*(BB2:BB888<>"")*(BD2:BD888<>"")*(B
F2:BF888<>"")*(BH2:BH888<>"")*(BJ2:BJ888<>"")*(BL2:BL888<>"")*(BN2:BN888<>"")*(BP2:BP888<>"")*(BR2:BR888<>"")*(BT2:BT888<>""))
your cell columns are not equal distant apart
Cell col diff
E2 5
Z2 26 21
AC2 29 3
AF2 32 3
AI2 35 3
AL2 38 3
AN2 40 2
AP2 42 2
AR2 44 2
AT2 46 2
AV2 48 2
AX2 50 2
AZ2 52 2
BB2 54 2
BD2 56 2
BF2 58 2
BH2 60 2
BJ2 62 2
BL2 64 2
BN2 66 2
BP2 68 2
BR2 70 2
BT2 72 2
bj2 62 2
bl2 64 2
bn2 66 2
bp2 68 2
br2 70 2
bt2 72 2
I tested for 887 rows (row 2 to 888) with all of cells
e2:VT888 filled in with the character "x", result would be 0
e2:BT888 empty, result would be 887
I tested also for 29 rows (row 2 to 30) rows instead of 888
Select a row then use Ctrl+RtArrow to find actual empty cell
you will select the cell to the left of the empty cell, then to the
right of the empty cell(s). If you find any empty cells in the
columns you checked then that row is incomplete.
You may need to change the number of rows sometime.
Sub ChgSTRinFormulas()
Dim cell As Range
Dim FromStr As String, ToStr As String
If Intersect(Selection, Selection.SpecialCells(xlFormulas, 23)) Is Nothing Then Exit Sub
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlFormulas, 23))
FromStr = "30"
ToStr = "888"
cell.Formula = Replace(cell.Formula, FromStr, ToStr, 1, -1, vbTextCompare)
Next cell
End Sub
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"lb" <brys...@rogers.com> wrote in message news:OR17QCrsCHA.1776@TK2MSFTNGP10...
lb
looking for, 620 and 267. I get 887 and 0.
I have 887 rows, all different individuals. The columns I'm referring to
are the CLOSED columns.
Not every individual participates in all programs so there are many blank
cells for that reason but minimally participates in 1 program.
There would also be blank cells in these columns for those who are still
involved in a particular program and therefore I haven't entered a closed
date yet.
I want to get a count of those individuals who are currently participating
in at least 1 program (the cell would be blank) and a count of those who
have completed at least 1 program.
Adding the 2 counts should add up to 887.
I don't know if this makes a difference if cells are blank. A blank cell
perhaps to be clearer can indicate 2 things:
1. they never participated in that program
2. they are currently participating and the program close date has not yet
been entered
With the same formulas that I've posted, I get a count of 887 with ROWS
SUMPRODUCT and a count of 0(zero) for the other formula. They of course add
up to 887 as they should however doing an long manual count, the totals
should be 620 closed and 267 open.
I'm not sure what you mean by cell columns not equal distant apart, or
changing the number of rows.
I've created a dummy file which does work. I've gone over my real file and
cannot see what the problem is.
Thanks for sticking with it.
lb
"David McRitchie" <dmcri...@msn.com> wrote in message
news:#jZdQessCHA.2176@TK2MSFTNGP12...
> I pasted your formula into excel, it broke up into about 5 rows in excel
> I used a concatenate to bring them together then reentered so it
> would be a formula. I got my formula from your posting so don't know
> what you expect if I paste the formula I used.
>
> The second formula with the ROWS and then subtracting
> the SUMPRODUCT is the number of incompletes.
> Basically if you have anything that looks like a blank in
> cells E2:BT888 then that row will not be subtracted because
> it is imcomplete.
>
> I suspect that you've lost track of what you asked for and
> can't find the problem because it is working correctly.
>
> Number of incomplete project rows:
>
>
=ROWS(E2:E888)-SUMPRODUCT((E2:E888<>"")*(Z2:Z888<>"")*(AC2:AC888<>"")*(AF2:A
David McRitchie
I don't think you are matching what you originally indicated,
but it is making more sense that all of those tasks are not
to be completed by everyone. In your original example
you showed that each task had a start for everyone.
Create another column -- I used column C for my test,
the formula in C2 must be copied down to C888
which I think is something you would want to be visible anyway.
Your new formula is for partial complete
Cell C2: (copy formula down through C888)
=COUNTA(E2,Z2,AC2,AF2,AI2,AL2,AN2,AP2, AR2,AT2, AV2, AX2, AZ2, BB2, BD2, BF2, BH2, BJ2, BL2, BN2, BP2, BR2, BT2)
for those columns above (completion dates) you might want to set up validation that you can't
enter a value unless the column to the left (start) exists.
Your new formula for started tasks
I suspect you want a another formula to copy down B2
same as C2 but one cell to the left of each.
=COUNTA(D2, Y2, ....etc...)
You completion would not be the total of the completion cells but the completion
cells that have start.
=B2=C2 (true would indicate all that were started were completed)
You can sum up those above zero with COUNTIF
=COUNTIF(B2:B888,">0") -- untested
incomplete -- THIS IS GARBAGE because nobody does all of them
=ROWS(A2:A888)-SUMPRODUCT((E2:E888<>"")*(Z2:Z888<>"")*(AC2:AC888<>"")*(AF2:AF888<>"")*(AI2:AI888<>"")*(AL2:AL888<>"")*(AN2:AN888<>""
)*(AP2:AP888<>"")*(AR2:AR888<>"")*(AT2:AT888<>"")*(AV2:AV888<>"")*(AX2:AX888<>"")*(AZ2:AZ888<>"")*(BB2:BB888<>"")*(BD2:BD888<>"")*(B
F2:BF888<>"")*(BH2:BH888<>"")*(BJ2:BJ888<>"")*(BL2:BL888<>"")*(BN2:BN888<>"")*(BP2:BP888<>"")*(BR2:BR888<>"")*(BT2:BT888<>""))
complete -- THIS IS GARBAGE because nobody does all of them
=SUMPRODUCT((E2:E888<>"")*(Z2:Z888<>"")*(AC2:AC888<>"")*(AF2:AF888<>"")*(AI2:AI888<>"")*(AL2:AL888<>"")*(AN2:AN888<>"")*(AP2:AP888<>
"")*(AR2:AR888<>"")*(AT2:AT888<>"")*(AV2:AV888<>"")*(AX2:AX888<>"")*(AZ2:AZ888<>"")*(BB2:BB888<>"")*(BD2:BD888<>"")*(BF2:BF888<>"")*
(BH2:BH888<>"")*(BJ2:BJ888<>"")*(BL2:BL888<>"")*(BN2:BN888<>"")*(BP2:BP888<>"")*(BR2:BR888<>"")*(BT2:BT888<>""))
rows:
=ROWS(E2:E888)
individuals with at least one task started
=COUNTIF(B2:B888,">0")
partial completion
=COUNTIF(C2:C888,">0")
So that I can test your formulas here is an additional macro::
Sub Show_LP()
Columns("E:IV").EntireColumn.Hidden = True
Range("e:e, z:z, ac:ac, AF:AF, AI:AI, AL:AL, AN:AN, " _
& "AP:AP, AR:AR, AT:AT, AV:AV, AX:AX").EntireColumn.Hidden = False
Range("AX:AX, AZ:AZ, BB:BB, BD:BD, BF:BF, BH:BH, BJ:BJ, BL:BL, BN:BN, " _
& "BP:BP, BR:BR, BT:BT").EntireColumn.Hidden = False
End Sub
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"lb" <brys...@rogers.com> wrote in message news:u5Zn2GtsCHA.2296@TK2MSFTNGP10...
lb
them, to date, have completed the program but will eventually. Others have
done several programs, not all of which, to date, are completed but will be
eventually.
The problem with adding new columns is that my formulas, over a 100 of them,
are linked to another sheet in the book. I understand that if I add new
columns, that messes up my formulas. I can't eliminate any of the columns,
it's all data that is necessarily collected.
My bottom line is I know there are 887 individuals/rows and I just need to
know for each person, who is closed and who is open. Each person only gets
counted once.
I can get these 2 figures in my dummy file but not in my actual file. Those
are the big formulas that I posted earlier and was hoping that someone could
catch a glitch in them because I sure as heck can't see it.
Not sure why it's garbage, that no one does all of them?
Afraid I know nothing about using macros.
lb
"David McRitchie" <dmcri...@msn.com> wrote in message
news:OsAErMusCHA.1628@TK2MSFTNGP10...
David McRitchie
Yes that's the reason it's garbage is because not all tasks
are done by everyone. You have to have a well defined
problem before starting. Previous answers were based on
an incorrect premise/definition. And also one needs a
good example to work with that is small and covers all
possibilities that you can think of. Much easier to work
with this information ahead of time rather than getting
it piecemeal late on.
As indicated specifically to you in email, but now for others
an example can be found in
Summarizing Data Examples (an Overview)
http://www.mvps.org/dmcritchie/excel/sumdata.htm
topic: COUNTA, COUNTIF Example
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"lb" <brys...@rogers.com> wrote in message news:O6lggg0sCHA.1644@TK2MSFTNGP12...
Harlan Grove
>Each person, all 887 of them, have started minimally 1 program, not all of
>them, to date, have completed the program but will eventually. Others have
>done several programs, not all of which, to date, are completed but will be
>eventually.
>
>The problem with adding new columns is that my formulas, over a 100 of them,
>are linked to another sheet in the book. I understand that if I add new
>columns, that messes up my formulas. I can't eliminate any of the columns,
>it's all data that is necessarily collected.
>
>My bottom line is I know there are 887 individuals/rows and I just need to
>know for each person, who is closed and who is open. Each person only gets
>counted once.
Jumping in. If you have a table of 888 rows (topmost containing headings,
followed by 887 rows for individuals), and N different programs where each
program fills 3 columns (program name, open date and close date) plus a leftmost
column of the individuals' names, so 3*N+1 columns, and this table starts in A1
and N were, say, 12, so the entire table spanned A1:AK888, and 'open' programs
requre the program's open column to contain a date but the corresponding closed
column to be empty, then you could use the formulas
AL2:
=SUMPRODUCT((MOD(COLUMN($B2:$AK2),3)=0)*(B2:AK2>0))
AM2:
=SUMPRODUCT((MOD(COLUMN($B2:$AK2),3)=1)*($B2:$AK2>0))
AN2:
=AL2-AM2
so AL2 gives the number of programs undertaken by the first person in the list,
AM2 gives the number of programs completed by that person, and AN2 gives the
number of open programs for that person. Copy AL2:AN2 and paste into AL3:AN888.
=SUM(AL2:AL888) gives the total number of programs undertaken
=SUM(AM2:AM888) gives the total number of programs completed
=SUM(AN2:AN888) gives the total number of open programs
=COUNTIF(AL2:AL888,">0") gives the number of people who have commenced programs
=COUNTIF(AM2:AM888,">0") gives the number of people who have completed programs
=COUNTIF(AN2:AN888,">0") gives the number of people who have open programs
To be honest, this is ideally a database operation, and database queries would
easier to construct to give you the answers you seek.
--
Public Service Announcement
Don't attach files to postings in nonbinary newsgroups like this one.
lb
for. I've broken down your comments and indicated 'CORRECT' or other
comments underneath them. I don't want to mess with where the columns are
placed in the worksheet the formulas are on another sheet and are linked.
"Jumping in. If you have a table of 888 rows (topmost containing headings,
followed by 887 rows for individuals),
CORRECT
and N different programs
N=23
where each
> program fills 3 columns (program name, open date and close date) plus a
leftmost column of the individuals' names,
CORRECT. ACTUALLY FIRST 2 COLUMNS ARE 'LAST NAME', 'FIRST NAME'.
so 3*N+1 columns, and this table starts in A1 and N were, say, 12, so the
entire table spanned A1:AK888,
STARTS IN A1 AND GOES TO BX888, N=23 on the columns I've got in the
formulas.
and 'open' programs
> requre the program's open column to contain a date but the corresponding
closed
> column to be empty,
THE CORRESPONDING CLOSED DATE CAN BE EMPTY BUT WILL EVENTUALLY CONTAIN A
DATE.
Okay, again I have to say that I have successfully achieved the results I
want in the way that I want them in a dummy file I set up. I've tested it
multiple times, adding rows, deleting rows, deleting open and closed dates,
and it always works. The range is A2:S7, 6 individuals. The results are 5
open, 1 closed. Here are the formulas:
=ROWS($D$2:$D$7)-SUMPRODUCT(($D$2:$D$7<>"")*($G$2:$G$7<>"")*($J$2:$J$7<>"")*
($M$2:$M$7<>"")*($P$2:$P$7<>"")*($S$2:$S$7<>""))
open files
=SUMPRODUCT(($D$2:$D$7<>"")*($G$2:$G$7<>"")*($J$2:$J$7<>"")*($M$2:$M$7<>"")*
($P$2:$P$7<>"")*($S$2:$S$7<>""))
closed files
In my actual file, 887 records, range A2:BX888, and amending the columns to
include:
E, Z, AC, AF, AI, AL, AN, AP, AR, AT, AV, AX, AZ, BB, BD, BF, BH, BJ, BL,
BN, BP, BR, BT
and I get the results 887 open, 0 closed, whereas doing a tedious manual
count, I know it should be 267 open, 620 closed.
I don't want to do this any other way as I know it can work. I need help in
finding out what is wrong with these 2 formulas:
=ROWS(E2:E888)-SUMPRODUCT((E2:E888<>"")*(Z2:Z888<>"")*(AC2:AC888<>"")*(AF2:A
F888<>"")*(AI2:AI888<>"")*(AL2:AL888<>"")*(AN2:AN888<>"")*(AP2:AP888<>"")*(A
R2:AR888<>"")*(AT2:AT888<>"")*(AV2:AV888<>"")*(AX2:AX888<>"")*(AZ2:AZ888<>""
)*(BB2:BB888<>"")*(BD2:BD888<>"")*(BF2:BF888<>"")*(BH2:BH888<>"")*(BJ2:BJ888
<>"")*(BL2:BL888<>"")*(BN2:BN888<>"")*(BP2:BP888<>"")*(BR2:BR888<>"")*(BT2:B
T888<>""))
open files
=SUMPRODUCT((E2:E888<>"")*(Z2:Z888<>"")*(AC2:AC888<>"")<>"")*(AF2:AF888<>"")
*(AI2:AI888<>"")*(AL2:AL888<>"")*(AN2:AN888<>"")*(AP2:AP888<>"")*(AR2:AR888<
>"")*(AT2:AT888<>"")*(AV2:AV888<>"")*(AX2:AX888<>"")*(AZ2:AZ888<>"")*(BB2:BB
888<>"")*(BD2:BD888<>"")*(BF2:BF888<>"")*(BH2:BH888<>"")*(BJ2:BJ888<>"")*(BL
2:BL888<>"")*(BN2:BN888<>"")*(BP2:BP888<>"")*(BR2:BR888<>"")*(BT2:BT888<>"")
I have tried both SUM and SUMPRODUCT.
I'm trying to get this resolved by Sunday evening so I can go back to work
and not obess! :)
Thanks again.
lb
"Harlan Grove" <hrl...@aol.com> wrote in message
news:VJlR9.3646$15....@www.newsranger.com...
Harlan Grove
....
>I don't want to do this any other way as I know it can work. I need help
in
>finding out what is wrong with these 2 formulas:
>
>=ROWS(E2:E888)-SUMPRODUCT((E2:E888<>"")*(Z2:Z888<>"")
>*(AL2:AL888<>"")*(AN2:AN888<>"")*(AP2:AP888<>"")
>*(AR2:AR888<>"")*(AT2:AT888<>"")*(AV2:AV888<>"")
>*(AX2:AX888<>"")*(AZ2:AZ888<>"")*(BB2:BB888<>"")
>*(BJ2:BJ888<>"")*(BL2:BL888<>"")*(BN2:BN888<>"")
>open files
>
>=SUMPRODUCT((E2:E888<>"")*(Z2:Z888<>"")*(AC2:AC888<>"")<>"")
The <>"")<>"") is a typo? (Should be just <>"") at the end.)
>*(AF2:AF888<>"")*(AI2:AI888<>"")*(AL2:AL888<>"")
>*(AN2:AN888<>"")*(AP2:AP888<>"")*(AR2:AR888<>"")
>*(AT2:AT888<>"")*(AV2:AV888<>"")*(AX2:AX888<>"")
>*(AZ2:AZ888<>"")*(BB2:BB888<>"")*(BD2:BD888<>"")
>*(BF2:BF888<>"")*(BH2:BH888<>"")*(BJ2:BJ888<>"")
>*(BL2:BL888<>"")*(BN2:BN888<>"")*(BP2:BP888<>"")
>*(BR2:BR888<>"")*(BT2:BT888<>"")
[closed files]
....
Almost certainly these cells aren't blank or don't contain zero-length
strings. If closed files have date entries in these cells (i.e., numeric
entries), change the formula for closed files to
=SUMPRODUCT(ISNUMBER(E2:E888)*ISNUMBER(Z2:Z888)
*ISNUMBER(AC2:AC888)*ISNUMBER(AF2:AF888)
*ISNUMBER(AI2:AI888)*ISNUMBER(AL2:AL888)
*ISNUMBER(AN2:AN888)*ISNUMBER(AP2:AP888)
*ISNUMBER(AR2:AR888)*ISNUMBER(AT2:AT888)
*ISNUMBER(AV2:AV888)*ISNUMBER(AX2:AX888)
*ISNUMBER(AZ2:AZ888)*ISNUMBER(BB2:BB888)
*ISNUMBER(BD2:BD888)*ISNUMBER(BF2:BF888)
*ISNUMBER(BH2:BH888)*ISNUMBER(BJ2:BJ888)
*ISNUMBER(BL2:BL888)*ISNUMBER(BN2:BN888)
*ISNUMBER(BP2:BP888)*ISNUMBER(BR2:BR888)
*ISNUMBER(BT2:BT888)
and the formula for open files to
==ROWS(E2:E888)-X99
where X99 is a placeholder for the cell address of the cell containing the
formula for closed files.
lb
following his table examples, I got what I needed! However, for all the
other suggestions, I still learned quite a few new tricks which I can apply
to other things.
lb
"Harlan Grove" <hrl...@aol.com> wrote in message
news:#$#ucE7sCHA.1816@TK2MSFTNGP10...
> "lb" <brys...@rogers.com> wrote...
> ...
> >I don't want to do this any other way as I know it can work. I need help
> in
> >finding out what is wrong with these 2 formulas:
> >
> >=ROWS(E2:E888)-SUMPRODUCT((E2:E888<>"")*(Z2:Z888<>"")
> >*(AC2:AC888<>"")*(AF2:AF888<>"")*(AI2:AI888<>"")
> >*(AL2:AL888<>"")*(AN2:AN888<>"")*(AP2:AP888<>"")
> >*(AR2:AR888<>"")*(AT2:AT888<>"")*(AV2:AV888<>"")
> >*(AX2:AX888<>"")*(AZ2:AZ888<>"")*(BB2:BB888<>"")
> >*(BD2:BD888<>"")*(BF2:BF888<>"")*(BH2:BH888<>"")
> >*(BJ2:BJ888<>"")*(BL2:BL888<>"")*(BN2:BN888<>"")
> >*(BP2:BP888<>"")*(BR2:BR888<>"")*(BT2:BT888<>""))
> >open files
> >
> >=SUMPRODUCT((E2:E888<>"")*(Z2:Z888<>"")*(AC2:AC888<>"")<>"")
>
> The <>"")<>"") is a typo? (Should be just <>"") at the end.)
>
> >*(AF2:AF888<>"")*(AI2:AI888<>"")*(AL2:AL888<>"")
> >*(AN2:AN888<>"")*(AP2:AP888<>"")*(AR2:AR888<>"")
> >*(AT2:AT888<>"")*(AV2:AV888<>"")*(AX2:AX888<>"")
> >*(AZ2:AZ888<>"")*(BB2:BB888<>"")*(BD2:BD888<>"")
> >*(BF2:BF888<>"")*(BH2:BH888<>"")*(BJ2:BJ888<>"")
> >*(BL2:BL888<>"")*(BN2:BN888<>"")*(BP2:BP888<>"")
> >*(BR2:BR888<>"")*(BT2:BT888<>"")
> [closed files]
> ...
>