Excel Formula

N

Newfie809

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
 
R

ryguy7272

With your data in A1:F5, use this function:
=SUMPRODUCT(--(B3:B5="A1"),--(C3:C5=10)*(D3:D5))

Regards,
Ryan---
 
P

Peo Sjoblom

No need to use the multiplication operand at all

=SUMPRODUCT(--(B3:B5="A1"),--(C3:C5=10),D3:D5)



--


Regards,


Peo Sjoblom
 
D

David Biddulph

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?
 
N

Newfie809

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 > .
 
D

David Biddulph

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top