yogi_Compute From Table 1 Combined Sum Of Values Of Corresponding Items In Table 2

              Google Spreadsheet   Post  #1857

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-07-2014

post by  Jayadev Chakravarty:
(https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!mydiscussions/docs/yFp5c132t8s)
Conditional Sum

I have a table as follows:

Table 1

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

Item          Value

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

A               100

B                200

C                300

D                150

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

I have another table

Table 2

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

Item

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

A

C

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

I want the sum of all the values of Table 1 where the Col A matches with Items in Col B.  I tried with the SUM function but I have to give individual criteria.  Instead I would like to give the whole column in Table 2 as the criteria for the SUM function.  Is this possible?  If not, what other way can I do this?  Thanks for your help.

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

yogi_Based On A Set Of Needs And Qualifications Tables Determine Whether Individuals Are Qualified

              Google Spreadsheet   Post  #1856

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-05-2014

post by  Allie Hopkins:
https://productforums.google.com/forum/#!mydiscussions/docs/jci6HbFHbeo
Compare arrays
I want to compare two different arrays to see if one is contained in the other.  Essentially, I have a sheet of Area Needs indicating what levels of certain key areas must be met.  I also have a skill sheet that contains users and their current levels of these same key areas.  I'd like to create a matrix on sheet Qualified that indicates if a person meets the criteria specified in the area needs.

I have shared a file as example.

https://docs.google.com/a/google.com/spreadsheets/d/1QEtEs2Vb5u-7qCzGOhu1k_DXREYWnsW6vy_rVD97i0k/edit?usp=sharing

Can this be done with a combination of standard formulas or should I write a custom function?

---

Using my examples in the shared sheet:

Fred is a tech1 (from Ldaps)

Fred has taken training1 and training2 in ABC (from Skills)

Area A needs a tech1 to take training 1 in ABC (from AreaNeeds)

Therefore, Fred is qualified in Area A and should show TRUE on Qualified sheet.

What I have done thus far is grabbed an array of the skills of a given user using a query and another array of area needs using another query.  I'm trying to compare these two arrays to see if one contains all the items in another.  This does not work the way I've done it since the vlookup in the formula is only looking at the first column of the query results and not the other two:

countif(iferror(ARRAYFORMULA(VLOOKUP(query(AreaNeeds!$A$1:$E$82, "select C,D,E where A = '"&B$1&"' and B = '"&vlookup($A4,Ldaps!$A$2:$B,2,false)&"' label C '', D '', E ''", -1),query(Skills!$A$1:$E$102, "select B,C,D where A = '"&$A4&"' label B '', C '', D ''", -1),1,false)),"Not"),"Not")

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

yogi_Given In And Out Info Compute Number of Days Including Fractional Days Discounting Weekends (Saturdays and Sundays)

              Google Spreadsheet   Post  #1855

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-05-2014

post by  Janice Roberts:
https://productforums.google.com/forum/#!mydiscussions/docs/0O-Abpxxxnk

Calculating number of work hours between timestamps

I am building a sheet that creates a timestamp when a specific cells is changed.  I need to calculate the number of work hours between the two dates.  I want to exclude weekends from the calculation.

Can anyone help?  Thanks!

Janice

---

Thanks for your response!   Below is a link to my spreadsheet, which I have shared to all with the link.

https://docs.google.com/spreadsheets/d/1KZM8vT0CA8b6CqDM857xvog7TbtVBuhBqllRjX7yQ6k/edit#gid=1709386020

In Row AN I am calculating time worked by subtracting the timestamps in cells AI and AG.  In Row AQ I used a formula to give me my answers in days.  The problem is that I don't want weekend days to count into my calculation because no one works on the weekends.  Row 6 is an example of timestamps that include a weekend.

I would like to calculate the number of days worked as in AQ but I would like the weekends to be subtracted. 

Hopefully this is enough information but if not please let me know.

Thanks in advance!

Janice

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

yogi_Find The Words And The Starting Position(s) Of Words In Cell A2 That Occur In Cells A1 To J1

              Google Spreadsheet   Post  #1854

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-02-2014

post by  amelie Boulliard:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/ZEZKIpuBwk4
Spreadsheet function : Search a Cell for a List of Words

