Hi everyone,

I'm using the following to find the number of times 4 is scored per each
employee

=SUMPRODUCT((Data!$B$4:$B$691=$B31)*(Data!$K$4:$K$ 691=4))

where B is the unique employee number and K is a calculated field.

When I enter 4 into the calculated field instead of the result of the calc
then sumproduct picks it up.

So, I'm thinking that it's probably because it's not exactly 4 but rounds to
4.

I'm going to copy and paste values to get arround this but I know that this
will happen a lot.

So, is there any way to specify in the formula that I want it to count where
the cell returns a value that rounds up or down to the number I'm asking it
to look for?

Hope this makes sense
Thank you
--
Trish

Doh!,

Copy and paste values - copies the actual result so no use - do I feel
silly!!!!!
--
Trish


"Trish Smith" wrote:

Hi everyone,

I'm using the following to find the number of times 4 is scored per each
employee

=SUMPRODUCT((Data!$B$4:$B$691=$B31)*(Data!$K$4:$K$ 691=4))

where B is the unique employee number and K is a calculated field.

When I enter 4 into the calculated field instead of the result of the calc
then sumproduct picks it up.

So, I'm thinking that it's probably because it's not exactly 4 but rounds to
4.

I'm going to copy and paste values to get arround this but I know that this
will happen a lot.

So, is there any way to specify in the formula that I want it to count where
the cell returns a value that rounds up or down to the number I'm asking it
to look for?

Hope this makes sense
Thank you
--
Trish

Trish,

You could try this

=SUMPRODUCT((Data!$B$4:$B$691=$B31)*(ROUND(Data!$K $4:$K$691,0)=4))

Mike

"Trish Smith" wrote:

Hi everyone,

I'm using the following to find the number of times 4 is scored per each
employee

=SUMPRODUCT((Data!$B$4:$B$691=$B31)*(Data!$K$4:$K$ 691=4))

where B is the unique employee number and K is a calculated field.

When I enter 4 into the calculated field instead of the result of the calc
then sumproduct picks it up.

So, I'm thinking that it's probably because it's not exactly 4 but rounds to
4.

I'm going to copy and paste values to get arround this but I know that this
will happen a lot.

So, is there any way to specify in the formula that I want it to count where
the cell returns a value that rounds up or down to the number I'm asking it
to look for?

Hope this makes sense
Thank you
--
Trish

Hi Mike,

That DID it!

Many thanks :-)
--
Trish


"Mike H" wrote:

Trish,

You could try this

=SUMPRODUCT((Data!$B$4:$B$691=$B31)*(ROUND(Data!$K $4:$K$691,0)=4))

Mike

"Trish Smith" wrote:

Hi everyone,

I'm using the following to find the number of times 4 is scored per each
employee

=SUMPRODUCT((Data!$B$4:$B$691=$B31)*(Data!$K$4:$K$ 691=4))

where B is the unique employee number and K is a calculated field.

When I enter 4 into the calculated field instead of the result of the calc
then sumproduct picks it up.

So, I'm thinking that it's probably because it's not exactly 4 but rounds to
4.

I'm going to copy and paste values to get arround this but I know that this
will happen a lot.

So, is there any way to specify in the formula that I want it to count where
the cell returns a value that rounds up or down to the number I'm asking it
to look for?

Hope this makes sense
Thank you
--
Trish

Try
=SUMPRODUCT((Data!$B$4:$B$691=$B31)*(round(Data!$K $4:$K$691,0)=4))

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Trish Smith" wrote in message
...
Hi everyone,

I'm using the following to find the number of times 4 is scored per each
employee

=SUMPRODUCT((Data!$B$4:$B$691=$B31)*(Data!$K$4:$K$ 691=4))

where B is the unique employee number and K is a calculated field.

When I enter 4 into the calculated field instead of the result of the calc
then sumproduct picks it up.

So, I'm thinking that it's probably because it's not exactly 4 but rounds
to
4.

I'm going to copy and paste values to get arround this but I know that
this
will happen a lot.

So, is there any way to specify in the formula that I want it to count
where
the cell returns a value that rounds up or down to the number I'm asking
it
to look for?

Hope this makes sense
Thank you
--
Trish