yogi_Formula For COUNTIF On Multiple Substrings

                    Google Spreadsheet   Post  #1775
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep-27-2014
post by  Toby Carson:
(https://productforums.google.com/forum/#!mydiscussions/docs/7bzxWf30qkc)
COUNTIF on multiple substrings

Sorry if the answer is already out there - I'm going Goggle-Eyed searching.

I have a large sheet (Form Responses) populated by staff at my school selecting items (mostly subjects) off a list of checkboxes in a form,

I have another sheet that simply lists those checkbox subjects in column A,

I'm keen to count how many times each subject has been checked in column B,

=COUNTIF('Form Responses'!D:D,"*"&A2&"*") does a pretty good job at this, however...

The complication is that some categories recur so that staff can award 2 points rather than the standard single (eg maths is 1 mark, maths*2 is 2 marks)

on some occasions, they may award 3 marks by combining both of these.

The formula above will only record one instance of the substring but apparently not the second one should it appear twice in the cell.

Any clever people got a simple solution?

Very grateful,

Toby

-------------------------------------------------------------------------------------------------------------------------------

yogi_Conditionally Format Each Cell Of Sheet2 That Is Different From That Of Sheet1

                    Google Spreadsheet   Post  #1775
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep-27-2014
post by  Bryan Blair:
(https://productforums.google.com/forum/#!mydiscussions/docs/OsjqOTXZQl4)
New Conditional Formatting option - Custom formula is
How would you use =indirect("Sheet1!A1") as a custom formula for conditional formatting across a range of cells.

Here is my current setup:

2 sheets - Sheet1 and Sheet2

I want to compare ALL of the cells in Sheet 2 to Sheet 1.  If the contents of each cell are different, I want to change the background color of the cell in Sheet 2.  This is easy enough if the 2 cells are on the same sheet. But, the indirect() function appears to add something that I don't completely understand.

I have tried the custom formula (above) in the cells for Sheet 2, and I can get it to work for single cells - but, as soon as I try to apply the custom formula to a range of cells, it doesn't work - the reference cell in the indirect formula is not a relative reference - it doesn't change to A2, A3... etc - it remains A1 for the entire range.  Why?

-----------------------------------------------------------------------------------------------------------------

yogi_Conditional Formatting For Items Where Every Material Listed Has Been Priced And Used For Open Ended List of Materials

                    Google Spreadsheet   Post  #1774
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep-26-2014
post by  robodawg42:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/TwxMuRFJ2Yw)
How do I use conditional formatting governed by multiple cells?

following is a case ...
where I have highlited cells in column D where all listed materials have been priced and used in an item

yogi_Conditional Formatting For Items With Materials Used With No Material Price Listed For Open Ended List of Materials

                    Google Spreadsheet   Post  #1773
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep-26-2014
post by  robodawg42:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/TwxMuRFJ2Yw)
How do I use conditional formatting governed by multiple cells?

Ok, the formula you listed 

=and(E3>0,E$1=0)+and(F3>0,F$1=0)+and(G3>0,G$1=0)

will definitely work, and I can change the range to every cell in the column. However, I was hoping there was a way to do this without writing out every and statement for each row (there are over 30). It's a momentary pain, but I was hoping for a more graceful solution, possibly changing it to something like:


=and(E3:G3>0,E$1:G$1=0)I know that way specifically won't work, but is there some way to go about it that I can use the and statement over the range instead of each individually?
-----------------------------------------------------------------------------------------------------------------------------------
following is the solution to a bit more generalized problem using open-ended list of Materials

yogi_Conditional Formatting For Items With Materials Used With No Material price Listed

                    Google Spreadsheet   Post  #1772
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep-26-2014
post by  robodawg42:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/TwxMuRFJ2Yw)
How do I use conditional formatting governed by multiple cells?
I'm trying to use Sheets to make a list of items, the materials required to make them, costs of those materials, and calculate a total cost for each item. It's a rather large spreadsheet. I'm using the SUMPRODUCT formula to generate the cost of each item. However, I'd like to format the cost cell to turn red if I've forgotten to input a value for the cost of a material for a given item. I know how conditional formatting works, but I don't know the formula to use. Here's some codefu that's roughly what I'm looking for:

IF(AND(Material > 0, MaterialCost = 0), Red, White)

