function help, sumif??

[QChris]

Hi

I'm a new user to this site. I've used it to get this far, but now I'm stuck.

D1150 has quantity values, which can alter

I11:I50 have a pull down menu (as do columns K, M, O)
J11:J50 have a length, which can alter (as do columns L, N, P)

I66 onwards has each value of the pull down menu (10 values) with the total
meterage to be (hopefully) in the next cell. Currently, it works but without
multiply it with the values in D, so total material meterage is short!

Current formula: =(SUMIF($I$11:$I$47,"value1",
$J$11:$J$47)+(SUMIF($K$11:$K$47,"value1",$L$11:$L$47))+(SUMIF($M$11:$M$47,"value1",$N$11:$N$47))+(SUMIF($O$11:$O$47,"value1",$P$11:$P$47)))/1000

This is repeated in 10 rows, changing "vaule1" accordingly.
/1000 is just a convertion from mm to m.
I don't know if "sumif" is correct or???

Hope someone can shed some light on this?

 

[Domenic]

Hi

I'm a new user to this site. I've used it to get this far, but now I'm stuck.

D1150 has quantity values, which can alter

I11:I50 have a pull down menu (as do columns K, M, O)
J11:J50 have a length, which can alter (as do columns L, N, P)

I66 onwards has each value of the pull down menu (10 values) with the total
meterage to be (hopefully) in the next cell. Currently, it works but without
multiply it with the values in D, so total material meterage is short!

Current formula: =(SUMIF($I$11:$I$47,"value1",
$J$11:$J$47)+(SUMIF($K$11:$K$47,"value1",$L$11:$L$47))+(SUMIF($M$11:$M$47,"val
ue1",$N$11:$N$47))+(SUMIF($O$11:$O$47,"value1",$P$11:$P$47)))/1000

This is repeated in 10 rows, changing "vaule1" accordingly.
/1000 is just a convertion from mm to m.
I don't know if "sumif" is correct or???

Hope someone can shed some light on this?

If the values in Columns I, K, M< and O are unique to those columns and
do not occur in Columns J, L, N, and P, try the following formula that
needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF($I$11:$O$50=I66,($J$11:$P$50)*($D$11:$D$50)))/1000

Otherwise, try...


=SUM(IF(MOD(COLUMN($I$11:$O$50)-COLUMN($I$11),2)=0,IF($I$11:$O$50=I66,($J
$11:$P$50)*($D$11:$D50))))/1000

....which also needs to be confirmed with CONTROL+SHIFT+ENTER.

 

[QChris]

Thanks, that works a treat.

If the values in Columns I, K, M< and O are unique to those columns and
do not occur in Columns J, L, N, and P, try the following formula that
needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF($I$11:$O$50=I66,($J$11:$P$50)*($D$11:$D$50)))/1000

Otherwise, try...


=SUM(IF(MOD(COLUMN($I$11:$O$50)-COLUMN($I$11),2)=0,IF($I$11:$O$50=I66,($J
$11:$P$50)*($D$11:$D50))))/1000

....which also needs to be confirmed with CONTROL+SHIFT+ENTER.

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions
.