Sumproduct
 

Hello from Steved

{=SUMPRODUCT(--('Depots by Bus Type'!$A$3:$A$30="B"),--('Depots by Bus
Type'!$I33="Roskill"),'Depots by Bus Type'!$B$3:$J$30)}

On worksheet to depots I've the above formula that id displaying #VALUE!
I've been going around in circles for over an hour.

Ok to sum from B3:J30 from Worksheet "Depots by Bus Type"

=SUMIF($B$34,"B",'Depots by Bus Type'!D7) this works, B34 = "Roskill"

What is required please.

Thankyou.

Hi!

A couple of things:

--('Depots by Bus Type'!$I33="Roskill")

That array is not the same size as the others.

The formula in general is not an array entered formula but that won't cause
an error, it's just unecessary.

Biff

"Steved" wrote in message
...
Hello from Steved

{=SUMPRODUCT(--('Depots by Bus Type'!$A$3:$A$30="B"),--('Depots by Bus
Type'!$I33="Roskill"),'Depots by Bus Type'!$B$3:$J$30)}

On worksheet to depots I've the above formula that id displaying #VALUE!
I've been going around in circles for over an hour.

Ok to sum from B3:J30 from Worksheet "Depots by Bus Type"

=SUMIF($B$34,"B",'Depots by Bus Type'!D7) this works, B34 = "Roskill"

What is required please.

Thankyou.

Hello Biff from Steved

Thanks finally worked through it and below works fine.


=SUM(IF('Depots by Bus Type'!$A$1:$A$33="M",IF('Depots by Bus
Type'!$D$2:$D$2="Roskill",'Depots by Bus Type'!$D$1:$D$33,0),0))

"Biff" wrote:

Hi!

A couple of things:

--('Depots by Bus Type'!$I33="Roskill")

That array is not the same size as the others.

The formula in general is not an array entered formula but that won't cause
an error, it's just unecessary.

Biff

"Steved" wrote in message
...
Hello from Steved

{=SUMPRODUCT(--('Depots by Bus Type'!$A$3:$A$30="B"),--('Depots by Bus
Type'!$I33="Roskill"),'Depots by Bus Type'!$B$3:$J$30)}

On worksheet to depots I've the above formula that id displaying #VALUE!
I've been going around in circles for over an hour.

Ok to sum from B3:J30 from Worksheet "Depots by Bus Type"

=SUMIF($B$34,"B",'Depots by Bus Type'!D7) this works, B34 = "Roskill"

What is required please.

Thankyou.

Try this:

Normally entered:

=IF('Depots by Bus Type'!$D$2="Roskill",SUMIF('Depots by Bus
Type'!$A$1:$A$33,"M",'Depots by Bus Type'!$D$1:$D$33),0)

Biff

"Steved" wrote in message
...
Hello Biff from Steved

Thanks finally worked through it and below works fine.


=SUM(IF('Depots by Bus Type'!$A$1:$A$33="M",IF('Depots by Bus
Type'!$D$2:$D$2="Roskill",'Depots by Bus Type'!$D$1:$D$33,0),0))

"Biff" wrote:

Hi!

A couple of things:

--('Depots by Bus Type'!$I33="Roskill")

That array is not the same size as the others.

The formula in general is not an array entered formula but that won't
cause
an error, it's just unecessary.

Biff

"Steved" wrote in message
...
Hello from Steved

{=SUMPRODUCT(--('Depots by Bus Type'!$A$3:$A$30="B"),--('Depots by Bus
Type'!$I33="Roskill"),'Depots by Bus Type'!$B$3:$J$30)}

On worksheet to depots I've the above formula that id displaying
#VALUE!
I've been going around in circles for over an hour.

Ok to sum from B3:J30 from Worksheet "Depots by Bus Type"

=SUMIF($B$34,"B",'Depots by Bus Type'!D7) this works, B34 = "Roskill"

What is required please.

Thankyou.

Thanks Biff

"Biff" wrote:

Try this:

Normally entered:

=IF('Depots by Bus Type'!$D$2="Roskill",SUMIF('Depots by Bus
Type'!$A$1:$A$33,"M",'Depots by Bus Type'!$D$1:$D$33),0)

Biff

"Steved" wrote in message
...
Hello Biff from Steved

Thanks finally worked through it and below works fine.


=SUM(IF('Depots by Bus Type'!$A$1:$A$33="M",IF('Depots by Bus
Type'!$D$2:$D$2="Roskill",'Depots by Bus Type'!$D$1:$D$33,0),0))

"Biff" wrote:

Hi!

A couple of things:

--('Depots by Bus Type'!$I33="Roskill")

That array is not the same size as the others.

The formula in general is not an array entered formula but that won't
cause
an error, it's just unecessary.

Biff

"Steved" wrote in message
...
Hello from Steved

{=SUMPRODUCT(--('Depots by Bus Type'!$A$3:$A$30="B"),--('Depots by Bus
Type'!$I33="Roskill"),'Depots by Bus Type'!$B$3:$J$30)}

On worksheet to depots I've the above formula that id displaying
#VALUE!
I've been going around in circles for over an hour.

Ok to sum from B3:J30 from Worksheet "Depots by Bus Type"

=SUMIF($B$34,"B",'Depots by Bus Type'!D7) this works, B34 = "Roskill"

What is required please.

Thankyou.