Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
This is a copy of a spreadsheet I have:
A B C D E F 1 Name Grid Years FTE Amount Allowance 2 John A1 10 1.0 $000.00 $5.00 3 Jane A1 4 .5 $000.00 $5.00 4 Judy A2 1 .75 $000.00 $5.00 and it continues on for 1300 hundred employees. I would like to create a foumula the would be able to tell me how many employees there are at say A1, for 1 year and bring back the total FTE for that group of employees, A2, for 10 years and bring back the total FTE for that group of employees. Is there anyone that can help me with this formula. Thank you -- Newfie |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
With your data in A1:F5, use this function:
=SUMPRODUCT(--(B3:B5="A1"),--(C3:C5=10)*(D3:D5)) Regards, Ryan--- -- RyGuy "Newfie809" wrote: This is a copy of a spreadsheet I have: A B C D E F 1 Name Grid Years FTE Amount Allowance 2 John A1 10 1.0 $000.00 $5.00 3 Jane A1 4 .5 $000.00 $5.00 4 Judy A2 1 .75 $000.00 $5.00 and it continues on for 1300 hundred employees. I would like to create a foumula the would be able to tell me how many employees there are at say A1, for 1 year and bring back the total FTE for that group of employees, A2, for 10 years and bring back the total FTE for that group of employees. Is there anyone that can help me with this formula. Thank you -- Newfie |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
No need to use the multiplication operand at all
=SUMPRODUCT(--(B3:B5="A1"),--(C3:C5=10),D3:D5) -- Regards, Peo Sjoblom "ryguy7272" wrote in message ... With your data in A1:F5, use this function: =SUMPRODUCT(--(B3:B5="A1"),--(C3:C5=10)*(D3:D5)) Regards, Ryan--- -- RyGuy "Newfie809" wrote: This is a copy of a spreadsheet I have: A B C D E F 1 Name Grid Years FTE Amount Allowance 2 John A1 10 1.0 $000.00 $5.00 3 Jane A1 4 .5 $000.00 $5.00 4 Judy A2 1 .75 $000.00 $5.00 and it continues on for 1300 hundred employees. I would like to create a foumula the would be able to tell me how many employees there are at say A1, for 1 year and bring back the total FTE for that group of employees, A2, for 10 years and bring back the total FTE for that group of employees. Is there anyone that can help me with this formula. Thank you -- Newfie |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
And if you *do* use the multiplication operand, it presumably doesn't need
the double unary minus? =SUMPRODUCT((B3:B5="A1")*(C3:C5=10)*(D3:D5)) perhaps? -- David Biddulph "Peo Sjoblom" wrote in message ... No need to use the multiplication operand at all =SUMPRODUCT(--(B3:B5="A1"),--(C3:C5=10),D3:D5) "ryguy7272" wrote in message ... With your data in A1:F5, use this function: =SUMPRODUCT(--(B3:B5="A1"),--(C3:C5=10)*(D3:D5)) Regards, Ryan--- -- RyGuy "Newfie809" wrote: This is a copy of a spreadsheet I have: A B C D E F 1 Name Grid Years FTE Amount Allowance 2 John A1 10 1.0 $000.00 $5.00 3 Jane A1 4 .5 $000.00 $5.00 4 Judy A2 1 .75 $000.00 $5.00 and it continues on for 1300 hundred employees. I would like to create a foumula the would be able to tell me how many employees there are at say A1, for 1 year and bring back the total FTE for that group of employees, A2, for 10 years and bring back the total FTE for that group of employees. Is there anyone that can help me with this formula. Thank you -- Newfie |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
-- Newfie "Newfie809" wrote: This is a copy of a spreadsheet I have: A B C D E F 1 Name Grid Years FTE Amount Allowance 2 John A1_E 10 1.00 $000.00 $5.00 3 Jane A1_E 4 .50 $000.00 $5.00 4 Judy A2_E 1 .75 $000.00 $5.00 5 Bill A2_E 1 1.00 $000.00 $5.00 6 Pat A2_E 1 1.00 $000.00 $5.00 and it continues on for 1300 hundred employees. I would like to create a foumula the would be able to tell me how many employees there are at say A1, for 1 year and bring back the total FTE for that group of employees, A2, for 10 years and bring back the total FTE for that group of employees. Is there anyone that can help me with this formula. Thank you -- Newfie |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
I tried all three of the Formulas and they did not work.
Thanks -- Newfie "Newfie809" wrote: -- Newfie "Newfie809" wrote: This is a copy of a spreadsheet I have: A B C D E F 1 Name Grid Years FTE Amount Allowance 2 John A1_E 10 1.00 $000.00 $5.00 3 Jane A1_E 4 .50 $000.00 $5.00 4 Judy A2_E 1 .75 $000.00 $5.00 5 Bill A2_E 1 1.00 $000.00 $5.00 6 Pat A2_E 1 1.00 $000.00 $5.00 and it continues on for 1300 hundred employees. I would like to create a foumula the would be able to tell me how many employees there are at say A1, for 1 year and bring back the total FTE for that group of employees, A2, for 10 years and bring back the total FTE for that group of employees. Is there anyone that can help me with this formula. Thank you -- Newfie |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
Hi there, I was wrong it did work. Thanks
I do have another question, If I have someone with a .50 or a year or 1.8 years can I use < than or . -- Newfie "Newfie809" wrote: I tried all three of the Formulas and they did not work. Thanks -- Newfie "Newfie809" wrote: -- Newfie "Newfie809" wrote: This is a copy of a spreadsheet I have: A B C D E F 1 Name Grid Years FTE Amount Allowance 2 John A1_E 10 1.00 $000.00 $5.00 3 Jane A1_E 4 .50 $000.00 $5.00 4 Judy A2_E 1 .75 $000.00 $5.00 5 Bill A2_E 1 1.00 $000.00 $5.00 6 Pat A2_E 1 1.00 $000.00 $5.00 and it continues on for 1300 hundred employees. I would like to create a foumula the would be able to tell me how many employees there are at say A1, for 1 year and bring back the total FTE for that group of employees, A2, for 10 years and bring back the total FTE for that group of employees. Is there anyone that can help me with this formula. Thank you -- Newfie |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
With such a problem description as "they did not work", you are not likely
to get much more useful assistance. If you want help, you are going to have to supply the group with more information, such as what error message you are getting, or if no error message what result you are getting, what result you were expecting, and what the contents of the relevant input cells were (and you may need to look carefully to make sure that the contents are what you think they are, as problems can occur if, for example, you've got text when you think you've got numbers). It hopefully won't surprise you to learn that all 3 formulae *do* work for us. -- David Biddulph "Newfie809" wrote in message ... I tried all three of the Formulas and they did not work. Thanks -- Newfie "Newfie809" wrote: -- Newfie "Newfie809" wrote: This is a copy of a spreadsheet I have: A B C D E F 1 Name Grid Years FTE Amount Allowance 2 John A1_E 10 1.00 $000.00 $5.00 3 Jane A1_E 4 .50 $000.00 $5.00 4 Judy A2_E 1 .75 $000.00 $5.00 5 Bill A2_E 1 1.00 $000.00 $5.00 6 Pat A2_E 1 1.00 $000.00 $5.00 and it continues on for 1300 hundred employees. I would like to create a foumula the would be able to tell me how many employees there are at say A1, for 1 year and bring back the total FTE for that group of employees, A2, for 10 years and bring back the total FTE for that group of employees. Is there anyone that can help me with this formula. Thank you -- Newfie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
match formula - 2 excel files:#1 hasthis formula, 2nd has the Raw | Excel Worksheet Functions | |||
Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions | |||
Converting an Excel formula to an Access query formula | Excel Discussion (Misc queries) |