This works when I use one cell for Material and MaterialCost, however I'd need to check over the entire SUMPRODUCT arrays; in other words, this needs to check if Material1 > 0 and MaterialCost1 = 0, then Material2and MaterialCost2, etc. It would then turn red if any of the checks comes out true. I don't want the cost cell to turn red if that item doesn't require the material whose cost is missing.

I'm not sure how to get the logic formulas to check the entire list and return values without writing each check explicitly, which would be a huge pain in the ass. I've tried using something like Cell1:Cell50 in the logic formula but it doesn't return values.

Any ideas?
---
Alright I've created a test sheet that looks similar to what I'm doing:

https://docs.google.com/spreadsheets/d/1GAGRupvP_EavQ5YHqgsmqMeV_3oQR50r7ltJWGXobLs/edit?usp=sharing

So I've got prices listed in row 2, and Material 2 has no price listed. Therefore any item that requires material two (two and four) has its cost cell turn red, as I've done manually. The actual sheet is about 30x60, so like I said I don't want to type out each manually.

GimeIG's answer is a bit different than I want, it ends up turning the material cell red instead of the cost cell.
----------------------------------------------------------------------------------------------------------------------------

yogi_Formula For Computed Unique Task Nos Array Computation

                    Google Spreadsheet   Post  #1771
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep-25-2014
post by  Raivis:
(https://productforums.google.com/forum/#!mydiscussions/docs/Hh-hrLyLwo4)
How to have array formula that calculates the next array value from the previous value?

Hi,

Is that possible?

I have shared this spreadsheet as an example. Cell G6 is calculated correctly by my array formula, however, all the preceding values are not correct. I have shown the expected values in column J.

What am I trying to achieve?

- I want to be able to provide for task Y either its start date or some other preceding task X (in this case it would take task's X end date and add one business day), and calculate the start/ end business day dates of task Y with the array formula. 

Some notes:

- The cells within red border are not calculated correctly by my current formula

- All grey cells are calculated by formulas

- All the white cells contain either array formulas or values entered by a user

- For more information about particular cells look at the cell notes

Regards

---

Hey Adam,

Thank you for looking into it again! I am trying to avoid script for now.

In my mind, this problem is very similar to the one in this sheet, however, the sheet looks way more simple. The grey cells contain simple non-array formulas and I am looking for a way to replace them with an array formula.

Cheers,

Raivis

-------------------------------------------------------------------------------------------------------------------------------------

yogi_Formula In Cell A4 For Cells A4 To D To Pull Records From 'Formulaire' For Dates Between A2 and B2

                    Google Spreadsheet   Post  #1770
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep-24-2014
post by  Vincent delaveau:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/zXXh1EHBv-8)
=Filter par date

le lien de la feuille de formulairej'aimerai faire un index Des dates du formulaire comprise entre le 10/09/2014 et 31/09/2014 sur ma feuille "Result"https://docs.google.com/ feuilles de calcul / d / 183L2tp4MBOjdEkJgQErbSpgQ- ZgoLHnyYMeSnMoPzrE / Ed

the link of the sheet form

I'd like to do an index Dates of form between the 10/09/2014 and 31/09/2014 on my sheet "Result"

https://docs.google.com/ spreadsheets / d / 183L2tp4MBOjdEkJgQErbSpgQ- ZgoLHnyYMeSnMoPzrE / Edit # gid = 0 

Thank you in advance.

-------------------------------------------------------------------------------------------------------------------------------------------

yogi_Formula In Cell E4 For Cells E4 To E To Create A Table For Each Event And Name As Noted In Sheet1 and Sheet2

                    Google Spreadsheet   Post  #1769
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep-24-2014
post by  nelipot:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/YTHBIm5QDGo)
How to create a new set of data rows from 2 columns

Hi guys -   I need to take data from 2 columns and produce a third data set as shown below. See spreadsheet here

https://docs.google.com/spreadsheets/d/1tvsrwAwu5RLJjFFuMfRaogIY1pBPEQzGOnezn8uEdlk/edit?usp=sharing

There is a lot of data spread across sheets and it is an ongoing process (new names) - if i can automate it with a formula or macro in Sheet 3 that would be brilliant :)

Sheet 1		Sheet 2		Sheet 3
				result needed
