|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
February 10th, 2009, 02:34 PM
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
February 10th, 2009, 02:38 PM
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
February 10th, 2009, 02:40 PM
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
February 10th, 2009, 04:41 PM
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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
