sum if problem

[Guest]

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!

 

[Bob Phillips]

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)

 

[Bryan Hessey]

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