In a spreadsheet, I need to Search a Cell for a List of words.

I googled it and found this : 

http://www.myonlinetraininghub.com/excel-search-string-for-a-list-of-words

... which basically explains what I need (except that I need to do this in a spreadsheet, and not in Excel).

But the function they suggest : 

=INDEX(list,SUMPRODUCT(ISNUMBER(SEARCH(list,A2))*ROW($1:$3)))

does not work in my spreadsheet (even after replacing "list" by the cells and "," by ";"

Example : let's say the list of words are in the cells A1:J1

and the cell where I search these words is B2, using their function, I get in my spreadsheet : 

=INDEX(A1:J1;SUMPRODUCT(ISNUMBER(SEARCH(A1:J1;B2))*ROW($1:$3)))

But this doesn't work.

Does anyone know how I can do this?

Thank you

---

Hello James, 

Thank you for trying to help me.

I don't want to share the spreadsheet because of the private information inside, but here is an example : 

	A	B	C	D	E	F	G	H	I	J
1	book	trip	stay	hotel	guest	holiday	B&B	flat	rental	journey
2	This lovely house will make you happy as it is perfect for your holidays.
3	?
4
5
6

the list of words are in the cells A1:J1

and the cell where I search these words is A2

In A3, I want to insert the function analyzing the content of A2 : if the function finds in A2's content at least 1 of the words from the A1:J1 list, I want it to give a value (whatever value : "yes/no" or "1/0" or the number of the 1st caracter where the listed word is found...)

In the above example, I'd like the function to find that A2 contains "holiday" which is in the  A1:J1 list (F1).

I hope this helps :-)

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

yogi_Time Sheet Calcs To Compute Days Of Leave And Number Of Hours Worked Over A Range Of Dates

              Google Spreadsheet   Post  #1853

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-02-2014

post by  Vinuta Prakash:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/D5KpUmvu4eI
how to calculate no of hrs worked in a week

Sl. No.

Employee Name

24/11/2014

25/11/2014

26/11/2014

27/11/2014

28/11/2014

Total Leaves

Total no.of Hrs worked

IN

OUT

IN

OUT

IN

OUT

IN

OUT

IN

OUT

1

Thara

Leave

9.30am

5.30pm

09.30am

05.30pm

09.30am

05.30pm

09.30am

05.30pm

01

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

yogi_Conditionally Format Column A Based On The Number Of Times A Name Appears In The Column

              Google Spreadsheet   Post  #1852

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-01-2014

post by  lodaries:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!category-topic/docs/spreadsheets/OXTKW0a-YiE

Countif for >= and <=

I'm using the following Formulas under conditional formulas-  I don't want to share the sheet it has student info on it, and I'd get fired- but I figured that someone could answer my question without too much issues if I post my formulas.  What we are doing is counting the number of times a name appears on the discipline list, and color coding it.   

=countif(A:A,A1)=1   It colors the cell Green

=countif(A:A,A1)=2 it colors the cell yellow

=countif(A:A,A1)>=7 it colors the cell blue

All of these formulas work fine- The formula I am having trouble with, is the middle one-  I want it to color the cell red if the name appears between 3 and 6 times. 

=countifs(A:A,A1)>=3<6

Can someone tell me what formula I should use to do this?   

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

yogi_From Table In Columns A To C Pull Entries Where Column C is Not Blank And Sort Them By WeekDay In Ascending Order

              Google Spreadsheet   Post  #1851

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Dec-01-2014

post by  Lisa Nikolau:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!category-topic/docs/spreadsheets/bfv6IMtRaVo

How to use filter formula for two columns, with one output

Okay, so this is essentially the formula (in words) that I need:

=(show all of the cells in Column C that are in the same row as cells in Column A that contain the phrase "MON").

If that makes any sense. Thank you, I'm terrible with sheets, have been searching for an answer and I will really appreciate any help!!

---

Thanks for the reply, Leith! This formula doesn't seem to work either, though. It shows a "#N/A" error message.

I've attached an example of what my spreadsheet looks like, to give you an idea of what I'm trying to do. The spreadsheet I'm working with is a large scale company schedule, and I'd like to see what individual employee's schedules look like.

Thanks!

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