Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? 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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting occurrences of multiple conditions | Excel Discussion (Misc queries) | |||
How do I count number of cels the matches 2 conditions ? | Excel Worksheet Functions | |||
should be possible to add more conditions to conditional formatti. | Excel Discussion (Misc queries) | |||
How to multiple conditions to validate more than 2 conditions to . | Excel Worksheet Functions | |||
average on 2 conditions | Excel Worksheet Functions |