Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 208
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,646
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 50
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,501
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 99
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula for searching 3 different Criteria Craig Excel Discussion (Misc queries) 1 February 9th 09 05:34 PM
Searching for records with multiple criteria Jeff Excel Worksheet Functions 7 December 11th 08 11:07 PM
Searching two columns against a specific criteria Colin Excel Worksheet Functions 2 July 10th 07 07:20 PM
Formula searching data that dose not equal two criteria FarmerGemGem Excel Discussion (Misc queries) 4 March 20th 07 11:04 AM
searching for specific criteria and then doing an action based on Bill Excel Discussion (Misc queries) 1 July 20th 05 03:55 AM


All times are GMT +1. The time now is 01:49 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"