Need to Count the number of times a value occurs within a dt range
 

I have a spreadsheet that tracks the number of times specific events occur
over a large period of time.

The first things I needed to do on my summary tab was count the number of
occurences (all) during a specific time frame- I decided to go by months. So
what I did was set up a new worksheet and called it summary.

In Column A, I created a list of "start dates", and in Column B, "end
dates". Then in column C I wrote the following in order to get Excel to go
to the "Data" tab and calculate how many occurences fell within this date
range.

"=-COUNT(Data!$A$2:$A$2000)+COUNTIF(Data!$A$2:$A$2000 ,"="&A3)+COUNTIF(Data!$A$2:$A$2000,"<"&B3)"

Now what I need to do is drill a little deeper. Column E of the "Data"
worksheet is where the specific type of occurence is listed. It can be one
of 4 types. For the sake of this question- I'll stick with one type, which
is labeled "Recordable".

What I need to do is come up with a function in Cell D1 of the "Summary"
worksheet tab that looks at A1:A2000 and D1:D2000 in the "Data" tab, and will
let me know how many occurences of "Recordable" (D1:2000) Happened within the
date range of 1/1/2007 and 2/1/2007, found in (A1:A2000) of the Data tab.

Help?

Need to Count the number of times a value occurs within a dt range
 

.. a function in Cell D1 of the "Summary" tab that
looks at A1:A2000 and D1:D2000 in the "Data" tab, and will
let me know how many occurences of "Recordable" (in D1:D2000)
happened within the date range of 1/1/2007 and 2/1/2007,
found in (A1:A2000) of the Data tab.

2 ways

In Summary,
In D1:
=SUMPRODUCT((TEXT(Data!A1:A2000,"mmmyy")="Jan07")* (Data!D1:D2000="Recordable"))
which returns it for the month/yr indicated

Or, if you have various startdates/"end"dates listed in A1:B1
(eg in A1: 1 Jan 07, in B1: 1 Feb 07)
then in D1 copied down:
=SUMPRODUCT((Data!A$1:A$2000=A1)*(Data!A$1:A$2000 <B1)*(Data!D$1:D$2000="Recordable"))
which also returns the same results for Jan07

Adapt the "=" & "<" signs to suit the start/enddates that you listed
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Need to Count the number of times a value occurs within a dt r
 

Max, this is exactly what I needed. I really appreciate your help, and have
located a tutorial on sumproduct, and plan to study this for the rest of the
day.

Thank you so much,

Gina

"Max" wrote:

.. a function in Cell D1 of the "Summary" tab that
looks at A1:A2000 and D1:D2000 in the "Data" tab, and will
let me know how many occurences of "Recordable" (in D1:D2000)
happened within the date range of 1/1/2007 and 2/1/2007,
found in (A1:A2000) of the Data tab.

2 ways

In Summary,
In D1:
=SUMPRODUCT((TEXT(Data!A1:A2000,"mmmyy")="Jan07")* (Data!D1:D2000="Recordable"))
which returns it for the month/yr indicated

Or, if you have various startdates/"end"dates listed in A1:B1
(eg in A1: 1 Jan 07, in B1: 1 Feb 07)
then in D1 copied down:
=SUMPRODUCT((Data!A$1:A$2000=A1)*(Data!A$1:A$2000 <B1)*(Data!D$1:D$2000="Recordable"))
which also returns the same results for Jan07

Adapt the "=" & "<" signs to suit the start/enddates that you listed
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Need to Count the number of times a value occurs within a dt r
 

Welcome, Gina. The feedback is appreciated.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gina" wrote in message
...
Max, this is exactly what I needed. I really appreciate your help, and
have
located a tutorial on sumproduct, and plan to study this for the rest of
the
day.

Thank you so much,

Gina

Need to Count the number of times a value occurs within a dt r
 

I have one additional question if it's not too much.

I am trying to sum the money associated with these date ranges now- the
values are found in column F of the Data worksheet. I have put this formula
into cell J4 of the Data worksheet (the date ranges are still found in A4 for
start, B4 for end), and I have hit a snag. Do you see anything obvious I am
missing?

=SUM(IF(Data!$A$2:$A$2000=A4,IF(Data!$A$2:$A$92<B 4,Data!$F$2:$F$92,0),0))

"Max" wrote:

Welcome, Gina. The feedback is appreciated.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gina" wrote in message
...
Max, this is exactly what I needed. I really appreciate your help, and
have
located a tutorial on sumproduct, and plan to study this for the rest of
the
day.

Thank you so much,

Gina

Need to Count the number of times a value occurs within a dt r
 

Some comments:
=SUM(IF(Data!$A$2:$A$2000=A4,IF(Data!$A$2:$A$92<B 4,Data!$F$2:$F$92,0),0))

1. The ranges need to be identically sized
2. You need to press CTRL+SHIFT+ENTER to confirm the formula

Try this alternative, normal ENTER will do:
=SUMPRODUCT((Data!A$2:A$2000=A4)*(Data!A$2:A$2000 <B4)*(Data!D$2:D$2000="Recordable")*Data!F$2:F$200 0)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Need to Count the number of times a value occurs within a dt r
 

Correction, it should read ..
Try this alternative, normal ENTER will do:
=SUMPRODUCT((Data!A$2:A$2000=A4)*(Data!A$2:A$2000 <B4)*Data!F$2:F$2000)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Need to Count the number of times a value occurs within a dt r
 

Thank you. That did work exactly as I hoped it would.
Gina

"Max" wrote:

Correction, it should read ..
Try this alternative, normal ENTER will do:
=SUMPRODUCT((Data!A$2:A$2000=A4)*(Data!A$2:A$2000 <B4)*Data!F$2:F$2000)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Need to Count the number of times a value occurs within a dt r
 

Welcome. Gina. Glad it did.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gina" wrote in message
...
Thank you. That did work exactly as I hoped it would.
Gina

Need to Count the number of times a value occurs within a dt r
 

Gina, this is a great tutorial on Sumproduct:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Regards,
Ryan---

--
RyGuy


"Max" wrote:

Welcome. Gina. Glad it did.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gina" wrote in message
...
Thank you. That did work exactly as I hoped it would.
Gina