| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula based on 3 criteria
A1 B1 C1
Location Hours Month Essex 12 December Kent 13 December Surrey 32 December Essex 13 January Surrey 13 January Essex 45 January Surrey 22 January Kent 24 January I need a formula that picks out a location, adds up the total hours for that location but only for a chosen month. For example from the table above i might want to look at the total hours spent working in Essex but only in January. In this case it would be 58 Can you help please? The answer would be displayed in D1 Thanks Craig |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula based on 3 criteria
=SUMPRODUCT(--(A2:A20="Essex"),--(C2:C20="January),B2:B20)
-- __________________________________ HTH Bob "Craig" wrote in message ... A1 B1 C1 Location Hours Month Essex 12 December Kent 13 December Surrey 32 December Essex 13 January Surrey 13 January Essex 45 January Surrey 22 January Kent 24 January I need a formula that picks out a location, adds up the total hours for that location but only for a chosen month. For example from the table above i might want to look at the total hours spent working in Essex but only in January. In this case it would be 58 Can you help please? The answer would be displayed in D1 Thanks Craig |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula based on 3 criteria
in D1...
=sumproduct(--(A2:A500="Essex"),--(c2:c500="December"),B2:B500) will get it for you! "Craig" wrote: A1 B1 C1 Location Hours Month Essex 12 December Kent 13 December Surrey 32 December Essex 13 January Surrey 13 January Essex 45 January Surrey 22 January Kent 24 January I need a formula that picks out a location, adds up the total hours for that location but only for a chosen month. For example from the table above i might want to look at the total hours spent working in Essex but only in January. In this case it would be 58 Can you help please? The answer would be displayed in D1 Thanks Craig |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula based on 3 criteria
Hi Craig
Use a pivot table for things like this. Best wishes Harald "Craig" wrote in message ... A1 B1 C1 Location Hours Month Essex 12 December Kent 13 December Surrey 32 December Essex 13 January Surrey 13 January Essex 45 January Surrey 22 January Kent 24 January I need a formula that picks out a location, adds up the total hours for that location but only for a chosen month. For example from the table above i might want to look at the total hours spent working in Essex but only in January. In this case it would be 58 Can you help please? The answer would be displayed in D1 Thanks Craig |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula based on multiple criteria | Excel Worksheet Functions | |||
| Assigning value based on criteria in Formula | Excel Discussion (Misc queries) | |||
| Formula for product based on criteria? | Excel Worksheet Functions | |||
| Executing a formula based on criteria being met | Excel Worksheet Functions | |||
| Need formula based on criteria. | New Users to Excel | |||
Linear Mode
