Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining Columns
Hello, I have a bird watching record saved as an excel file. Now I would like to analyze this file. There are columns: Date, Name of the Bird and Quantity. Many dates have same birds. I would like to calculate how many birds of certain type I have seen. Im trying to figure out the formula to do this for me. I know that I should first compare Name of the Bird cells and pick all the rows that has certain value (for example a Bohemian Waxwing). Then I should calculate together all Quantities for these rows. What would be the easiest formula to do this? Heres an short example of the sheet and the results Im hoping to get Date Bird Qty 05/06/06 A Fishing Eagle 1 05/06/06 A Marsh Harrier 1 05/06/07 A Hen Harrier 2 05/06/07 A Marsh Harrier 3 05/06/08 A Fishing Eagle 2 Results: Year 2005 all together: A Fishing Eagle 3 A Marsh Harrier 4 A Hen Harrier 2 --- The question: How can I limit my calculation for certain birds? Thanks in advance! -- Petri M ------------------------------------------------------------------------ Petri M's Profile: http://www.excelforum.com/member.php...o&userid=29210 View this thread: http://www.excelforum.com/showthread...hreadid=489386 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining Columns
Hi this will be =SUMIF ( Range of All Birds, A Bird Name, Qty of bird range) See below 06-May black 3 06-May brown 2 06-May red 1 08-May black 0 08-May white 7 08-May brown 6 08-May red 2 08-May green 4 10-May black 6 10-May white 3 10-May brown 5 10-May red 2 10-May green 4 ` = SUMIF($B$5:B18,$H5,$D$5:$D$18) ----This is the formula when written black 9 white 45 brown 13 red 5 green 8 -- Thiem ------------------------------------------------------------------------ Thiem's Profile: http://www.excelforum.com/member.php...o&userid=27474 View this thread: http://www.excelforum.com/showthread...hreadid=489386 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining Columns
Thank you very much. I had some difficulties to make this work, because I have localised Finnish version of Excel. They have also translated function names. What a brilliant idea... SUM is SUMMA in Finnish version and SUMIF is SUMMA.JOS. After figuring this out, I finally got it to work. Thank you once again, now I start to count my birds and make some fancy charts of them :) -- Petri M ------------------------------------------------------------------------ Petri M's Profile: http://www.excelforum.com/member.php...o&userid=29210 View this thread: http://www.excelforum.com/showthread...hreadid=489386 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to combine several columns into a single column | Excel Discussion (Misc queries) | |||
How do I make different size columns on the same work space? | Charts and Charting in Excel | |||
Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) | |||
Combining IF and COUNTIF based on two columns | Excel Discussion (Misc queries) | |||
Pivot Table combining multiple columns | Excel Discussion (Misc queries) |