yogi_Count Number Of Employees by Specified Type From Data In Another Sheet

Google Spreadsheet   Post  #2363

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Feb-12-2018

question by: Mike236
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/ZPgJ9uUWzZI;context-place=forum/docs

Arrayformula SUM Countifs

For each Job on Jobs Sheet I need a count of employees by type (Tech1, Tech2) from Employee Sheet.

2 Questions:

1. How to get this into an arrayformula

2. More efficient way to do this?

This works when copied down but fails in an arrayformula:

=IF(LEN(A3:A),SUM(countifs(Employees!A3:A,A3:A,Employees!C3:C,"Tech1")+countifs(Employees!A3:A,A3:A,Employees!C3:C,"Tech2")),)

Here's the sheet.

yogi_Row By Row Running Count Of Numeric Entries 1 In Column A

Google Spreadsheet   Post  #2362

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Feb-04-2018

question by: kgftikh
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/cLFLsWqqv50;context-place=forum/docs

Arrayformula CountIF from first cell till each cell within a column

Hello,

I can't seem to figure out how to make an arrayformula that counts everything till each cell from one column.

Example:

ColumnA    Result

1               1
_               1
_               1

1               2

1               3
_               3
_               3
_               3

1               4

1               5
_               5
_               5
_               5
_               5

1               6

In the column A there will only be either 1 or empty cell, so this means that countif can be replaced with sum, though I don't know how to use it either...

Your help would be much appreciated.

yogi_Count and list selected weekdays that occur between a start and end date

Google Spreadsheet   Post  #2361

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Feb-04-2018

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/cLFLsWqqv50;context-place=forum/docs

Count and list selected weekdays that occur between a start and end date

yogi_Compute Applicable Hourly Salary For Pay Period From Table OF Hourly Salary By Date

Google Spreadsheet   Post  #2360

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Feb-03-2018

question by shimoda

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/miPwUBHQTNg;context-place=forum/docs
How to find an employee last pay rise to calculate his next pay check using ARRAYFORMULA

I am looking for a way using ARRAYFORMULA to get an employee last pay rise to calculate his next pay check. The goal is to find the last pay rise effective before the beginning of the pay period. I want to use ARRAYFORMULA in order to avoid copying formulas down the sheet.

I have not found an example where ARRAYFORMULA allow to retrieve a value when searching for a text value (employee name) and the BEST matching date that would fit inside a time interval (so not an exact date match value). 

https://docs.google.com/spreadsheets/d/1jTVYPx9WwhSWDpZSZVLB-40tQFO7q1tk7AYKDnKY358/edit?usp=sharing

I would be happy to read your suggestions. 

yogi_Assign sequential ID from range based on cell text and previously used range value

Google Spreadsheet   Post  #2359

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Feb-03-2018

question by b_sander

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/ikBUIPillkk;context-place=forum/docs

Assign sequential ID from range based on cell text and previously used range value

 

I would like to populate a field with a sequentially unique number from a given range dependent on a string/product type.

There are three products (ProdA, ProdB, and ProdC) and when one of the three products is selected from a field, I want a number generated sequentially from a range of numbers where (ProdA=100-199, ProdB=200-299, and ProdC=300-399)

Product, Article#
ProdA, 101

ProdA, 102

ProdC, 301

ProdB, 201

ProdC, 302

ProdA, 103

ProdB, 202

I have used  =if(A2="ProdA",100,if(A2="ProdB",200,if(A2="ProdC",300))) and then manually changed the last digit to the next unused digit in the range.

I have also tried to use the script editor unsuccessfully.

Any direction or advice would be appreciated.

yogi_Compute commission (or Pay) Based On Tiered Brackets Of Profit Generated

Google Spreadsheet   Post  #2358

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Feb-01-2018

question by JenniferHowell

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/Q7DGCqhKJzM;context-place=forum/docs

How to formulate commission structure based on tiered brackets of profit generated?

ADD A REPLY

I am trying to create a calculated commission structure based on a tiered program. The tiers look like this:

Profit Per Employee	Pay %
Tier 1: $0-$10,000.00	6%
Tier 2: $10,000.01-15,000.00	8%
Tier 3: $15,000.01-$20,000	11%
Tier 4: $20,000.01-$25,000	14%
Tier 5: $25,000.01-$30,000	17%
Tier 6 : $30,000+	20%

Employees will receive pay based on each individual tier. For Example:

$18,000 TOTAL PROFIT TO BASE COMMISSION ON

6% x $10,000 = $600

8% x $5000 =  $400

11% x $3,000 = $330

$1230 USD TOTAL COMMISSION PAYOUT

I am sure it is probably a simple IF statement. I have used them in the past for simpler commission structures that are not broken up into segments. Can someone please assist me with the correct IF stament or formula?

Thank you!

yogi_Pull Multiple Entries From Form Reponses Sheet Entered Together In Rows

Google Spreadsheet   Post  #2357

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-28-2018

question by N2k

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/6LtBRfXU7OQ;context-place=forum/docs
Struggling with INDEX and MATCH to pull data from another sheet
Hi all,

I've been looking for a way to import user Google Forms responses into my database automatically, however the problem I've run into is that I have a lot of repeating blank column due to repeating sections.

I would like for the Form Responses to be pasted into the correct row when the RMA number given in the Please enter your provided RMA reference section matches the row in MainData sheet, returning the relevant company, customer, device type, serial, site and fault reported fields.

However I've been struggling with achieving this with QUERY as I cannot edit things the next row below, or make it loop, so that if fills in Yes to another RMA reference, the same contact details get added to the row below.

I've thought of using INDEX and MATCH but I have no idea where to start! Here's a sample sheet:

https://docs.google.com/spreadsheets/d/1ydLs8XV0aBdoUfO9WH_p-6AidVX7NpMaKRDQjL2dG7Q/edit?usp=sharing

Any help is MUCH appreciated!