sum on conditions.....
 

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


--
pimar
------------------------------------------------------------------------
pimar's Profile: http://www.excelforum.com/member.php...fo&userid=5386
View this thread: http://www.excelforum.com/showthread...hreadid=374137

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 your
f's and d for your m's


--
tkaplan
------------------------------------------------------------------------
tkaplan's Profile: http://www.excelforum.com/member.php...o&userid=22987
View this thread: http://www.excelforum.com/showthread...hreadid=374137

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


--
pimar
------------------------------------------------------------------------
pimar's Profile: http://www.excelforum.com/member.php...fo&userid=5386
View this thread: http://www.excelforum.com/showthread...hreadid=374137

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.


--
greg7468
------------------------------------------------------------------------
greg7468's Profile: http://www.excelforum.com/member.php...fo&userid=9031
View this thread: http://www.excelforum.com/showthread...hreadid=374137

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


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=374137

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

ciao
*pimar


--
pimar
------------------------------------------------------------------------
pimar's Profile: http://www.excelforum.com/member.php...fo&userid=5386
View this thread: http://www.excelforum.com/showthread...hreadid=374137

Good it helped you. Thanks for the feedback.

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=374137