sum if

[Pete]

in column a1:a25 i have the following values

a1 r
a2 1
a3 2
a4 3
a5 4
a6 r
a7 1
a8 2
a9 3
a10 4
a11 x
a12 1
a13 2
a14 3
a15 4
a16 r
a17 1
a18 2
a19 3
a20 4
a21 x
a22 1
a23 2
a24 3
a25 4

what i want to do is caluclate the total sum if the criteria meets say x or
r.

a1, a6, a11, a16, a21

so a1, a6, a16 all = r then i want the total of a2:a5 + a7:a10 + a17:a20

and the same in another formula looking for the x criteria which would be
a11 and a21, total a12:a15 + a22:a25

this is an example, the data continues almost to the limit of rows in
excel. And some the crteria is not just x or r there are others.

any ideas?

 

[Guest]

one way to do it
insert a new row 1
(there can be formating problems using row one with data. you can normally
work arround them, but it is generally just easier to let row one be labels
or blank.)
Ib B2 enter
if(isnumber(A2),B1,A2)
copy this down to the bottom of your data
then in C1
=sumproduct(--(B1:B25="r"),A1:A25) for the equals r
and in C2
=sumproduct(--(B1:B25="x"),A1:A25) for the equals x