Formulas

TPALJA

I have a sheet created in which specific employees are awarded bonuses on. Those marked as completed would receive a bonus. So I need the formula for Status, assigned, Job # and address

Carson Penticuff

It sounds like a report would be more appropriate to your use case than adding formulas to your sheet. You can set up a report referencing this sheet, filter it to only include completed jobs, and then group it by either address or person depending on how you want to use the data.

Paul Newcome

If you needed to chart it based on that particular breakdown (name and location), you would need to use a COUNTIFS in a sheet. Report groupings can't be used for stacked column charts.

=COUNTIFS({Location}, @cell = Location@row, {Name}, @cell = Name@row, {Date}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))

Carson Penticuff

Can you provide more detail on what you would like the formula to do?

TPALJA

thanks for the reply Im ultimately wanting to know which employees are due bonus's based off the status of being marked completed for the job's that they worked at. So if the status shows as completed the person assigned to the job address would be awarded a bonus.

Carson Penticuff

It sounds like what you are looking for is maybe a way to be notified when a job is marked as complete?

Also, you have marked your question as "Answered" already. If you are able to remove the answered tag, you will probably have more people looking at your post.

Paul Newcome

Are you able to provide a screenshot with sample data entered that shows what you are wanting to accomplish?

TPALJA

this is just sample data my original list being much longer, Im wanting to know how many jobs each person completed in each given month.

TPALJA

the completion needs to correspond to the address in which they completed it at

Carson Penticuff

So, do you mean that one person could complete multiple jobs at the same address in a month? Are you looking for information kind of like this?

Month of May

John Smith - 123 Main Street - 2 Jobs

John Smith - 987 Vine Street - 1 Job

John Smith - 456 Walnut Street - 5 Jobs

Jane Smith - 321 Main Street - 1 Job

Jane Smith - 789 Vine Street - 2 Jobs

TPALJA

yes

TPALJA

yes

Carson Penticuff

It sounds like a report would be more appropriate to your use case than adding formulas to your sheet. You can set up a report referencing this sheet, filter it to only include completed jobs, and then group it by either address or person depending on how you want to use the data.

Paul Newcome

If you needed to chart it based on that particular breakdown (name and location), you would need to use a COUNTIFS in a sheet. Report groupings can't be used for stacked column charts.

=COUNTIFS({Location}, @cell = Location@row, {Name}, @cell = Name@row, {Date}, AND(IFERROR(MONTH(@cell), 0) = MONTH(TODAY()), IFERROR(YEAR(@cell), 0) = YEAR(TODAY())))