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

[Gina]

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 D12000 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?

 

[Max]

.. a function in Cell D1 of the "Summary" tab that

looks at A1:A2000 and D12000 in the "Data" tab, and will
let me know how many occurences of "Recordable" (in D12000)
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!D12000="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$2000="Recordable"))
which also returns the same results for Jan07

Adapt the ">=" & "<" signs to suit the start/enddates that you listed

---

 

[Gina]

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]

Welcome, Gina. The feedback is appreciated.

 

[Gina]

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<B4,Data!$F$2:$F$92,0),0))

 

[Max]

Some comments:

=SUM(IF(Data!$A$2:$A$2000>=A4,IF(Data!$A$2:$A$92<B4,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$2000="Recordable")*Data!F$2:F$2000)

---

 

[Max]

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)

---

 

[Gina]

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

 

[Max]

Welcome. Gina. Glad it did.

 

[ryguy7272]

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

Regards,
Ryan---