Events		People		Person	Event
Screening		John		John	Screening
Treating		James		John	Treating
Followup		Jack		John	Followup
				James	Screening
				James	Treating
				James	Followup
				Jack	Screening
				Jack	Treating
				Jack	Followup

------------------------------------------------------------------------------------------------------------------------

Formula In Cell J2 For Cells J2 To J For Row By Row Comma Delimited List Of Days Off By Each Name In Column A

                     Google Spreadsheet   Post  #1768
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep-23-2014
post by  googledocsfan:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/4_Xx8Twztao)
Creating a formula to return 'Days OFF'. Help :) Spreadsheet Included!

Hello all!  I would greatly appreciate your help in figuring this out.  Please see the attached below.  

I have a chart showing days off for a list of names. In Column J, this is my desired result that I am trying to produce (without manually doing it).  Does anyone have a formula or solution to make this work in google sheets?

The results don't have to be formatted exactly like shown, but something similar would work.  Thank you so much for any of your help!  

https://docs.google.com/spreadsheets/d/1MFyIfKCadh9hPrpehFriO857JBOkDzdUvSIf0c2yCgw/edit?usp=sharing

---------------------------------------------------------------------------------------------------

yogi_Rotate (Slant) Text Reading From Text String In Cell(s) -- A Formula Based Approach

             Google Spreadsheet   Post  #1767

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep-23-2014 updated Jan-08-2015

post by Deb14 -- question by meljay (https://productforums.google.com/forum/#!searchin/docs/Rotate$20text/docs/KdyxPSBGjjI/8TQRfZs2eoQJ)
How can I rotate text within a cell in Google docs?
Nice answer, but I need the text to be linked to another field. In this case I am doing book spines, and I want the text smaller across the top, and the same text (linked to the across field) down the back in large. I could do each one as a drawing rotate and insert, each time I use the sheet, or else just use Libreoffice that allows me to do this reasonably easily and quickly. The whole, do what you want in another module, edit and import that, loses a lot of functionality, so not a real solution. It would be good if future posters could check old posts, and the comments that arise from them, and don't post solutions that were suggested years ago.
----------------------------------------------------------------------------------------------------------------------- 

As of now (Sep-23-2014) Google spreadsheet does not support ROTATE (SLANT) text -- following is a formula based solution as a WorkAround

in the image presented below the formulas are

formula in cell B3
=ArrayFormula(join(char(10),rept(" ",row(indirect("YY1:YY"&len(join(char(9),"Google Docs"))))*B1)&mid("Google Docs",row(INDIRECT("YY1:YY"&len("Google Docs"))),1)))
this formula is then dragged to the right for copying to cells C3 to E3

and

formula in cell F3
=ArrayFormula(join(char(10),rept(" ",(len(join(char(9),"Google Docs"))+1-row(indirect("YY1:YY"&len(join(char(9), "Google Docs")))))*F1)&mid(join(char(9),"Google Docs"),len(join( char(9),"Google Docs"))+1-row( INDIRECT("YY1:YY"&len(join(char(9),"Google Docs")))),1)))

 
the formulas in cells B3 and F3 may be copied from the following yogi-Sheet2-formula(s)

yogi_Formula In Cell H15 For H15 To H For RESULT (Pay) Based On Hours And Pay Rate For Hotel And Position From Sheet1

yogi_Formula In Cell B2 For Cells B2 To C To Compute Sum Of Numbers For Each Letter ID In Cells Of Column A

                      Google Spreadsheet   Post  #1765
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep-22-2014
post by Medellee Antonioli:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/uCVHuTDhec0)
Summing numbers connected to letters

I'm trying to find a way to sum up numbers attached to a certain letter in a range of cells.  The difficulties are: 

a) Each number is attached to a letter like 20A and 

b) Each cell contains a list of numbered letters like 1A, 12B, 2D

I want my function to look at a range of cells and tell me (for example) the sum of the "A" numbers.  So if my column is:

10A, 2B

3C, 5A, 1B

2A, 2B

I want the function to output 17.  I know this would be easier if the A's, B's and C's were in separate cells, but for data entry reasons this is the way it has to be.  I've poked around a bit with regex type things, but no luck.  Any ideas?

-------------------------------------------------------------------------------------------

