SUMPRODUCT 1

[Suggie]

Hello,

Can anyone tell why this formula is just returning #value

=SUMPRODUCT((('Iflex Projects Summary'!$C$6:$K$102=Iflex!$C$14)*('Iflex Projects Summary'!$D$6:$K$102=Iflex!$M$11)*('Iflex Projects Summary'!$C$6:$K$102=Iflex!M12)*'Iflex Projects Summary'!C6:K102))

I'm trying to use sumproduct based on 3 criteria's
C14 = unique ID
M11 = Week 1

But I ca't get it to work - the Projects Summary table it is searching is a pivot table.

Any ideas?

TIA,

 

[xlbo]

you either have a #VALUE! error somewhere in your data set or you have mix n match data types (ie numbers stored as text) somewhere

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hi,
[tt]
=SUMPRODUCT
((
('Iflex Projects Summary'!$C$6:$K$102=Iflex!$C$14)*
('Iflex Projects Summary'!$D$6:$K$102=Iflex!$M$11)*
('Iflex Projects Summary'!$C$6:$K$102=Iflex!M12)*
[red]([/red]'Iflex Projects Summary'!C6:K102)
[red])[/red])

[/tt]


Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[AnotherHiggins]

Without trying to recreate your conditions to try this, I'd say that your parentheses look wrong. Depending one what you want to do with the formula, I think it should either be

=SUMPRODUCT(([red]([/red]'Iflex Projects Summary'!$C$6:$K$102=Iflex!$C$14)*('Iflex Projects Summary'!$D$6:$K$102=Iflex!$M$11)*('Iflex Projects Summary'!$C$6:$K$102=Iflex!M12)*[highlight]([/highlight]'Iflex Projects Summary'!C6:K102))

or =SUMPRODUCT(([red]([/red]'Iflex Projects Summary'!$C$6:$K$102=Iflex!$C$14)*('Iflex Projects Summary'!$D$6:$K$102=Iflex!$M$11)*('Iflex Projects Summary'!$C$6:$K$102=Iflex!M12)[highlight])[/highlight]*'Iflex Projects Summary'!C6:K102[red]))[/red]

(Getting rid of red paren's and adding highlighted ones.

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[xlbo]

....or that ;-)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Suggie]

Thanks for the responses - still getting errors though and I can't see what it is??


=SUMPRODUCT
(('Iflex Projects Summary'!$C$6:$K$102=Iflex!$C$14)*
('Iflex Projects Summary'!$D$6:$K$102=Iflex!$M$11)*
('Iflex Projects Summary'!$C$6:$K$102=Iflex!M12)
*('Iflex Projects Summary'!C6:K102))


Still get #value

=SUMPRODUCT(('Iflex Projects Summary'!$C$6:$K$102=Iflex!$C$14)*('Iflex Projects Summary'!$D$6:$K$102=Iflex!$M$11)*('Iflex Projects Summary'!$C$6:$K$102=Iflex!M12))*'Iflex Projects Summary'!C6:K102

get #N/A

 

[xlbo]

2nd formula is incorrect syntactically

1st formula is correct syntactically so please refer to my 1st post

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

You should be looking in the SAME RANGE SIZE which you are NOT -- usually a single column range in each.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[SkipVought]

What COLUMN contains the IDs

What COLUMN contains WEEKS.

What else are you comparing?

What COLUMN contains the VALUES that you want to SUM?

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[Suggie]

Thanks Skip - I did try the same range but still not working.

Basically, I have a pivot table that contains the hrs that each employee has booked for each day throughout Sept.

I need to put this into a separate format.

column C = is the unique id - employee id
column d = is the week, whether it was week 1, 2, 3 etc
row e6:k6 = the days, Mon, Tues, Wed etc

The data I'm trying to get back is in the pivot table range being C6:K102

TIA

 

[SkipVought]

[tt]
=SUMPRODUCT
(('Iflex Projects Summary'!$C$6:$C$102=Iflex!$C$14)*
('Iflex Projects Summary'!$D$6:$D$102=Iflex!$M$11)*
('Iflex Projects Summary'!$E$6:$K$102=Iflex!M12)
*(1))
[/tt]


Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[Suggie]

Skip - genius! very impressed.

Thanks.

 

[SkipVought]

This will also work...
[tt]
=SUMPRODUCT
(('Iflex Projects Summary'!$C$6:$C$102=Iflex!$C$14)*
('Iflex Projects Summary'!$D$6:$D$102=Iflex!$M$11)*
('Iflex Projects Summary'!$E$6:$K$102=Iflex!M12))
[/tt]

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]