sum on conditions.....

P

pimar

Hello again,
I have a new question for you, guru.

This is my worksheet

Column A Column B

Member A 5
f
Member B 5
m
Member C 5
f

As you can see, for each "member" I need to specify 2 values : a
numeric one (let's say "age") and string one ("f"="female" or
"m"="man").

My problem is to sum the ages for all "female" (f) members .... and sum
of ages for "man" (m) members.

Of course, it would be simplier to have

Member A 5 f
Member B 5 i
Member C 5 f

but I cannot change the spreadsheet as it is now.

How can achieve this result? :confused:

thanks a lot
*pimar
 
T

tkaplan

one possible solution(although not very elegant)...
in c1:
=IF(A2="f",B1,0)

d1:
=IF(A2="m",B1,0)

put this formula in every other line. then you can sum col c for you
f's and d for your m'
 
P

pimar

Thank you,
but I really cannot use this solution, mainly because I should repea
this operation for dozen of coloumns (it means I shlould have at òeas
2*12 new columns ... it would make my excel unusable)...
btw, thanks for your reply...
*pima
 
G

greg7468

Hi,
again not a very elegant way but may possibly work,

I assume your spreadsheet looks like this

---col A-------col B
--name-------age
-gender-----blank

If so could you not copy B1 (age) into B2

Then have a subtotal for each column.

Filter to the gender required and the subtotal should sum the ages

----col A------col B
-----f------------5
-----f------------6
-----f------------5

---total---------11


HTH,

Greg
 
M

mangesh_yadav

I copied your example in the range A1:B6. Now to get the age of all
females, use the following formula:
=SUMPRODUCT(B1:B6,--(OFFSET(A1:A6,1,0)="f"))

and for males, use:
=SUMPRODUCT(B1:B6,--(OFFSET(A1:A6,1,0)="m"))


Mangesh
 
P

pimar

Thanks a lot all of you.
I've adopted the solution proposed by Mangesh . It was exactly what I
was looking for... :)

ciao
*pimar
 
Top