I'm having some issues here, I've been digging through the questions to find
an answer and I've tried several methods but I can't seem to get it. There
are a few different things I want to do so bear with me.

I have my dates separated into three columns b,c,d and I want to be able to
return the number of times "Inury, Lost Time" occurs in 2006 which would be
column b. I've tried the sumproduct but keep getting a value error

=SUMPRODUCT(--('Incidents 2006'!B3:B31500="2006"),--('Incidents
2006'!E3:E1500="Injury, Lost Time"))

Watch your ranges. B3:B31500 has lots more rows than E3:E1500.

And if that year field is really numeric, you may want to use =2006 (not the
string "2006").

And if that date field is really a date, you may want:

=SUMPRODUCT(--year('Incidents 2006'!B3:B31500)=2006),....



phatbusa wrote:

I'm having some issues here, I've been digging through the questions to find
an answer and I've tried several methods but I can't seem to get it. There
are a few different things I want to do so bear with me.

I have my dates separated into three columns b,c,d and I want to be able to
return the number of times "Inury, Lost Time" occurs in 2006 which would be
column b. I've tried the sumproduct but keep getting a value error

=SUMPRODUCT(--('Incidents 2006'!B3:B31500="2006"),--('Incidents
2006'!E3:E1500="Injury, Lost Time"))

--

Dave Peterson

Thanks, I didn't notice the b31500, but now it returns 0 instead of what it
should? what do you figure?


"Dave Peterson" wrote:

Watch your ranges. B3:B31500 has lots more rows than E3:E1500.

And if that year field is really numeric, you may want to use =2006 (not the
string "2006").

And if that date field is really a date, you may want:

=SUMPRODUCT(--year('Incidents 2006'!B3:B31500)=2006),....



phatbusa wrote:

I'm having some issues here, I've been digging through the questions to find
an answer and I've tried several methods but I can't seem to get it. There
are a few different things I want to do so bear with me.

I have my dates separated into three columns b,c,d and I want to be able to
return the number of times "Inury, Lost Time" occurs in 2006 which would be
column b. I've tried the sumproduct but keep getting a value error

=SUMPRODUCT(--('Incidents 2006'!B3:B31500="2006"),--('Incidents
2006'!E3:E1500="Injury, Lost Time"))

--

Dave Peterson

=SUMPRODUCT(--('Incidents 2006'!B3:B31500=2006),
--('Incidents 2006'!E3:E1500="Injury, Lost Time"))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"phatbusa" wrote in message
...
I'm having some issues here, I've been digging through the questions to
find
an answer and I've tried several methods but I can't seem to get it.
There
are a few different things I want to do so bear with me.

I have my dates separated into three columns b,c,d and I want to be able
to
return the number of times "Inury, Lost Time" occurs in 2006 which would
be
column b. I've tried the sumproduct but keep getting a value error

=SUMPRODUCT(--('Incidents 2006'!B3:B31500="2006"),--('Incidents
2006'!E3:E1500="Injury, Lost Time"))

I'd figure the text/date suggestion (same as before).

phatbusa wrote:

Thanks, I didn't notice the b31500, but now it returns 0 instead of what it
should? what do you figure?

"Dave Peterson" wrote:

Watch your ranges. B3:B31500 has lots more rows than E3:E1500.

And if that year field is really numeric, you may want to use =2006 (not the
string "2006").

And if that date field is really a date, you may want:

=SUMPRODUCT(--year('Incidents 2006'!B3:B31500)=2006),....



phatbusa wrote:

I'm having some issues here, I've been digging through the questions to find
an answer and I've tried several methods but I can't seem to get it. There
are a few different things I want to do so bear with me.

I have my dates separated into three columns b,c,d and I want to be able to
return the number of times "Inury, Lost Time" occurs in 2006 which would be
column b. I've tried the sumproduct but keep getting a value error

=SUMPRODUCT(--('Incidents 2006'!B3:B31500="2006"),--('Incidents
2006'!E3:E1500="Injury, Lost Time"))

--

Dave Peterson


--

Dave Peterson

that worked bob, thanks

now for the next part

I want to be able to have change the 2006 to 2005 and have a different sheet
referenced for that answer in the same cell

does that make sense?

"Bob Phillips" wrote:

=SUMPRODUCT(--('Incidents 2006'!B3:B31500=2006),
--('Incidents 2006'!E3:E1500="Injury, Lost Time"))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"phatbusa" wrote in message
...
I'm having some issues here, I've been digging through the questions to
find
an answer and I've tried several methods but I can't seem to get it.
There
are a few different things I want to do so bear with me.

I have my dates separated into three columns b,c,d and I want to be able
to
return the number of times "Inury, Lost Time" occurs in 2006 which would
be
column b. I've tried the sumproduct but keep getting a value error

=SUMPRODUCT(--('Incidents 2006'!B3:B31500="2006"),--('Incidents
2006'!E3:E1500="Injury, Lost Time"))

Not quite.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"phatbusa" wrote in message
...
that worked bob, thanks

now for the next part

I want to be able to have change the 2006 to 2005 and have a different
sheet
referenced for that answer in the same cell

does that make sense?

"Bob Phillips" wrote:

=SUMPRODUCT(--('Incidents 2006'!B3:B31500=2006),
--('Incidents 2006'!E3:E1500="Injury, Lost Time"))

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"phatbusa" wrote in message
...
I'm having some issues here, I've been digging through the questions
to
find
an answer and I've tried several methods but I can't seem to get it.
There
are a few different things I want to do so bear with me.

I have my dates separated into three columns b,c,d and I want to be
able
to
return the number of times "Inury, Lost Time" occurs in 2006 which
would
be
column b. I've tried the sumproduct but keep getting a value error

=SUMPRODUCT(--('Incidents 2006'!B3:B31500="2006"),--('Incidents
2006'!E3:E1500="Injury, Lost Time"))

Sorry,
do you have an email, and I'll show you what I'm workin' on?

if not basically what I'm trying to do is this,

I want to be able to change the year on the top of a sheet and have it show
only the data that corresponds to that year, and break it down into
categories like
equipment damage, injuries, and so on. to summarize the database.

Just change the hard-coded year to the year cell

=SUMPRODUCT(--('Incidents 2006'!B3:B31500=A1),
--('Incidents 2006'!E3:E1500="Injury, Lost Time"))



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"phatbusa" wrote in message
...
Sorry,
do you have an email, and I'll show you what I'm workin' on?

if not basically what I'm trying to do is this,

I want to be able to change the year on the top of a sheet and have it
show
only the data that corresponds to that year, and break it down into
categories like
equipment damage, injuries, and so on. to summarize the database.

Thank you so much for the help, I'm headed in the right direction now

"Bob Phillips" wrote:

Just change the hard-coded year to the year cell

=SUMPRODUCT(--('Incidents 2006'!B3:B31500=A1),
--('Incidents 2006'!E3:E1500="Injury, Lost Time"))



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"phatbusa" wrote in message
...
Sorry,
do you have an email, and I'll show you what I'm workin' on?

if not basically what I'm trying to do is this,

I want to be able to change the year on the top of a sheet and have it
show
only the data that corresponds to that year, and break it down into
categories like
equipment damage, injuries, and so on. to summarize the database.