Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for searching 3 different Criteria
A1 B1 C1
Location Hours Month Essex 6 November Kent 13 November Surrey 14 December Middlesex 24 January Essex 17 January Kent 12 January Essex 16 January Essex 21 January From the table above I need a formula that will filter out the location i want, total the number of hours for that location but only in a given month. For example in the table above i would be looking for a total number of hours spent in Essex but only those for January. The answer would therefore read 54. Answer would be in D1. Thanks in advance Craig |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for searching 3 different Criteria
Hi Craig
=SUMPRODUCT(($A$2:$A$100="Essex")*($C$2:$C$100="No vember")*$B$2:$B$100) Better to put the variables in cells Put Months in F1 going across. Put Counties in E2 going down =SUMPRODUCT(($A$2:$A$100=$E2)*($C$2:$C$100=$F1)*$B $2:$B$100) Copy across and down Better still use a Pivot Table Place cursor in tableDataPivot TableFinish On the PT skeleton that appears on a ne sheet Drag Location from the field list to the Row Area Drag Month from the field list to the Column Area Drag Hours to the Data area -- Regards Roger Govier "Craig" wrote in message ... A1 B1 C1 Location Hours Month Essex 6 November Kent 13 November Surrey 14 December Middlesex 24 January Essex 17 January Kent 12 January Essex 16 January Essex 21 January From the table above I need a formula that will filter out the location i want, total the number of hours for that location but only in a given month. For example in the table above i would be looking for a total number of hours spent in Essex but only those for January. The answer would therefore read 54. Answer would be in D1. Thanks in advance Craig |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching for records with multiple criteria | Excel Worksheet Functions | |||
Searching two columns against a specific criteria | Excel Worksheet Functions | |||
Formula searching data that dose not equal two criteria | Excel Discussion (Misc queries) | |||
Searching for a criteria in array of cells within an IF statement | Excel Worksheet Functions | |||
searching for specific criteria and then doing an action based on | Excel Discussion (Misc queries) |