19 views

Skip to first unread message

Dec 31, 2002, 3:53:32 PM12/31/02

to

I've attached a dummy file with 9 records, each representing an individual.

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

Dec 31, 2002, 6:09:55 PM12/31/02

to

Never attach files to newsgroup posts (fortunately, you forgot to add

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.

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

Jan 1, 2003, 2:21:35 PM1/1/03

to

lb, ditto all Jerry's comments re attachments. If you

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.

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................

>.

>

Jan 1, 2003, 6:56:34 PM1/1/03

to

Hi Jerry, I sent an attachment, or meant to! as I've been asked to supply a

dummy file a couple of times in the past, however won't do that again!

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...

Jan 1, 2003, 7:23:06 PM1/1/03

to

Copied and pasted the Excel file, much tidier:

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

Jan 1, 2003, 9:05:04 PM1/1/03

to

Hi lb, (and your real names is?)

If I understand correctly, and my Array Formulas are correct as well

count of completed tasks, and incomplete tasks with multiple criteria.

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]

>

Jan 1, 2003, 10:31:12 PM1/1/03

to

In addition to John Walkenbach's tip 74 already mentioned,

Excel Developer Tip: Summing and Counting Using Mutliple Criteria

http://www.j-walk.com/ss/excel/tips/tip74.htm

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,

Jan 2, 2003, 12:36:46 AM1/2/03

to

Good grief, there I was about to close down for the night but checked for

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.

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...

Jan 2, 2003, 9:55:42 AM1/2/03

to

hi lb,

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..

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...

Jan 2, 2003, 3:29:30 PM1/2/03

to

Extending the formula you gave below in my dummy file works perfectly,

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.

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...

Jan 2, 2003, 4:37:16 PM1/2/03

to

CTRL SHIFT ENTER, yes I did. Where is the extra text in the second formula?

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...

Jan 2, 2003, 4:08:44 PM1/2/03

to

The formula seems to work. Did you Array Enter it?

The seond formula has some extra text in it.

The seond formula has some extra text in it.

"lb" <brys...@rogers.com> wrote in message

news:Ojv#t0psCHA.432@TK2MSFTNGP10...

Jan 2, 2003, 4:31:31 PM1/2/03

to

You have to enter as an Array Formula but if you change

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.

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...

Jan 2, 2003, 5:18:04 PM1/2/03

to

I've done F9 and have it set for automatic recalculation as you suggested.

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.

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...

Jan 2, 2003, 5:21:54 PM1/2/03

to

Wrong Dave, Dave (Patton not Peterson) <bg>

--

Regards,

Peo Sjoblom

"David McRitchie" <dmcri...@msn.com> wrote in message

news:uoPstaqsCHA.2344@TK2MSFTNGP10...

Jan 2, 2003, 5:48:17 PM1/2/03

to

Still hammering away at it - how about if you cut and paste yours to me,

just one more thing I can try to eliminate as a problem, if you still have

it that is.

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...

Jan 2, 2003, 8:26:41 PM1/2/03

to

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.

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...

Jan 2, 2003, 9:45:35 PM1/2/03

to

I have not lost track of what I'm asking for. There are 2 results which I'm

looking for, 620 and 267. I get 887 and 0.

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

Jan 2, 2003, 11:42:55 PM1/2/03

to

Hi Lou,

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.

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...

Jan 3, 2003, 11:53:14 AM1/3/03

to

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.

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...

Jan 3, 2003, 1:57:52 PM1/3/03

to

Hi Lou,

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.

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...

Jan 3, 2003, 2:43:49 PM1/3/03

to

"lb" wrote...

>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.

>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.

Jan 3, 2003, 5:21:47 PM1/3/03

to

Hi Harlan, thanks for jumping in. You're pretty close to what I'm looking

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.

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...

Jan 4, 2003, 12:20:42 AM1/4/03

to

"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<>"")

>open files

>

>=SUMPRODUCT((E2:E888<>"")*(Z2:Z888<>"")*(AC2:AC888<>"")<>"")

....

>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<>"")

>*(BD2:BD888<>"")*(BF2:BF888<>"")*(BH2:BH888<>"")

>*(BJ2:BJ888<>"")*(BL2:BL888<>"")*(BN2:BN888<>"")

>*(BP2:BP888<>"")*(BR2:BR888<>"")*(BT2:BT888<>""))>*(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.

Jan 4, 2003, 3:59:33 PM1/4/03

to

Hey everyone, thank you so much for all your help and to David M. as in

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.

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]

> ...

>

Reply all

Reply to author

Forward

0 new messages

Search

Clear search

Close search

Google apps

Main menu