sum if

P

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?
 
B

bj

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
 

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