yogi_Compute Total Time In Minutes By User From A Table Of StartTime EndTime And UserName

                      Google Spreadsheet   Post  #1764
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep-19-2014
post by CIS Mentor:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/zhnMThGBvWU)
How do I count cells based on a condition and extract that cell's data?
How do I count cells based on a condition and extract that cell's data (if it matches a condition), sort of like a COUNTIF but also adding that cell's data into a variable (for each occurrence) so I have a total.

For example cell has numbers (with values 1, 2 and 3) that meet the condition, and I want to store those values in an integer that will have the value 6 (alternatively, using an array to store each cell's value then looping through the array and storing the total through the iterations will work).
----------------------------------------------------------------------------------------------------------

yogi_Formula In Cell B3 For Cells B3 To 3 To SumUp Expenses By Category For Each Month Of The Year

                      Google Spreadsheet   Post  #1763
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep-14-2014
post by Chris634:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/OV0cOVow1YQ)
I have a form the user is submitting dollar amounts for expenses on a variety of items.  As usual, each submission will populate and timestamp one row on the form responses sheet.

I need to create a second sheet that will show a sum of each expense (each column). But I want it to summarize by month, based on the timestamp if possible?  So for instance, If the user submitted multiple forms in September, I want the amount on the second sum sheet to reflect all the values that were submitted in September.  I will be doing this for each month.  So if it was submitted in October, the sum of that data will display in the October cells of the second sum sheet.
---
https://docs.google.com/forms/d/1WCcVe0bAUdBr1oxx695zHcnh_XrkvWRt2G66K5iRn4A/viewform?usp=send_form

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

yogi_Manipulate Words Separated By Commas Without Splitting Them Into Different Cells

                      Google Spreadsheet   Post  #1762
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep-13-2014
post by Chkr:
(https://productforums.google.com/forum/#!mydiscussions/docs/t8z9TzXoYFA)
Manipulate words separated by commas without splitting them into different cells

Hello All

I am currently prefixing and appending html elements to names to create html links via concatenate. For example:

fixed cell 1:    <a href="abc.com/
dynamic reference cell 1:     name1
fixed cell 2:    .html">

fixed cell 3:    </a>

>>>    <a href="abc.com/name1.html">name1</a>

I would like to do the same for a dynamic reference cell which has two or more names in it separated by commas, keeping the new modified forms within the one cell, still separated by commas. Thus:

fixed cell 1:    <a href="abc.com/
dynamic cell 1:     name1, name2, name3
fixed cell 2:    .html">

fixed cell 3:    </a>

>>>    <a href="abc.com/name1.html">name1</a>, <a href="abc.com/name2.html">name2</a>,  <a href="abc.com/name3.html">name3</a>

etc.

I have dabbled and failed, as I am not sure how to manipulate each word without splitting/transposing them into separate cells.

Assistance with this from one of our esteemed gurus would be hugely appreciated!

-----------------------------------------------------------------------------------------------------------------------

following is a solution to a bit more generalized problem

Formula In Cell L2 For Cells L2 To L For Computing Number Of Instances Of G1 G2 G3 Multiplied By Corresponding Number Of Days In Cells I2 To I4

                      Google Spreadsheet   Post  #1761
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Sep-13-2014
post by Colder Pressed:
(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/i7Xz8EqDCP0)
How do I count data multiple times based on a multiplier?

Hello All,

Here's what I have:

Product	Breakdown							Days		Item	Total
1 CC (L2)	G1	F2	G4	F4	G1	G3		1		G1	3
1 CC (CUSTOM)	G3	G3	G4	G4	G5	G5	R6	1		G2	0
2 CC (L1)	G3	F2	G1	F4	R1	N1		2		G3	4

Let's say Product is col. A and Total is col. M

In col M I'm using

=COUNTIF


in order to get the number of times each item appears in the table

However I'd like all the entries in 'Breakdown' to be counted twice, as dictated by the value in the 'Days' column. So in reality, the count value for 'G3' should be 5, not 4. Likewise, the total value for 'G1' should be 4, not 3.

Here's what I want to do:

Product	Breakdown							Days		Item	Total
1 CC (L2)	G1	F2	G4	F4	G1	G3		1		G1	4
1 CC (CUSTOM)	G3	G3	G4	G4	G5	G5	R6	1		G2	0
2 CC (L1)	G3	F2	G1	F4	R1	N1		2		G3	5

Is something like that possible?

Thanks in advance for any replies :) 

--------------------------------------------------------------------------------------------------------------------