sum if problem
 

Hello I need help urgently

I have a spreadsheet that looks like this
a b c d e f
1 100 1 257 599
2 50 10
3 10 2 40 4 5 1
4 45

and i'm classifying the volymes in column b,d,f in numbers between 1-10
Here is the problem:
I want to sum all the 1,2,3, so if i classify b1;1 and F3;1 the sum should
be 105

I guess it should be an sum if formula but i cant figure it out

Please help me!

I think you mean

=SUMPRODUCT(--(SUMIF(INDIRECT(CHAR({2,4,6}+64)&"1:"&CHAR({2,4,6} +64)&"5"),1,
INDIRECT(CHAR({1,3,5}+64)&"1:"&CHAR({1,3,5}+64)&"5 "))))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Felix Lindberg" <Felix wrote in message
...
Hello I need help urgently

I have a spreadsheet that looks like this
a b c d e f
1 100 1 257 599
2 50 10
3 10 2 40 4 5 1
4 45

and i'm classifying the volymes in column b,d,f in numbers between 1-10
Here is the problem:
I want to sum all the 1,2,3, so if i classify b1;1 and F3;1 the sum should
be 105

I guess it should be an sum if formula but i cant figure it out

Please help me!

You have not said where you are putting the '1' classification, so,
assuming that you use range AB1 to AG4 to hold the class of 1 (2 or 3
etc) for numbers in B1 to G4, then
=SUMIF(AB1:AG4,1,B1:G4)
=SUMIF(AB1:AG4,3,B1:G4)

will add up to 145 with a 1 in AB1 and AE4 etc
and 41 with a 3 in AC1 and AD3

hope this helps you


Felix Lindberg Wrote:
Hello I need help urgently

I have a spreadsheet that looks like this
a b c d e f
1 100 1 257 599
2 50 10
3 10 2 40 4 5 1
4 45

and i'm classifying the volymes in column b,d,f in numbers between
1-10
Here is the problem:
I want to sum all the 1,2,3, so if i classify b1;1 and F3;1 the sum
should
be 105

I guess it should be an sum if formula but i cant figure it out

Please help me!


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=392089