Home |
Search |
Today's Posts |
#1
|
|||
|
|||
sum if
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? |
#2
|
|||
|
|||
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 "Pete" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|