formula question for arrays
 

I have a group of cells with dates in them set up as so
4/8/2004 4/26/2004 Cycle 1
4/27/2004 5/4/2004 Cycle 2
5/5/2004 5/11/2004 Cycle 3

etc etc etc

FIND PLOGS CLOSED DURING A SPECIFIC TIME PERIOD
Then I have a query to sum the number of "All Products" with the criteria of
"Closed Plogs" and "Show Stopper" and the date value in column "H" if ="a
date" and then <= "a date" as shown below
{=SUM(IF(Closed_Plogs!$A$2:$A$3000="All
Products",1,0)*IF(Closed_Plogs!$L$2:$L$3000="Show
Stopper",1,0)*IF(Closed_Plogs!$H$2:$H$3000=VALUE( "4/08/2004"),1,0)*IF(Close
d_Plogs!$H$2:$H$3000<=VALUE("4/26/2004"),1,0))}

FIND PLOGS OPENED DURING A SPECIFIC TIME PERIOD
I have another query to the sum the number of "All Products" with the
criteria of "show stopper" and date value criteria as shown below
{=SUM(IF(All_Plogs!$A$2:$A$3000="All
Products",1,0)*IF(All_Plogs!$L$2:$L$3000="Show
Stopper",1,0)*IF(All_Plogs!$B$2:$B$3000=VALUE("4/08/2004"),1,0)*IF(All_Plog
s!$B$2:$B$3000<=VALUE("4/26/2004"),1,0))}

I say all of this to I now need to take the same data and now I need to know
which cycle the plog was opened in and which cycle it was closed in, not the
specific Plog, just a count of when they were opened and closed. The above
formulas are set up in cycles in the spreadsheets and tell me how many were
closed during a cycle and how many were opened during a cycle, however, I am
having a real difficult time coming up with the correct formula

I would have a column for Open C1 Closed C1
column for Open C1 Closed C2
column for Open C1 Closed C3
column for Open C2 Closed C2
column for Open C2 Closed C3
column for Open C3 Closed C3

Well I have rambled on and on trying to explain, I hope this makes sense and
I appreciate any and all help. You guys are the greatest.
--
Thanks,
Cynthia G.

"Information is just signs and numbers, while knowledge
involves their meaning. What we want is knowledge, but what
we get is information."
--Heinz R. Pagels

formula question for arrays
 

Assume it says Cycle1 in C for when opened and Cycle2 in D for when closed
as an example.
*IF(All_Plogs!$C$2:$C$3000="Cycle1",1,0)*IF(All_Pl ogs!$SD$2:$D$3000="Cycle2"
,1,0)*

Add the above condition to the appropriate other conditions.

If not that, then you would need a better explanation of how cycle open and
cycle close are indicated for a single plot.

--
Regards,
Tom Ogilvy


"Cynthia Gregory" wrote in message
...
I have a group of cells with dates in them set up as so
4/8/2004 4/26/2004 Cycle 1
4/27/2004 5/4/2004 Cycle 2
5/5/2004 5/11/2004 Cycle 3

etc etc etc

FIND PLOGS CLOSED DURING A SPECIFIC TIME PERIOD
Then I have a query to sum the number of "All Products" with the criteria
of
"Closed Plogs" and "Show Stopper" and the date value in column "H" if ="a
date" and then <= "a date" as shown below
{=SUM(IF(Closed_Plogs!$A$2:$A$3000="All
Products",1,0)*IF(Closed_Plogs!$L$2:$L$3000="Show

Stopper",1,0)*IF(Closed_Plogs!$H$2:$H$3000=VALUE( "4/08/2004"),1,0)*IF(Close
d_Plogs!$H$2:$H$3000<=VALUE("4/26/2004"),1,0))}

FIND PLOGS OPENED DURING A SPECIFIC TIME PERIOD
I have another query to the sum the number of "All Products" with the
criteria of "show stopper" and date value criteria as shown below
{=SUM(IF(All_Plogs!$A$2:$A$3000="All
Products",1,0)*IF(All_Plogs!$L$2:$L$3000="Show

Stopper",1,0)*IF(All_Plogs!$B$2:$B$3000=VALUE("4/08/2004"),1,0)*IF(All_Plog
s!$B$2:$B$3000<=VALUE("4/26/2004"),1,0))}

