Multiple conditions...why wont this work?

[Guest]

I am trying to calculate the number of instances by employee

A18 = emplyee number
column c contains range of employee numbers
column I = contains the range of information that needs to be counted
a21=criteria listed within the range in column i

=SUMPRODUCT('Dec03-Dec30'!$C$2:$C$5500=$A$18)*('Dec03-Dec30'!$I$2:$I$5500=A21)*('Dec03-Dec30'!$I$2:$I$5500)

Why won't the formula above work.

Please and Thanks

 

[ScottO]

I'm no expert on the use of SumProduct (and many other things as
well,
but I think that you've just forgotten the "double minus" before each
argument (assuming that the missing parentheses are just typos).
Try
=SUMPRODUCT(--('Dec03-Dec30'!$C$2:$C$5500=$A$18)*--('Dec03-Dec30'!$I$
2:$I$5500=A21)*('Dec03-Dec30'!$I$2:$I$5500))
Rgds,
ScottO

| I am trying to calculate the number of instances by employee
|
| A18 = emplyee number
| column c contains range of employee numbers
| column I = contains the range of information that needs to be
counted
| a21=criteria listed within the range in column i
|
|
=SUMPRODUCT('Dec03-Dec30'!$C$2:$C$5500=$A$18)*('Dec03-Dec30'!$I$2:$I$
5500=A21)*('Dec03-Dec30'!$I$2:$I$5500)
|
| Why won't the formula above work.
|
| Please and Thanks
|
|

 

[Guest]

I am still getting error "#value"

Thanks

 

[Biff]

(assuming that the missing parentheses are just typos).

No, that's why it "won't work".

but I think that you've just forgotten the "double minus"

The double unary and the multiplication operator achieve the same thing but
it's not necessary to use both.

Either will do the same thing:

=SUMPRODUCT(('Dec03-Dec30'!$C$2:$C$5500=$A$18)*('Dec03-Dec30'!$I$2:$I$5500=A21)*('Dec03-Dec30'!$I$2:$I$5500))

=SUMPRODUCT(--('Dec03-Dec30'!$C$2:$C$5500=$A$18),--('Dec03-Dec30'!$I$2:$I$5500=A21),--('Dec03-Dec30'!$I$2:$I$5500))

Biff

 

[ScottO]

Then I think the issue will be in your data on the 'Dec03-Dec30' tab.
I tested out the formula before my previous post on some dummy data
and it returned the correct result - so I think it's either a typo in
the formula, or an error in the data.
Rgds,
ScottO

| I am still getting error "#value"
|
| Thanks
|
| "ScottO" wrote:
|
| > I'm no expert on the use of SumProduct (and many other things as
| > well,
| > but I think that you've just forgotten the "double minus" before
each
| > argument (assuming that the missing parentheses are just typos).
| > Try
| >
=SUMPRODUCT(--('Dec03-Dec30'!$C$2:$C$5500=$A$18)*--('Dec03-Dec30'!$I$
| > 2:$I$5500=A21)*('Dec03-Dec30'!$I$2:$I$5500))
| > Rgds,
| > ScottO
| >
| > | > | I am trying to calculate the number of instances by employee
| > |
| > | A18 = emplyee number
| > | column c contains range of employee numbers
| > | column I = contains the range of information that needs to be
| > counted
| > | a21=criteria listed within the range in column i
| > |
| > |
| >
=SUMPRODUCT('Dec03-Dec30'!$C$2:$C$5500=$A$18)*('Dec03-Dec30'!$I$2:$I$
| > 5500=A21)*('Dec03-Dec30'!$I$2:$I$5500)
| > |
| > | Why won't the formula above work.
| > |
| > | Please and Thanks
| > |
| > |
| >
| >
| >