google sheets – Query with sums and 2 columns on the left

basically, I have this "problem": I have the query

= Query (Raw_Data! J \$ 2: \$ BL, "Select J, Sum (U), Sum (V), Sum (W), Sum (X), Sum (Y), Sum (Z), Sum (AA) , Sum (AB), Sum (AC), Sum (AD), Sum (AE), Sum (AF) where J contains "CUS" and (AL = "1-Income") and (AJ = "No CAPEX") or AJ = "unconfirmed CAPEX" and (AH = "No Business") and (AM = "2-Learn") and (M = & # 39;) 2-B2B & # 39;) Group By J ", 0)

It works well, I get almost 90% of what I need BUT, basically, I have another column in the range (P), which I want to see to the right of the J column.

The situation is as follows:

J. P

A . 1
B. 2
C. 2
RE. 3
E. 4
F. 1

I'd like to see the results from the P column to the right of column J (which I already have with the formula above), then the sums (which are already derived from this formula).

How can I add column P?

thank you very much

google sheets – Multiple IF (AND) statements to determine whether a test succeeds

I use Google sheets to try to generate a positive / negative result in a cell based on two different parameters;

If (R2) is less than 85% AND (I2) is equal to "T1", "T2 or" T3 ", a failure is necessary.

likewise for the same cell;

If (R2) is less than 95% AND (I2) is equal to "Broker Audit", a failure is necessary.

My problem is that if it is a broker audit and less than 95%, I need a failure. However, if it is a T2 or T3 T1 audit, it must fail at 85%. Can I put this together?

Posted on

google sheets – Online Project Reference Number Generator

I have a construction company and a team of contractors. We are constantly having a problem where contractors do not know which project reference number to assign to their projects in order to maintain accuracy, and not to have two projects with the same number.
I was wondering if there could be a database and a user input feature where they could enter all the details and generate the next project reference. No, while storing details and number in the database.

You also wonder if the database can also be used to automatically generate the project work order form.
Can forms and Google sheets do this?
I have a subscription on wix to be able to host it on my site, I also have a subscription to a cloud service.

Please recommend a solution to my problem.

Posted on

formulas – Multiple IF (AND) statements to determine whether the sheets are compliant or not

I use Google sheets to try to generate a positive / negative result in a cell based on two different parameters;

If (R2) is less than 85% AND (I2) is equal to "T1", "T2 or" T3 ", a failure is necessary.

likewise for the same cell;

If (R2) is less than 95% AND (I2) is equal to "Broker Audit", a failure is necessary.

my problem is that if it 's a broker audit and that less than 95%, I need a failure. However, if it is a T2 or T3 T1 audit, it must fail at 85%. Can I put this together?

Posted on

In Google Sheets, I need to find a string and return true / false or even a number in a cell to perform an action.

In my example, I have a column with the names of the signs we produce in our store and I have to be able to isolate similar named signs. Suppose I have to return a value if the cell is Bud Light and only Bud Light among the following:

Light bud
Bud Light Platinum
Bud Light Lime
Bud Light NFL

Performed more than 100 times at random.

I'm trying to determine the cost of signage and promotions.

The sheet above is an example of what I'm trying to do.

Posted on

google sheets – How to use a formula to divide a text into individual characters, regardless of their length?

I have a list of words in Japanese. I want to write a formula that will enter the word, for example. ら が な, and display it as an array consisting of all the characters: ひ + ら + が + な, regardless of the length of the word.

Below is an example of what I'm trying to do. Column C contains the list of words that I want to split into individual characters. I could just calculate the length of the longest word and use a MID function to get characters in positions 1 to n, but I'm sure there must be a more beautiful and less complicated way to do it. I had already found a solution online, but for some reason I could not find it anymore and I forgot how it worked. SPLIT and Text-to-column will do nothing because you need a specific delimiting character.

Posted on

google sheets – How to get the address of a beach with a formula?

To return the beach \$C\$5:\$M\$5 you can use the following formula:

Note that when you use the ADRRESS function, you refer to the cell, for example.C5 but we write the line number first 5 then the column number3.

You can not use the CELL function to get the range of your named range WidgetTypes.

Why do I have these results?
The syntax for CELL is as follows: CELL(info_type, reference).
In your case, when you give reference your named range WidgetTypes (which is actually C5:M5) and ask info_type the "address", the formula correctly "returns an absolute reference as raw text of the upper left cell reference" Which one is C5

You will find information on the type of information you are allowed to use, as well as the results you can expect from CELL on the Google official documentation page.

How to calculate the following mathematical expression in Google Sheets?

I'm trying to calculate the total money that can be earned by calculating the average dollar costs each year using Google Sheets. For example: how could I evaluate this? Posted on

Automatic Numbering of Google Sheets – Exchange Web Application Stack

I want the auto numbering to depend on the drop-down list of cells in the help column so that I can resolve the problem.

If someone chooses Start in A2 have the number 1 in B2.
Then skip one or two and have A4=2 in B3,
A6=3 in B6
A3=4 in B3
Then automatically sort the sheet according to B column? 