I say all of this to I now need to take the same data and now I need to
know
which cycle the plog was opened in and which cycle it was closed in, not
the
specific Plog, just a count of when they were opened and closed. The
above
formulas are set up in cycles in the spreadsheets and tell me how many
were
closed during a cycle and how many were opened during a cycle, however, I
am
having a real difficult time coming up with the correct formula

I would have a column for Open C1 Closed C1
column for Open C1 Closed C2
column for Open C1 Closed C3
column for Open C2 Closed C2
column for Open C2 Closed C3
column for Open C3 Closed C3

Well I have rambled on and on trying to explain, I hope this makes sense
and
I appreciate any and all help. You guys are the greatest.
--
Thanks,
Cynthia G.

"Information is just signs and numbers, while knowledge
involves their meaning. What we want is knowledge, but what
we get is information."
--Heinz R. Pagels

formula question for arrays
 

Thanks Tom. I will try that!

--
Thanks,
Cynthia G.

"Information is just signs and numbers, while knowledge
involves their meaning. What we want is knowledge, but what
we get is information."
--Heinz R. Pagels

"Tom Ogilvy" wrote in message
...
Assume it says Cycle1 in C for when opened and Cycle2 in D for when closed
as an example.

*IF(All_Plogs!$C$2:$C$3000="Cycle1",1,0)*IF(All_Pl ogs!$SD$2:$D$3000="Cycle2"
,1,0)*

Add the above condition to the appropriate other conditions.

If not that, then you would need a better explanation of how cycle open
and
cycle close are indicated for a single plot.

--
Regards,
Tom Ogilvy


"Cynthia Gregory" wrote in message
...
I have a group of cells with dates in them set up as so
4/8/2004 4/26/2004 Cycle 1
4/27/2004 5/4/2004 Cycle 2
5/5/2004 5/11/2004 Cycle 3

etc etc etc

FIND PLOGS CLOSED DURING A SPECIFIC TIME PERIOD
Then I have a query to sum the number of "All Products" with the
criteria
of
"Closed Plogs" and "Show Stopper" and the date value in column "H" if
="a
date" and then <= "a date" as shown below
{=SUM(IF(Closed_Plogs!$A$2:$A$3000="All
Products",1,0)*IF(Closed_Plogs!$L$2:$L$3000="Show


Stopper",1,0)*IF(Closed_Plogs!$H$2:$H$3000=VALUE( "4/08/2004"),1,0)*IF(Close
d_Plogs!$H$2:$H$3000<=VALUE("4/26/2004"),1,0))}

FIND PLOGS OPENED DURING A SPECIFIC TIME PERIOD
I have another query to the sum the number of "All Products" with the
criteria of "show stopper" and date value criteria as shown below
{=SUM(IF(All_Plogs!$A$2:$A$3000="All
Products",1,0)*IF(All_Plogs!$L$2:$L$3000="Show


Stopper",1,0)*IF(All_Plogs!$B$2:$B$3000=VALUE("4/08/2004"),1,0)*IF(All_Plog
s!$B$2:$B$3000<=VALUE("4/26/2004"),1,0))}

I say all of this to I now need to take the same data and now I need to
know
which cycle the plog was opened in and which cycle it was closed in, not
the
specific Plog, just a count of when they were opened and closed. The
above
formulas are set up in cycles in the spreadsheets and tell me how many
were
closed during a cycle and how many were opened during a cycle, however,
I
am
having a real difficult time coming up with the correct formula

I would have a column for Open C1 Closed C1
column for Open C1 Closed C2
column for Open C1 Closed C3
column for Open C2 Closed C2
column for Open C2 Closed C3
column for Open C3 Closed C3

Well I have rambled on and on trying to explain, I hope this makes sense
and
I appreciate any and all help. You guys are the greatest.
--
Thanks,
Cynthia G.

"Information is just signs and numbers, while knowledge
involves their meaning. What we want is knowledge, but what
we get is information."
--Heinz R. Pagels