Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for searching 3 criteria
Can someone help me with this formula? I have a table that looks like this:-
A1 B1 C1 Location Hours Month London 6 January Manchester 14 January London 12 February London 11 February Birmingham 16 February London 9 February I want to count the hours for a single location but only in a certain month. For example if i need to count the total hours spent in london for february the answer would be 32. The answer would be returned in D1 Many Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for searching 3 criteria
=SUMPRODUCT(--(A2:A7="London"),--(C2:C7="February"),B2:B7)
Regards, Stefi €˛Craig€¯ ezt Ć*rta: Can someone help me with this formula? I have a table that looks like this:- A1 B1 C1 Location Hours Month London 6 January Manchester 14 January London 12 February London 11 February Birmingham 16 February London 9 February I want to count the hours for a single location but only in a certain month. For example if i need to count the total hours spent in london for february the answer would be 32. The answer would be returned in D1 Many Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for searching 3 criteria
=SUMPRODUCT(--(A2:A7="London"),--(C2:C7="February"),B2:B7)
"Craig" wrote: Can someone help me with this formula? I have a table that looks like this:- A1 B1 C1 Location Hours Month London 6 January Manchester 14 January London 12 February London 11 February Birmingham 16 February London 9 February I want to count the hours for a single location but only in a certain month. For example if i need to count the total hours spent in london for february the answer would be 32. The answer would be returned in D1 Many Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for searching 3 criteria
Craig,
It depends what column C actualy is, Dates or text so try one oof these for text =SUMPRODUCT((A2:A20="London")*(C2:C20="January")*( B2:B20)) or for dates =SUMPRODUCT((A2:A20="London")*(MONTH(C2:C20)=1)*(B 2:B20)) Mike "Craig" wrote: Can someone help me with this formula? I have a table that looks like this:- A1 B1 C1 Location Hours Month London 6 January Manchester 14 January London 12 February London 11 February Birmingham 16 February London 9 February I want to count the hours for a single location but only in a certain month. For example if i need to count the total hours spent in london for february the answer would be 32. The answer would be returned in D1 Many Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for searching 3 criteria
Hi,
You could also use any of the following three array formulas, entererd with Control-Shift-Enter. =SUM(IF((A2:A7="London")*(C2:C7="February"),B2:B7) ) =SUM(IF($A$2:$A$7="London",IF($C$2:$C$7="February" ,$B$2:$B$7,0),0)) =SUM(($A$2:$A$7="London")*($C$2:$C$7="February")*( $B$2:$B$7)) It might be better to choose your Location and Month via list boxes or combi boxes, and refer to the cells in the formulas. Dave "Craig" wrote: Can someone help me with this formula? I have a table that looks like this:- A1 B1 C1 Location Hours Month London 6 January Manchester 14 January London 12 February London 11 February Birmingham 16 February London 9 February I want to count the hours for a single location but only in a certain month. For example if i need to count the total hours spent in london for february the answer would be 32. The answer would be returned in D1 Many Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for searching 3 different Criteria | Excel Discussion (Misc queries) | |||
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 specific criteria and then doing an action based on | Excel Discussion (Misc queries) |