Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct | Excel Worksheet Functions | |||
sumproduct causing memory errors? | Excel Worksheet Functions | |||
Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |