Sumproduct

[Steved]

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.

 

[Biff]

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]

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]

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]

Thanks Biff

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