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

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

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

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

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