function help, sumif??

Q

QChris

Hi

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

D11:D50 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?
 
D

Domenic

QChris said:
Hi

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

D11:D50 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.
 
Q

QChris

Thanks, that works a treat.

Domenic said:
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
.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top